Cascading PO's and LineItem Pricing
The solution in question is solving a problem of purchasing items from other companies in the system. Essentially there are currently three tables in the relationship:
When a user creates a Job that user's ID is set as Job::DIV_OwnerID. The user can then set the client ID and begin adding lineitems etc... When a lineitem is added to the current job a script creates a new job naming the LineItem::VendorID as the new Job::DIV_OwnerID and the new Job::ClientID as the original job's Job::DIV_OwnerID. If there is already a job record, the script ignores this step.
Each LineItem has two prices that each user will see: cost and sold. The trick here is that these actually need to be in the separate table "LineItems_Prices" which has the following fields:
- LineItem_RecordID (references the lineitem the price is for)
Two TO's of LineItems_Prices will be needed to drive the LineItem's "Cost" and "Sold" prices
LineItems contains the following fields:
The end-goal is to have each respective DIV_OwnerID (user) see only the LineItems_Prices relating to them. For example, I am creating a job for a non-user client and add an item from CompanyA which triggers the previously mentioned script. The LineItem shows me my Cost and Sold prices. When CompanyA logs in, it shows them their Cost and Sold prices. CompanyA needs to add an item that I didn't put on the original PO that is going to be billed to my job, so they add a new item of their own as well as something they need to acquire from yet another vendor on the system and so on....
- "GlobalJobID" is not a global field, it is simply a tracking number that keeps all related items together. It helps all the vendors in the system stay on the same page when they are working together.
- When a user signs in, a script and self-join of users sets the global field CurrentUser::gDIV_OwnerID to the user's associated company.
- The solution to this problem needs to allow for a vendor creating a job to add items from their own inventory as well.