Trying to roll up price total between multiple entities

Hi All,

So I have hit a wall and in turning to the community to see if anyone can help even know this might be long winded questions. First I am building a Vehicle database for my company that will house vehicles and their respective options. Currently I need to relate these options and their prices to a Work order then relate that work order to a vehicle. These different options are related to a car though the work order and there can be many options on a work order. I need to get the pricing from all these different parts and display them in a table with the vehicle information. I have attached some screenshots on how I'm going about making this work. I am currently hung up on get the pricing for all the different parts, options and the car price I am also having an issue where I get multiple duplicate vehicles when I create more then a on work order on a vehicle. My biggest problem is rolling up the extra options section as that is a many to many relationship, I cannot get the prices from the work order to iterate  and combine to make the msrp on the list page. Its possible I am going about this the entire wrong way as well so I am totally open to suggestions.


Showing duplicates ( the stock no 24888 has 2 work orders on it)

Relations (Should I be using less?)

Saving work order(in wondering if  i can roll up the price and just save it in here?)


Like I said I've hit a wall so I'm just going to keep trying different things till something clicks or I make a discovery so any suggestions and help is greatly appreciated.



Thanks!

Bryan

Hi Bryan

First of all, I am assuming that if you're getting the price it means that all the parts have been linked to a workorder, and this workorder has been linked to a car. If that is the case, then it means that you have the VehicleDB.Id and the WorkOrder.Id. If this is in fact the case, you can get the total price with three aggregates.

  • For the Vehiacle price, you just need an aggregate fitering by the VehiacleDB.Id
  • For the Extra Options Price do an aggregate with WorkorderDB join OnlyWith ExtraOptionsRelations join OnlyWith ExtraOptions, filter by WorkOrderId, and do a SUM on the ExtraOptions.Price (bonus points if the ExtraOptions join contains a 'ExtraOptions.Price > 0' for better performance)
  • For the Parts price, do an aggregate with WorkOrderDB as a source, and join with each of the part tables. Filter by WorkOrderId, then add a calculated attribute summing the price of each of the parts.

For the total workorder price you just need to sum the results from the three aggregates


On a side note, wouldn't it be better to have the type of part as a static entity and having one table (containing all part for all workorders)  linking a workorder and the part type? This way if a new part type needs to be added to the system, you dont have to create a new entity + a new attribute in the workorder entity (Edit) plus, you'd be able to get the parts price with a join and a sum instead of having to expressly sum the prices in a calculated attribute.


Hope this helps!


CLSJ

Hi Bryan,

You're getting multiple entries when you have more than one Work Order for the same Vehicle because you are not Grouping By the attributes of the Vehicle (so currently your Join will generate multiple rows with the same VehicleDB, one for each WorkOrderDB that has a reference to it).

I agree with Carlos, if there is no difference (or minor differences) to the info of each type of part, you should have a PartType static entity to define the possible kinds of parts, and then a single Part entity with a PartTypeId. I will assume you have done this, to simplify a bit the explanation below.

Using the Group By functionality and some Joins, you should be able to get a list of vehicles, including price of the vehicle, total price of parts and total price of extra options all from one single (more complex) Aggregate:

  1. Create an Aggregate for VehicleDB 
  2. Join VehicleDB with or without WorkOrderDB (or "only with" if every VehicleDB will have at least one WorkOrderDB)
    • Apply Group By to all the attributes of VehicleDB you will need later (that guarantees you will only have one row per vehicle, and all the info of vehicle you need) 
  3. Join WorkOrderDB with or without Part (or "only with" if every WorkOrderDB will have at least one Part) 
    • Apply Sum to the Part.Price (that gives you the total price of your parts)
  4. join WorkOrderDB with or without ExtraOptionsRelation (or "only with" if every WorkOrderDB will have at least one ExtraOptions) 
    • Join ExtraOptionsRelation with or without ExtraOptions (or "only with", if you used "only with" in 4.)
      • Apply Sum to the ExtraOptions.Price (that gives you the total price of your extra options)

For 3., if you have multiple part entities, like your current data model, you will need to:

  • Join WorkOrderDB with or without <each part entity>
    • Add a New calculated Attribute called PartPrice that will add the Price attribute of each of the part entities (this will give you the price of the part, regardless of which part it was). Expression should look similar to: Flooring.Price + TieDowns.Price + ... + Cut.Price + Conversions.Price
    • Apply Sum to the new attribute PartPrice (that gives you the total price of your parts)


Hey Guys,


