9 Replies Latest reply on Aug 14, 2014 12:20 PM by ChadBarnard

    Auto-Populating Field by Referencing a Calculated Result

    ChadBarnard

      Title

      Auto-Populating Field by Referencing a Calculated Result

      Post

           Here's what I'm trying to do:

           The picture is of the Orders Table.  I'm trying to get the County Name field to auto-populate based on the County field above.  The problem that I'm having is that the County field is auto-populated by a calculation that's triggered when the city is entered.

           I have the Permit City Contacts on one table that auto-populate the left side (this is what PhilModJunk helped me with yesterday), and I have a separate table for the Permit County Contacts.  The Permit City Contacts Table is related to the Orders Table by state and city, and the Permit County Contacts Table is related to the Orders Table by county. 

           I know how to do it if the County field was entered text, but I having trouble making it work when it's referenced text.

      County.PNG

        • 1. Re: Auto-Populating Field by Referencing a Calculated Result
          philmodjunk

               I'm not sure that I understand what you mean by "referenced text". Is this a field that auto-enters text from a related table? A calculation field that references text in a related record? or ???

          • 2. Re: Auto-Populating Field by Referencing a Calculated Result
            ChadBarnard

                 The County field in the Orders Table is a text field w/ a calculated value of

                      If ( OrderCity = CityContacts::PermitContactCity; CityContacts::PermitContactCounty; "" )

                 If I was manually entering it in each time, I'd know (from yesterday) how to do it.  Since I'm referencing/pulling up a calculated value, I'm coming up short.  I thought I understood how to "Lookup" a result and calculate from there,but I obviously don't.

            • 3. Re: Auto-Populating Field by Referencing a Calculated Result
              ChadBarnard

                   Slight change.  Again, I was over thinking stuff.  Since the Orders table is related to the CityContacts table by City and State, I replaced the calculated County field (Orders Table) with the County field (CityContacts Table).

                   So, I still need help with the above, but I changed the parameters slightly.

              • 4. Re: Auto-Populating Field by Referencing a Calculated Result
                philmodjunk
                     

                          The Permit City Contacts Table is related to the Orders Table by state and city,

                     

                          The County field in the Orders Table is a text field w/ a calculated value of

                     

                               If ( OrderCity = CityContacts::PermitContactCity; CityContacts::PermitContactCounty; "" )

                     But why the If function?

                     Since your relationship matches by city (and state). CityContacts::PermitContactCity will either be empty because OrderCity and OrderState don't match values to a record in the CityContacts table or CityContacts::PermitContactCity will have the same text as OrderCity due to the relationship matching records. You only need this expression (As I previously explained):

                     CityContacts::PermitContactCounty

                     No If function is needed.

                     So you first need to simplify your expression by removing the IF function.

                     But you may have encountered a "timing" issue as you have to look up (copy over) value 1 before you can use that value to lookup value 2 (the county name looked up here). FileMaker may attempt to look up value 2 first (and get a null result) and then looks up value 1.

                     You may need to put an OnObjectSave Trigger on the State and City fields to perform a script that first checks to see that the OrderState and OrderCity fields are not empty and then uses Set Field to update the county field from the CityContacts table occurrence.

                • 5. Re: Auto-Populating Field by Referencing a Calculated Result
                  ChadBarnard

                       Yeah, I realized that after my 11:25 post and corrected myself on the 11:30 post.  It's slowly sinking in. heh

                       I'm still confused about the rest, so I'm going to lay out everything again to make sure that we're on the same page (more to help me, than you probably).

                       The picture above is the Orders table.  The City and State of the Orders table is related to the CityContacts table.  The County field is CityContacts::PermitContactCounty.  It gets auto-populated once the City and State are entered on the Orders table.  Once the City and State are entered the entire left side of the tab control auto-populates as well.

                       The CountyContacts table is related to the Orders table by County.  I'm needing the County Name (right side of Tab control) and all of the fields below it to auto-populate with the appropriate contact information from the CountyContacts table.  I know I'll have to do the same thing for each field, but I was just trying to get the Name to work first.

                       Should the Name field display data from CountyContacts::CountyContactFullName or to a text field from the Orders table that has a calculated value of CountyContacts::CountyContactFullName?

                       Is this the script that you referenced?:

                            If [ not IsEmpty ( Orders::OrderCity ) and not IsEmpty ( Orders::OrderState)
                                 Set Field [CityContacts::PermitContactCounty]
                           End If

                       I think I'm wrong above, but I wanted to detail my thought process, so it would be easier to see where I'm going wrong.

                  • 6. Re: Auto-Populating Field by Referencing a Calculated Result
                    philmodjunk

                         Should the Name field display data from CountyContacts::CountyContactFullName or to a text field from the Orders table that has a calculated value of CountyContacts::CountyContactFullName?

                         It should be the same name either way, but I see no reason to complicate the process by pulling data into One field only to copy it to a third.

                         Your script's set field step is incomplete.

                         It should read like this:

                         Set Field [Orders::County ; CityContacts::PermitContactCounty]

                         In order to copy the county from cityContacts into the corresponding field in Orders.

                         If you set up auto-enter options on other fields to copy data from cityContacts, they should also update when this script is performed. You won't need an additional script for each such field.

                         When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                    • 7. Re: Auto-Populating Field by Referencing a Calculated Result
                      ChadBarnard

                           I'm so sorry Phil.  I'm still confused and I think that it's how I'm explaining it.

                           Before I made the initial post - and still -  the County field, and all of the fields in the left of the tab control, populate correctly.  I'm having trouble with the right side that's pulling information from a third table (CountyContacts).

                           You said, "If you set up auto-enter options on other fields to copy data from cityContacts, they should also update when this script is performed. You won't need an additional script for each such field."  

                      I need to copy data from CountyContacts not CityContacts.  The CityContacts is working fine.

                           I fixed the script as you suggested, but it's still not updating.  Again, it's probably how I'm explaining it.  I appreciate your patience.

                      • 8. Re: Auto-Populating Field by Referencing a Calculated Result
                        philmodjunk

                             Yes, but doesn't the county name come from CityContacts?

                             And then this data is what is used to look up data from CountyContacts?

                             Does a portal to CountyContacts show the records you would expect to see?

                        • 9. Re: Auto-Populating Field by Referencing a Calculated Result
                          ChadBarnard

                               Everything works.  It's weird and probably user error, but once I placed the portal (and set it's parameters) everything worked.  I really don't know what I did, and it was the simple act of placing the portal (which I know, logically, didn't have anything to do with it.)  As least I know how to code it in the future.  Thanks again.