I am working on an application to track dealer performance in scheduling truck pick ups on time. I do a daily import of information from our enterprise software keyed on Sales Order# with the following fields:
SalesOrder# , ScheduledShipDate , ActualShipDate , TotalLbs , ShippedLbs , RemainingLbs
Lotus Calculates the number of Trucks needed based on TotalLbs/45000
Customer Service enters in the Lotus app a CustomerConfirmPickupDate for each truck and assigns number of Lbs to each truck based on trucks actual capacity
I need help with capturing (DateTruckWasConfirmed) the date customer service enters the CustomerConfirmPickupDate which can change if the truck is rescheduled. So I need some way to allow the CustomerConfirmPickupDate to be changed but maintain each date and the date they were entered. The goal is to be able to track the following:
1. Has a truck been rescheduled within 3 days of its CustomerConfirmPickupDate (is the new DateTruckWasConfirmed within 3 days of previously entered CustomerConfirmedPickupDate)
2. Did the ActualSHipDate occur after the last entered CustomerConfirmPickupDate
Any help or ideas would be greatly appreciated.