Thanks you for your input as I have been working on this for a while and your tips have definitely helped. Currently I am getting everything to display nicely on the main inventory screen, no duplicates but I am still running into one issue. The prices for all the parts not the extra options, are not all adding together to get the final total for the parts. I have recreated some tables to fit a better data model and i don't think that has anything to do with it. When I get the aggregate to sum of all the parts it only shows up with the one line and takes the price from the first part in the list of (Part)Id. I created a get statement to finally get the prices of all the parts on that work order but there has to be a better, more efficient way.



Only pulling up ConversionId Price, When I try to add another join for another part I get 0 

Expression to get prices, there has to be a better way lol

Relations

 Let me know what you guys think

Hi Bryan


That second DB model looks much better, but still has room for some improvement. I would suggest an entiy relationship of One-To-Many of the Workorder (one) and the Parts table (many). That would mean Deleting from the Workorder table all the specific part Ids (BackRowId for example) and making a WorkOrderDBId foreign key in the Parts table. That way when you want to get all the parts for a workorder you can just do an aggregate on the parts table filtering by WorkOrderId, and sum the prices accordingly. If you want to know the price of a specific part type for a workorder, you can just add the ParttypeId = Entities.PartType.Whatever to the aggregate as a filter (and if the aggregate is empty it means that there is no such part).



Right now you're getting just one line because you'd have to do a join with WorkOrder and Parts for every parts type, and beacuse the ConversionDb Id is in the workorder table (which is the inverse of what I suggested, implying that one workorder only has one part).


Hope this helps!

   CLSJ


PS. I am glad to see a developer so open to so extensive changes in the sake of a better coded app. That is not as common as should be! Props!


Hi Carlos,

Thank you so much for this information as it is making so much sense and is helping me along. The only issue I have with using that suggestion to the DB is that I am currently using combo boxes to elect the different options on a car in order to fit our current business process in making work orders as well as displaying them with limited room for error. I am currently trying to think of a way to ensure that all these different parts in these different categories get selected and displayed the correct way.


(Create Work Order)



is there a way I can create a list, save all of the combo box variables then iterate through the list and save all of those parts? I would assume this would work but would be problematic when trying to retrieve all of the parts and sorting them into there respective areas on the Work Order Detail Screen, I'm wondering, even know I don't want to, if I should I break up all of the parts into separate entities again?



(Work Order Detail)



Carlos, I'm open to pretty much any changes! As a beginning developer in this system I'm all for learning as much as I can and sometimes trying and failing at parts because is how I learn the best. I am so grateful there is such an amazing community behind this program as I've been stuck in spots in other dev projects and didn't have a community to turn to. Thank for all your help! Let me know if you have any suggestions about how to go about this portion as I am currently trying to thing of a good way myself.


Thanks,

Bryan

Ah I see, every part has its own catalog to choose from as well.

Assuming you follow my earlier DB recommendation,

In that case I would suggest adding a second static entity (PartOptions for example) for all the part sub-options ("Comercial grade flooring, lowerd only" and  "Retractable Tie Downs Set of 4" from the examples I can see on the screenshots). Add to that PartOptions static entity a FK Id to the Parts static entity and fill those out accordingly.


Then in the CreateWorkorder screen comboboxes to get each part choices you can do an aggregate on that PartOptions table and filter by the specific PartId. To save the combobox variables I would recomend a structure that contains the Ids of each and create a ScreenVariable of the type of structure. Then, when you save a workorder you do a create PartDb for each of the combobox choices (checking that the SelectedId is not null)


Hope this helps!

   CLSJ

PS. I'm assuming the PartOptions catalog will not change frequently and there will be no back-end to add/remove those choices. If I am wrong in this then the PartOptions has to be a non-static entity so you can access the Create, Update and Delete actions through code. And you would need to bootstrap the PartOptions manually.

Okay I'm in the process of completing the above post and its going well but it has brought me to another question of displaying this information. Would each piece of information from this from Conversion, Pan, Operation etc. need a separate aggregate in order to display the part information on the WorkOrderDetail Screen? And I also am using the same page to create as I am to edit a work order and given the new process its probably going to be easier to wipe any parts related to that work order and write a new set. I'm just wondering if I'm thinking along the right lines for when I'm done with the previous posts steps. 


Also thank you so much again for all this help, Id still be working away at this by myself at a much slower pace haha.


-Bryan

Solution

Hey Bryan


Happy to help. Yes, if you want to display each option separatly (in the comboboxes for example) it will take a separate query for each. The other option is to check if it is a new WorkOrder (WorkOrderDbId = NullIdentifier()) and if not you can display a list with the parts found for the order and hide the comboboxes. That would take only one query for the workorder parts.


Hope this helps!

   CLSJ

Solution