Custom Record Picker Report

Question

We have the following relationships.

Contracts have many Invoices which have many Invoice Lines. [Invoice - Contract Number] is carried down to the invoice line record.
Contracts have many Payments which have many Invoice Lines. [Payments - Contract Number] is carried down the invoice line record.

We are matching invoice lines with payment lines.We are trying to create a custom record picker report in the Payments Table that show, on the Invoice Line record, only those payments where [Invoice-Contract Number] = [Payments - Contract Number], i.e only show payments belonging to the Contract where the invoice is being applied.However we can't get a filter working correctly where [Payments-Contract Number] is equal to [Invoice - Contract Number] in the record picker report.Thanks for any assistance.------------------------------Tim Egerton------------------------------

blakeharrison · Answer

If you're building this report as an embedded report on the Invoice Line record, you won't build in any filters in that report. Your "filter" that matches the Contract Number will be set in the Report Link field properties.

------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------

blakeharrison · Answer

I read your question a second time and wanted to clarify if you're looking to do a Record Picker report so that your users can select the correct Payment when adding a Payment Line record or if you're looking to have an embedded report?

If it's the selection of the Payment, then my previous suggestion wouldn't help. For that, you'll want to decide from which side are people creating these Payment Line records - from the Invoice Line or from the Payment (this will determine our next step).

As with my previous comment, setting the filter in the report isn't the way to accomplish what you're looking for. For this, you'll need to setup a Conditional Drop-Down. Basically, on the Payment Line table, you have 2 reference fields - Related Payment and Related Invoice. On the field that the user selects SECOND, you will need to use the 'Conditional Values' settings in the Reference Field Options section of the field properties:

This is where you will select the Contract Number as the matching value. The first option would be for your Invoice Contract Number and the second option for your Payment Contract Number (assuming your user is adding these from the Invoice Line and therefore selecting the Invoice Line first).

------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------

timegerton · Answer

Thanks Blake,Option 2 worked perfectly.Tim------------------------------Tim Egerton------------------------------

Forum Discussion

Custom Record Picker Report