Freight Management - Dataverse Table Design

[TruckinAlex]

Hi,

I am trying to build a  simple freight management system for my car carrying business & am new to Power Apps & Dataverse. I have a Job table & a Leg table. A Job can consist of one or more Legs. A Leg is any part of a Job completed by either a company truck or a subcontractor. For example a Door-to-Door service from Cairns to Melbourne would require:

1. Leg1: Pickup Cairns (Subcontractor)
2. Leg2: Linehaul Cairns-Brisbane (Subcontractor)
3. Leg3: Linehaul Brisbane-Melbourne (Company Vehicle)
4. Leg4: Delivery Melbourne (Subcontractor)

I currently have a fk_JobID in the Leg tables but somehow get the feeling this isn't correct?

Any guidance would be sincerely appreciated?

[ChrisPiasecki]

Hi @TruckinAlex,

In your Leg table, you should create a Lookup column, selecting Job as the table. This will create a one-to-many relationship between Job and Leg. It does all the work in the backend automatically to setup the foreign key and such which you don't need to worry about since we don't access the backend database directly.

Now if you find that you need to create a common set of Legs for every Job you create, you can use Power Automate to automatically trigger a workflow upon creation of that Job to create a bunch of Legs and relate it to that Job. If you have different "types" of Jobs, you can use that field value in your flow to conditionally create certain types of Leg records. 

Hope that helps. 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[TruckinAlex · Accepted solution]

Hi Chris,

Thanks so much for your reply...sincerely appreciated. I haven't done any coding/tech stuff for almost 20 years so I'm pretty rusty to say the least. Not to mention jumping into Dataverse was starting to feel like a very deep pool indeed...so thanks for the lifeline.

What I have done, prior to your response, was to go down another level & consider the Vehicle the base element in the data structure as everything involves it. The Vehicle table now consists of only those fields necessary to define that element specifically (i.e. Make, Model, Color, Class, Rego & VIN). It is then assigned an autonumber (i.e. VehicleID) by Dataverse (e.g. V-01000, etc.)

The Vehicle is entered/created as part of creating a Job which resides in the Job table consisting of:

1. JobNumber: A text prefixed autonumber (e.g. JB-001001)
2. JobAvailabeOn: The date from which the customer can make the vehicle available;
3. JobCommencedOn: The date the first Leg commences;
4. JobCompletedOn: The date the last Leg is completed;
5. fk_JobContact: Associated with an Account for billing purposes;
6. fk_VehicleID;
7. fk_Lane (LaneCode, LaneDescription, LaneKms): This is a designated major Australian freight corridor between capital (or major regional) cities such as Sydney-Brisbane (SYD-BNE), Melbourne-Cairns (MLB-CAI), etc. I intend to use the Lane table for rating purposes also down the track;
8. Service Type (Choice): Door-to-Door, Depot-to-Depot, Door-to-Depot, Roadside-to-Depot, etc.;
9. fk_Pick-up Depot: An Account linked to the 1st depot the vehicle encounters in the system. This is the depot either the customer delivers their vehicle to (e.g. ServiceType=DP-DP) or a local subcontractor delivers the vehicle to (e.g. ServiceType=DR-DR);
10. Pick-up Address: A text field used for the address specified by the customer for the pick-up in a Door-to-* service. This defaults to the Pick-up Depot's address in the case of a Depot-to-* service;
11. Pick-up Contact: A text field for one-off point of contact to collect/receive the vehicle in that case where it's someone other than the Customer (defaults to Customer if null);
12. Pick-up Phone: see Pick-up Contact;
13. Fields 5 through 7 are replicated for the Delivery aspect of the Job;
14. Number of Legs: A whole number field containing the number of legs required to complete the Job determined at the time of booking;
15. Job Rate: The rate to be charged to the Account.
16. Job Status (Choice): 1.Booked, 2.Confirmed, 3.Allocated, 4.Consigned/Manifested, 5.Collected, 6.Delivered, 7.Finalised, Pending, Claim;

I then created a flow that creates a row entry in the Leg table. The Leg table consists of mostly the same fields as the Job less the Account Contact. The flow repeats the number of times specified in pt.10 above (i.e. int_NumOfLegs) via a loop.

It forces the autonumber to take on the Job number with a single digit decimal trailer (e.g. JB-001001.1, JB-001001.2, etc.) so it's easy to trace back to the parent job at a glance. However that doesn't create a relationship between the Job & Leg as such. I can't create a subgrid for the associated Legs in the Job's main view. Not sure if I'm diminishing the super power that is relational database by structuring it this way...I'd be interested to hear your opinion on this?!

It also passes VehicleID so that each Leg has a definite relationship to the Vehicle & independently of the Job.

This seems to have given the desired affect. I can now view a Vehicle in the main form view with an associated subgrid for both the Job & the Legs, which is great & the type of "natural" relationship I was looking for.

I've been struggling with the logic to populate several of the Leg fields from Job fields dependent on Leg sequence number (e.g. 1st Leg pick-up address is the Job pick-up address if ServiceType starts with DR else PupDepotAddress) but your suggestion of specifying a LegType (i.e. Pick-up, Delivery or Linehaul) reduces the number of scenarios I need to consider...thanks very much.

Any thoughts on connecting a Job/Vehicle/Leg to a manifest? I'm thinking I'll only use manifests for my own linehaul trucks & the Legs that go on them & simply consign those Legs that I give to subcontractors. My thoughts are that a manifest is a record of consignments allocated to travel together on the same vehicle, on the same lane & at the same time. That makes sense for the internal Legs travelling on my trucks (I want to be able to see revenue per vehicle, etc. so manifesting achieves that).  But the jobs I consign to other carriers don't necessarily travel together or on the same date or even on the same lanes...so I don't see rationale behind it?!

This stuff is probably basic & obvious to most here, but believe me, coming from the Australian trucking industry, an industry that has traditionally frowned upon technology...it's really hard to find anyone to discuss THIS stuff with!

So, again, my sincere thanks for your time & thoughts...

[TruckinAlex]

Sorry,

I failed to mention the main point of creating Legs...to assign the parts of the Job my company doesn't perform itself to subcontractors. This is turn allows me to keep track of outstanding Legs, as well as giving me the ability to automate the whole booking, notification, completing of the Jobs via Power Automate & Power Apps.