1 2 Previous Next 23 Replies Latest reply on Aug 13, 2014 12:26 PM by philmodjunk

    PhilModJunk - Continuation of the If script/function question from this morning.

    ChadBarnard

      Title

      PhilModJunk - Continuation of the If script/function question from this morning.

      Post

           Phil,
           So the Underground field auto-populates as needed, but I'm having trouble w/ the Permit Contact information.  Since the tables are related, it's taken the first contact that matches the State field and auto-populates the fields in the Order table.

           I need to fill those fields w/ records that match the state and city.  I tried to set up the target field as a auto-enter calculation:

           From the Orders table:

                If ( OrderCity = PermitContacts::PermitContactCity & OrderState = PermitContacts::PermitContactState; GetField (      
               PermitContacts::PermitContactFullName ); "") 

           Of course, it doesn't work.  I'm probably over thinking it again. I've tried to set up additional occurrences, but still couldn't get it to work.

           Any suggestions?

            

           Thanks for any help.

            

        • 1. Re: PhilModJunk - Continuation of the If script/function question from this morning.
          philmodjunk

               To keep an ongoing issue in context, you are welcome to use Post A New Answer to post additional questions.

               

                    I need to fill those fields w/ records that match the state and city.

               Then you need a relationship that matches by state and city. If you already have a relationship that matches by state and you must keep that relationship unmodified to support other functions of your database, add a new Tutorial: What are Table Occurrences? of your related table so that you can link to it by the state and city fields.

          • 3. Re: PhilModJunk - Continuation of the If script/function question from this morning.
            philmodjunk

                 I was thinking in terms of a new occurrence of PermitContacts, but what you show might work in certain contexts if you are careful to set up the PermitOrderContact... table occurrence as the "context" for any look ups that you set up. It won't let you add related fields from PermitContacts to an Orders layout, however.

            • 4. Re: PhilModJunk - Continuation of the If script/function question from this morning.
              ChadBarnard

                   Okay, as per your suggestion, I changed the occurrence.

                   I've changed the fields in the order table to reference the PermitOrderContact fields, and it works if I have it on the first contacts City and State.  If I change the City to something else (that I do have a contact for), it returns blank.  See below to make sure that I've set the relationships correctly.

                   What should I be referencing to pull in the information?

                   I'm going through a course on Lynda.com and reading a book on Filemaker, but I'm still confused on practical applications, so I really appreciate the help.

              • 5. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                ChadBarnard

                     Okay, I changed around the relations to make things cleaner (see below).  I'm still having the problem where if I choose the city/state on the order table that's the same city/state as the first permit contact, everything works perfectly.  If I change the city/state to the city/state of the second permit contact, it returns blank.

                     Any suggestions?

                     Thanks in advance.

                • 6. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                  philmodjunk

                       If you have more than one record in PermitOrderContact with the same city and state values, you can't simply match by city and state. You'll need to match by other means.

                       Much depends on what you need to see happen here.

                       You could, for example, define a PermitContctsIDfk fielid in Orders and use the State and City values in order to pull up a list of records from PermitContacts such that selecting a listed contact enters that record's pk value into this fk field.

                       Or you might match by additional fields such as the city field.

                        

                  • 7. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                    ChadBarnard

                         The records in PermitOrderContact will have some matching state fields, but the cities will be different, so only one contact will have a specific city/state.  So in this example, the first contact has a city value of "North Wilkesboro" and a state value of "NC".  The second contact has a city value of "Albemarle" and a state value of "NC".

                         In the Orders table, if I enter in "North Wilkesboro" (in the city field) and "NC" (in the state field), the PermitOrderContact fields auto-populate correctly.  If I change the city to "Albemarle", the PermitOrderContact fields become blank.

                    • 8. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                      philmodjunk

                           And is there a record in PermitORderContact that has NC in the state field and Albemarle in the city field? If there isn't, these fields should become blank. If not, they should show data from PermitOrderContact. What result are you expecting here?

                           How exactly are these fields set up? Are they fields from PermitOrderContact? Fields from Orders that auto-enter data from PermitOrderContact? Calculation fields?

                      • 9. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                        ChadBarnard

                        And is there a record in PermitORderContact that has NC in the state field and Albemarle in the city field?  There is.

                        If there isn't, these fields should become blank.  Agreed.

                        There was something funny going on, because I changed the Permit Contact state to "KY" with a city of "North Wilkesboro", and it would still auto-populate the permit contact fields in an order table with "NC" as the state and "North Wilkesboro" as the city. In changing things back and forth, trying to figure out my error, there's no telling what I did, so let's start from scratch.

                             Luckily, I'm just building the backbone of this database, so I deleted the two permit contacts, re-entered them, and now nothing pulls up.  

                             I have the relationships that I showed above and all fields are basic text fields.  Are they supposed to auto-populate due to the relations set up with city/state alone, or do I need to go in and add some calculations/scripts?

                        • 10. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                          philmodjunk

                               With the relationship to match by two fields, a field set with an auto-enter field option should auto-enter a value when either of the match fields in that record are edited.

                          • 11. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                            ChadBarnard

                                 I guess this is where my understanding is spotty.  So I create a text field in the Orders table called OrderPermitContactFullName.  I give it the following auto-enter calculated value:

                                        If ( OrderCity  =   PermitContacts::PermitContactCity & OrderState = PermitContacts::PermitContactState; result 1 ; "" )

                                 Now in result 1 I've tried the following:

                                      Lookup (PermitContacts::PermitContactFullName)

                                      GetField (PermitContacts::PermitContactFullName)

                                      OrderPermitContactFullName = PermitContacts:PermitContactFullName

                                  

                                 All of them leave a blank field. I know it's probably something simple at the tip of my nose, but I'm having a time making it work.

                            • 12. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                              philmodjunk

                                   First, the syntax is wrong. The & operator is the concatenation operator used to "glue together" different text strings. If you want to use And here, you need to use the word "And".

                                   Second, this is not what you should be setting up. The Relationship is what does the value matching of your city and state fields in the records of the two tables. There's no need to set up an If function to do the matching, it's already been done for you. If the value in OrderCity doesn't match to PermitContactCity and the value in OrderState doesn't match the value of PermitContactState in the same PermitContact record, any reference to a field from PermitContact in your expression will be that of an empty field.

                                   So all you should have in this expression is:

                                   PermitContacts::PermitContactFullName

                                   No lookup function, no If function needed.

                                   And you can get the same result if you select the Looked up value field option and use the settings inside this option to copy over the value of PermitContactFullName

                                   But note that both of these methods copy the data. If the value of PermitContactFullName of the matching record later changes, your field in this Order record will not change. If you want that name to update automatically each time the data in the related PermitContact record changes, replace this field by simply adding the PermitContactFullName field directly to your Order layout. (but in many order systems, business managers want this data to be copied. That's so that if the contact info changes at a later time, they still see the contact info that was current at the time the order was placed.)

                              • 13. Re: PhilModJunk - Continuation of the If script/function question from this morning.
                                ChadBarnard

                                     Thank you.  I appreciate your patience.  Unfortunately, it's still not working for me.  I'll post a bunch of pictures and see if it clears up what I'm doing wrong.

                                      

                                1 2 Previous Next