8 Replies Latest reply on Jun 25, 2013 8:30 AM by philmodjunk

    Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)

    JeffCas

      Title

      Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)

      Post

           Hello, 

           I've been using FMP for about 8 years, but have never taken my game to the next level.

           I have 2 fields per record that I am trying to streamline- account name and account type.

            

           Basically on a new record there are many options for account name and account type, but some account names come up frequently and they are always the same account type.

            

           Looking for a way to have account type automatically populate with the correct information for some of my account names.

            

           Thanks in advance!

            

        • 1. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
          symbister

               Ok, sounds like you need a script with something like the following:

               If [yourtable::yourfielda = valuex]
                      Set field [yourtable::yourfieldb ; valuey]
               Else
                      Set field [yourtable::yourfieldb ; ""] // sets fieldb to blank - if that's what you wanted...
               End If

               Then you use this in the yourtable::yourfielda as a script trigger OnObjectModify

                

                

          • 2. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
            philmodjunk

                 Set up a table of account names and types. Link it by account name to your current table. Use a looked up value auto-enter field option to copy the matching type into your type field. If there is no matching record--it's not a common type, nothing get's entered and you can edit the type by hand.

                 This method also makes adding/removing/changing account names and types for this auto-enter feature a data-entry task. You don't have to redesign a script or calculation to implement such a change.

                 Here's a thread with more info on the subject of looking up data from a related table: Auto Fill

            • 3. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
              JeffCas

                   thank you both for your responses. I attempted both of your methods and while I did learn some great new things, in the end, I think it was a bit over my head for now. 

                   However, from attempting your methods, I did stumble upon a different idea that looks promising.

                   I setup the 2nd field- the one I want to autopopulate- as an "auto-enter calculation" and it looks like this:

                   If ( FieldA="Info X1" ; "Info Y1" )

                   That is pretty much identical to what is in my field.  I tested this and it works great. The problem is that I need to have multiple options for "Info X1" and "Info Y1"

                   If I were able to just keep listing them one right after the other as needed that would be great.

                   I was not able to figure out a solution.

                   Thanks again

                    

                    

              • 4. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
                JeffCas

                     I found an solution to the problem: nested if statements- I guess I just didn't understand the syntax

                     If ( test ; resultOne ; resultTwo )

                     Originally I had set it up for test then result and that worked fine.  What I didn't realize was that resultTwo could be a brand new if statement.

                      

                     My new question would be: is there a limit to the number of if statements that can be nested in one equation? (Hard limit or soft limit before problems arise)

                • 5. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
                  philmodjunk

                       The problem with your approach is that you will have to update the field definition--requiring a database developer with a full access password, any time you need to update the values entered.

                       A looked up value field option to copy values from a related table of values is not a complex thing to set up. And then changing the values that can be auto-entered is simply a matter of managing the data in a specific table--which can be done by any user to whom you grant access to a layout set up for the purpose, no design changes needed.

                  • 6. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
                    JeffCas

                         Ok, so I went back and tried that method again.  I think I see how it works- I have never worked with multiple tables. 

                         Does the 2nd table just kinda hang out until I want to make changes to it?

                         Does each record in the 2nd table act as a holder of information?

                         and what happens if I have multiple records in the 2nd table that have the same information in the first field but different in the 2nd field. I just tried it and it automatically put info from the earlier record

                         Thanks

                    • 7. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
                      JimMac

                           FMP application makes FMP Files or documents on your OS platform, which has at least one Table.

                      Table=Database

                           A Multiple Tables can be inside a single FMP File.  Usually the Tables are Associated or Related to each other but not required.

                      File = N * Tables or N * Databases

                           FMP does allow External Files to be Associated or Related,  such that...

                      Files = Tables = Databases

                           Each Table stores it's respective field Data!

                           Therefore the File size grows as you add data to each Table.

                           Each Record of each Table is stored separately in that Table. You, the developer, must check on or prevent duplicates.

                           Jim...

                            

                      • 8. Re: Don't know verbiage, can't find solution for if field(a) = value(x) then field(b) gets value(y)
                        philmodjunk
                             

                                  Does the 2nd table just kinda hang out until I want to make changes to it?

                             

                                  Does each record in the 2nd table act as a holder of information?

                             Yes. That's what it's there for, to store values to automate the look up process.

                             

                                  and what happens if I have multiple records in the 2nd table that have the same information in the first field but different in the 2nd field. I just tried it and it automatically put info from the earlier record

                             Correct, FileMaker will auto-enter data from the "first" related record. But think of your calculation field. It would have the same problem. If the value in the first field is the same for multiple options, how would you determine which result is the "right" one to auto-enter? You'd need some additional information to determine which one is the correct value to enter and that same additional info--if entered into a field can be used in the relationship so that there is only one related record from which to auto-enter the data.

                             But please note that this last question contradicts your original description of the problem:

                             

                                  but some account names come up frequently and they are always the same account type.

                             The only records you create in this related table are for account names that are always the same account type so that entering the account name, looks up the correct type.