AnsweredAssumed Answered

Local vs Related Field for Finds

Question asked by user28271 on Feb 10, 2018
Latest reply on Feb 10, 2018 by philmodjunk

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

     Job 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.