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:
- JobNumber: A text prefixed autonumber (e.g. JB-001001)
- JobAvailabeOn: The date from which the customer can make the vehicle available;
- JobCommencedOn: The date the first Leg commences;
- JobCompletedOn: The date the last Leg is completed;
- fk_JobContact: Associated with an Account for billing purposes;
- fk_VehicleID;
- 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;
- Service Type (Choice): Door-to-Door, Depot-to-Depot, Door-to-Depot, Roadside-to-Depot, etc.;
- 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);
- 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;
- 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);
- Pick-up Phone: see Pick-up Contact;
- Fields 5 through 7 are replicated for the Delivery aspect of the Job;
- Number of Legs: A whole number field containing the number of legs required to complete the Job determined at the time of booking;
- Job Rate: The rate to be charged to the Account.
- 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...
