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.
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.
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.
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.
Thanks in advance.
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.
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.
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?
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?
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.
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:
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.
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:
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.)
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.