Difficulty populating field from related table
I am building a solution for a farrier business and am having problems getting one field to populate properly. I am a newbie and I’m obviously missing something critical.
The field I want to populate is date_last_service - not stored because it will change based on the date of the most recent invoice.
The tables are:
Products (also includes services)
The relationships are
Owners <-> Invoices <->Lines<->Products-> Horses 2
Owners -> Horses<->Invoices 2
The Horses relationship to Owners successfully populates a portal showing each owner’s horses.
Horses 2 relationship with Invoices successfully allows an invoice to show the horse on which each service is performed, and also allows me to show an invoice list based on Lines_Products_Invoices and sortable by horses and owners, etc
I added Invoices 2 in order to link date_last_serice to date_invoice (unsuccessful so far)
Without Invoices 2, I have been able show an invoice list showing date,horses, products & services, and totals, so I’m not sure that Invoices 2 is necessary.
The field date_last_service appears in the 3 layouts:
1)horse form layout based on Horses
2)in a portal based on Horses in the Owner layout, (It shows all the horses belonging to a particular owner.)
3)in a Horse List layout based on Horses
Any suggestions would be appreciated. I don’t know if I need Invoices 2 in the layout.
I would appreciate any suggestions. Aside from this - my solution works just great.
In the table occurrence Horses, I added a field “Date_Last_Service_Experiment - the field appears at the bottom of the Horse Form Layout to check the result of my experimentation, so I wouldn’t mess up what I had so far. Here's a link to the file with dummy data which you can access at 4shared.com