9 Replies Latest reply on Oct 9, 2013 12:21 PM by philmodjunk

    Script with If calculation

    Annette

      Title

      Script with If calculation

      Post

           Hi, 

           I'm going to start by telling you a couple of fields I have on my database that I would like to include in my if statement.

           People Table::Service (The persons profession) (This is captured in a script that runs at the startup of the database called $$Services)

           Users Table::Services1 (Repetition field that allows multiple team / locations)

           UsersTable::Services1Text (this is a calculation field that lists information from field Services1

           EOC::Type_Location (this would be a calculation field which combines two fields of info the EOCType and EOC Location)

           EOC::Service

            

           So what i'm trying to do is have a button on a line that runs a script that says:

           If Field1 (would be any one of the services a user could be) is equal to $$Service

           AND the EOC_Location is text found within the UsersTable::Services1Text then go to layout X.  

           My problem is I don't know how to search for a string of text within a field in a calculation.  (EOC_Locations can be any combination of things where a user might only have one of those combinations in their Services1Text and it would always be in the same order and same spacing as it's all assigned via drop down lists to avoid text entry differences)

            

           Sorry, probably explained it a lot more complicated than I needed to or it is.  

            

        • 1. Re: Script with If calculation
          Annette

               Also, does this mean I need to add to the startup script a variable capturing the information in Services1Text?

          • 2. Re: Script with If calculation
            philmodjunk

                 Actually, you didn't explain enough. You list three different tables, people, users, and EOC as well as (ick) a  repeating field and do not report the relationships linking them, the context of what layout (and thus what table occurrence) is active at the time this script step executes nor what calculation you have defined for Services1Text. Since Services1 appears to be a repeating field, this calculation has to be defined correctly or it will only refer to one repetition of the repeating field.

                 That said, you can use PatternCount to search for text contained in quoted text, a text field or a calculation that has text specified as a result type.

                 If [ PatternCount ("AppleSauce" ; "Apple" ) ] will be true in all cases.

                 If [ PatternCount (YourTable::Field ; "Apple" ) ] will be true if the text "Apple" appears anywhere within YourTable::Field.

            • 3. Re: Script with If calculation
              Annette

                   Apologies, people and users were referring to the same thing, I didn't mean to write them differently.  

                   The two table are related to each other by the PK in Users to the FK in EOC.   

                    

              • 4. Re: Script with If calculation
                Annette

                     Basically I want

                     if Field1=$$Service and EOC_Location = Part of the string found in Services1Text then go to Layout X 

                     Else 

                     Show custom dialogue.........etc etc......its matching that one field to within my text string of Services1Text that is not working for me.  The script works fine for me if that one step isnt there.

                • 5. Re: Script with If calculation
                  philmodjunk

                       So your relationship is:

                       Users::__pkUserID = EOC::_fkUserID

                       Sorry, but these posts don't fully answer my questions. Posting your script might help as we need to know what layout (and thus what table occurrence) is current at the time that your script executes. That may or may not be revealed by showing us the actual script steps. Are you on a layout based on Users or EOC when it executes?

                       And in what table did you define "field1"?

                       

                            Part of the string found in Services1Text

                       That sounds like something where you can use the PatternCount function as I described previously.

                  • 6. Re: Script with If calculation
                    Annette

                         Yes you are correct, that is the relationship.  

                         The layout that the button to execute the script is on is based on the EOC table.  

                         Field 1 is on the EOC table as well.  

                         There is nothing else to the script now really that would help as the is the beginning part of it which is causing the issue.  it works fine if its only part of that if statement.....like this. 

                         If [EOC::Field1=$$Service

                         Go to layout [Layout X (EOC)]

                         Else 

                         Show Custom dialogue ["Access Denied"; "You do not have access to this page"

                         End If

                          

                         I've tried adding to the if statement with pattern count saying If [EOC::Field1=$$Service and PatternCount (EOC::EOC_Location; Users::Services1Text     .....doesn't work.

                    • 7. Re: Script with If calculation
                      philmodjunk

                           Try:

                           If [EOC::Field1=$$Service and PatternCount (Users::Services1Text ; EOC::EOC_Location ) ]

                      • 8. Re: Script with If calculation
                        Annette

                             Still gives the custom dialogue 

                              

                        • 9. Re: Script with If calculation
                          philmodjunk

                               You've mentioned that Users::Services1Text is a calculation field. Can you describe it in more detail? Is "Text" selected as the result type? What does it do with the data in the repeating field? Is it also a repeating field?

                               And there are other ways to control access to different layouts. You can set up privilege sets for different users and specify which layouts they are permitted to access. Then your script could just use Get ( AccountPrivilegeSetName ) to determine whether or not they are permitted to access a given layout.