1 of 1 people found this helpful
What you describe assumes that any given location uniquely identifies exactly 1 customer, Cost center and Fund center. That seems very unlikely. Are you sure that this is really the case?
Assuming that it is, you need a table of Locations where the location name (or an associated ID field) is unique with a field for Customer ID, Cost Centre and Fund Centre.
The auto-enter expression for Invoices::Customer ID then is simply:
The expressions for the other two fields are the same in format, but name different fields in the locations table.
The Looked up Value auto-enter option could also be used to enter these values each time the location field's value is modified in the invoice record.
Thanks for you quick response!
Yes, this is the case. Each location has a unique customer number, cost centre and fund centre.
I will try to play around with making a table--thank you for your help!
And I forgot to add that this locations table needs to be linked in a relationship to your invoice table by the location fields.
I use some guidelines which in my opinion work best.
As Phil mentioned to create a new table which will have the id fields you need to populate with.
then make a relationship with the table occurrence that you are in to the new table with the id fields.
Instead of using a lookup, I use a catculated auto enter.
You can see it right above the lookup button in the field options panel.
enter the field name with the relationship (table A related to table B) [A_B::id]
so that on a new record creation the id number that you want gets populated in the field you want in the local table.
1 remember that the field that the relationship is based on in the daughter table must be indexed.
2 in order for the auto enter to work, the relationship must be valid, meaning the field in the parent matches the field in the daughter table.
The other option is to do it by scripting. I like scripting the setting of those fields because I find it most dependable. It takes less than a second. Second most dependable way is the calculated method(as explained above) and third most dependable way is lookup in my opinion.
I would say go with what is most comfortable for you.
Hopefully this helps
Note calculated auto-enter values have an option "Do not replace existing value" which defaults to On. Be sure to switch that off, or if someone accidentally selects the wrong location, the others won't update when it's changed.
annr makes a good point. If the id information in that field that you want populated will never change then check the box that says "Do not replace existing value of field".
Thank you for your suggestions.
As it turns out, I was able to figure it out without the creation of another table and adding relationships--which seems a little out of my league as it is now. I crafted a nested if function which perfectly suits my needs and works without the additional tables.
Thank you everyone for your help!
Umm, no it does not perfectly meet your needs sorry to say. It works just fine right now, but you have just added a "brittle" design feature to your database--a feature that easily "breaks" and thus fails to work correctly when relatively modest changes occur in the future--such as adding a new customer or changing the data associated with a given location. Any such change and you have to go in and make a design change to your system.
By using a look up table, all such changes are managed through simple data entry operations. You the developer do not even have to be the person making the change as you can delegate that to an authorized user or group of users.
If you have any spare learning time (I know, we all have so much), I recommend going through "FileMaker Pro 14: The Missing Manual" (my library had it in their tech ebook collection). I found it very helpful for filling in knowledge gaps and giving me ideas when I was learning FileMaker.
I understand it has it's issues, but what I mean is it'll have to do until I can learn more and properly set up the look up table. I have a long way to go in understanding everything it takes to get it working, but at least in the mean time some of the work it cut out of the daily invoice entry! Which is a bit of a relief.
This database was created by someone else a long time ago, and I'm sort of just trying to find solutions to things I think would make the day-to-day a lot easier. I'm by no means the developer (though I'm apparently the only one in the office who knows how to do any kind of minor edits!).
Thank you for your help; I'm going to do some research on properly setting up the table and adding the necessary relationships.