Dear all,
Good day. I am encountering a problem when I'm trying to join the results returned from a saved search of type: Payment File Administration to the system notes table.
Essentially, for the most part, the fields I am after can be safely obtained from the available ones provided by the saved search interface. However, in order to access a specific value I am interested in, I would need to dig into the system notes for each corresponding returned file.
For some additional context, I am filtering the above Payment File Administration saved search in the criteria section by "Payment Type", for which I am specifically filtering by DD (a.k.a. direct debit) type files. In terms of the fields currently returned, I have the following:
ID, Payment Type, File Processed?, Date Created, File Reference, Posting Period, Subsidiary, Bank Account, Total Transactions, Reversed Transaction Count, Total Amount Paid and Total Payments.
Apart from the above fields, I am also interested in obtaining the original Total Amount of the file, before any amendments to the total are carried out due to refunds etc. There does not seem to be a field which provides this information. Having said that, if we had to dig into the system notes of any returned DD record, I notice that the field: "Total Amount" seems to contain the value I am after … in fact it may contain more than a single value, meaning that I would need to filter by the first instance encountered for this field.
After further evaluation, the easy way to filter the correct value would be to specify the filter {systemnotes.field} = 'Total Amount' and {systemnotes.type} = 'Set'. If in some way I may filter out all the remaining system notes value, then, in the results section, I can simply reference {systemnotes.newvalue} to get the information I am after.
In view of this, I attempted to add the following condition to the criteria section in some convoluted attempt to only return results from the System Notes table where the above condition applies. To do so, I added a Formula (Numeric) field whose formula has been set as: "CASE WHEN {systemnotes.field} = 'Total Amount' AND {systemnotes.type} = 'Set' THEN 1 ELSE 0 END", and the entire result is then further set to be equal to 1, such that only when a 1 value applies, would the data be returned from the System Notes table when joining it to the Payment Administration File table.
I would then add the {systemnotes.newvalue} field in the Results tab to simply read the total value.
I save and run the query and no results are now being returned, leading me to believe that of course, this approach is not joining the data correctly.
Would you have any suggestions or way forward on how I could potentially arrive to the figure contained in the "Total Amount" field of the system notes pertaining to a DD Payment Administration file record, please?
Any assistance would be greatly appreciated.
Best Regards,
G