0

Compare two fields from different tables with summing

Matching.ninox

I am trying to compare two fields that should have the same data, and where one or there other doesn't have a matching, I'd like those to be in the list as well. Basically I have a table where I keep transactions and in another table I import a transaction register, and I want to know if I received all payments I am expecting and occasionally they don't but they also occasionally pay me for a transaction I may have forgotten to add on my side.

MyTable with a field called my_reference_number and commission (I should have ONLY one record that matches)

ImportTable with a field called their_reference_number and payout (They could have multiple records of which I need to sum together) 

I have built a relationship between the two and I just can't get anything to display even using basic select statements without summing, etc. Any help on the language would be appreciated! I've uploaded a sample DB that hopefully helps clarify this.

MM

2 replies

null
    • John_Halls
    • 13 days ago
    • Reported - view

    Have a look at the updated file

    I have added some fields and created a button with this code in it

    let a := (select MyTable).my_reference_number;
let b := (select ImportTable).their_reference_number;
let c := unique(array(a, b));
for d in c do
    let e := (create Reconcile);
    e.(Reference := d);
    for f in select MyTable where my_reference_number = d do
        f.(Reconcile := e)
    end;
    for g in select ImportTable where their_reference_number = d do
        g.(Reconcile := e)
    end
end

    Regards John

    Matching.ninox
    • checkyourmirrors
    • Matthew_Moran
    • 12 days ago
    • Reported - view

    Thank you so much for your help, and I really appreciate all that this community does to help out others in supporting our use of Ninox!

Login to reply

Content aside

Categories

View all topics
  • Status Answered
  • 12 days agoLast active
  • 2Replies
  • 27Views
  • 2 Following