We have a saved search which we call a batch report, similar to that you would have from a credit card processor with all the cash sales grouped by a custom field "Batch ID". We populate the Batch ID per cash sale/csh refund in a different process.
We then create a vendor bill and populate the reference number (tranid) of the bill or bill credit enter with the Batch ID such that we can pay the vendor and tie the bill back to the specific group of cash sales that comprise the batch.
Sorry for the detail but thought it was important to provide context.
Problem:
We would like put the Bill Payment date in our Batch Saved search such that we can show the date the batch was created next to the date it was paid. This seems really challenging to me as it need to use the Batch ID from the Cash Sale saved search to find the Date on a Bill Payment where the reference number is a line item of the Bill Payment.
The Batch ID on the Cash Sale is text (875221010276) and the reference number on the bill is clearly a number. The reason we used text on the cash sale is when sorting purposes. However if the Cash Sale Batch ID field needs to change to a integer for simplicity we are fine with that.
Any thoughts on this complex problem and or possible examples about how to achieve this for a novice? Perhaps our approach could be different so we are open to suggestions.