function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
David ChurchillDavid Churchill 

Historical Lookup Relationships and Reporting

I have a business scenario that I’m having problems designing a solution for.  I have four custom objects that I'm using to track assignment of toll passes to vehicles and record toll transactions for those passes:

vehicle__c
toll_pass__c
vehicle_to_pass_assignment __c (fields: vehicle_ID, toll_pass_ID)
toll_transactions__c (fields: toll_pass, transaction_date, transaction_amount)
 
The scenario I'm trying to allow for is that a particular toll pass may be assigned to more than one vehicle during a given time frame (say a month).  I have to be able to import the transaction history for a given toll pass over that time frame and generate a report by vehicle that lists all the transactions for all the passes that were assigned to that vehicle over that time frame.
 
I'm not quite sure how to approach this schema wise, some options I'm considering:

1) Add an effective start and end date field to each record in vehicle_to_pass_assignment and write triggers to end date the applicable record when a device is re-assigned to a different vehicle). Then develop a report that lists all the transactions that were attributed to that vehicle during the reporting time frame by querying the vehicle_to_pass_assignment object.
 
2) Eliminate the vehicle_to_pass_assignment object and make the toll_pass a lookup relationship on the vehicle object and then use historical field tracking to try to generate the correct report by using the transaction date and the vehicle to toll pass relationship history to correctly group the transactions by vehicle.
 
3) A variant of #1 or #2: Add vehicle lookup relationship field to the toll_transaction object and develop some sort of custom import that finds the correct vehicle to toll pass assignment by either querying the vehicle to device relationship field history or the vehicle_to_pass_assignment object.

I'm not sure if any or all of the options above are techincally possible  or preferable or what the level of difficulty would be for any of them.  I would appreciate any advice or to be pointed to someone whose tackled a similiar problem.  I'm not even sure how to search for this type of scenario; at a previous company we called this concept "date spanning" and used SQL server as our platform - I don't know if there's a comparible or more correct term in the force.com vernacular.

Thanks,

David

Best Answer chosen by David Churchill
Chris ShadeChris Shade
David,

Unfortunately, I haven't had much experience with REST API's so I'm not sure how feasible that would be. 

It were me, again not knowing much about REST, I'd consider adding vehicle__c to the toll_transactions__c object and have another trigger that would populate vehicle__c based on the transaction date fitting between the start date and the end date of the assignment.

Chris

All Answers

Chris ShadeChris Shade
David,

I think option 1 is the best solution but with one change.  For the toll_transactions__c I wouldn't include the toll_pass but rather the vehicle_to_pass_assignment__c object.  This should allow you to run a report in SFDC based on transaction that has the date, amount, vehicle, and toll pass.

The way you have it structured today, you'd have to have a report whose primary object is toll_transactions__c (because this object has the date you want to filter on) but getting to the vehicle_ID would be challenging.  The objects you have to traverse are, in order of relationship, toll_transactions__c -> toll_pass__c -> vehicle_to_pass_assignment __c ->vehicle_ID.  However, here are the results would be returned... the date on the toll_transactions__c will return the correct toll_transactions__c records.  These all link to only one toll_pass__c, so records returned will be correct too.  Those toll_pass__c however have multiple vehicle_to_pass_assignment __c assignments, all of those assignments would be returned which isn't want you want and it would be challenging to filter those out.

Regarding the end date population, this would be done easily enough using an apex trigger and the SOQL query.

For option 2, I think you'd have to export at least two tables and manipulate the data to get your desired result.  The two tables being, 1) the historical toll pass assignments on the vehicle object and 2) the transactoins on the toll pass.  Just running a report in SF seems superior.

Chris

If you found this helpful, hit the like button.



 
David ChurchillDavid Churchill
Chris, 

Thanks for the advice.  However, one additional caveat that I guess I didn't make clear is that the data that is being used to populate the toll_transactions object is being imported from a file received from the toll authority and it only lists the toll pass number.  So I guess that solution would require some type of custom import to lookup the vehicle_to_toll_pass_assignment__c record that was relevant on the transaction date and import that along with the other transaction data.  I'm not yet familiar with writing custom import routines but I would guess it would be possible to write such an item using the REST API, am I correct in this assumption?
Chris ShadeChris Shade
David,

Unfortunately, I haven't had much experience with REST API's so I'm not sure how feasible that would be. 

It were me, again not knowing much about REST, I'd consider adding vehicle__c to the toll_transactions__c object and have another trigger that would populate vehicle__c based on the transaction date fitting between the start date and the end date of the assignment.

Chris
This was selected as the best answer