Good afternoon,
I was asked to create a report to ...
# generalm
mescalante
05/16/2024, 9:38 PMGood afternoon,
I was asked to create a report to list Requisition, corresponding POs, Bills and Bill Payments and I am struggling to do this.
• Any suggestions?
s
Sam-I-Am
05/16/2024, 10:08 PMbest option would be the right choice, since requisition is the applied to transaction and Bill, Item receipt/fulfilment, vendor rtn auth and JE are applying transactions. You reach bill payment by join from Bill (applying tran for PO) because bill payment/credit are the applying tran for bill.
e
Edgar Valdes
05/16/2024, 10:56 PMUff... been there
Edgar Valdes
05/16/2024, 11:00 PMSome problems using Saved Searches:
1. Payment is linked at the line level (main line : false), and the rest are linked at the header level (main line : true). Grouping to remove duplicates is a nightmare in this case.
2. Single Payment can be applied to multiple Bills, Multiple Bills to single PO, etc.
3. And the worst:
a. If you start with Requisition and start linking forward, you will miss the standalone PO. Same with standalone Bill.
b. If you start with Payment and start linking backwards, you will miss the Bills without Payment, etc.
Edgar Valdes
05/16/2024, 11:03 PMAnother problem with Saved Searches is that you can't join more than two tables I think, so it is not possible to get 4 documents (Req, PO, Bill, Payment) because this is not available
Transaction
Transaction : Applying/Applied Transaction
Transaction : Applying/Applied Transaction : Applying/Applied Transaction
etc...
Edgar Valdes
05/16/2024, 11:05 PMI ended up creating a Suitelet and some SQL magic to join all documents
Edgar Valdes
05/16/2024, 11:05 PMWonder if Workbooks would fit this scenario
m
mescalante
05/16/2024, 11:33 PMI was hopping there was a way to at least start from the PO and link everything form there and see how much we can link and capture on a saved search.
e
Edgar Valdes
05/16/2024, 11:58 PMI tried that once. The Requisitions without PO are missing in this case, and Procurement is not happy about it.
m
mescalante
05/17/2024, 4:29 PMIf I can get them a search with PO, Bill and Bill Payment, that will be a big help for now.
I can then give them another one with Reqs that have not been converted into PO's.
2 Views