I have a absurdly complex system for a client.
Labor records can be added via relationship to:
Graphic Requests Table
Editorial Request Table
AE Requests Table
PA Request Table
Finishing Request Table
Each of these request record will has a status of Open, Closed, Billed, NB, or Killed.
Client need to go to the labor list and search for anything OPEN.
First I made a calc field in labor that displays the status of the related open request.
Being a remote field and un-indexable this search for open take way too long. They currently have over 220K labor lines.
So I scripted the status in each request table to post status local to the related labor lines.
This is becoming an increasing burden as they add more information in Labor to search for. For example now they want to see Billing Status in labor. If labor line is billed what is the status of that bill. So now I have to post bill status of a local labor line each time the billing status is changed. And on and on it goes.
Currently I have 5 fields in labor that I have to be posted in order for finding to efficient.
And sometime the posting fails because the labor record is in use by another user. I capture the error and report back to user to try again.....
I am curious if anyone has suggestion for how they would handle this.