Forum Discussion

KallieGoodwin's avatar
KallieGoodwin
Qrew Trainee
8 years ago

Comparing two fields in different tables to produce a out come in a third table.

I need to compare what our records have versus the records of a third party. I get the third party records as a excel/csv and import into a table in my Quickbase app. 

I want to look at Field 1 in our records and compare it to the corresponding or similar field in the other table with the third party records. I am not sure how to start to go about doing this; but my thought is to have three tables total 1) our records, 2) third party records 3) results from the comparison. These three tables would have relationships created between them. 

I am new to Quickbase and not experienced with writing formulas so I need some help here, but this is my thought process on how to accomplish this: 

Table 3 field to compare - CompareFields (formula-text)
Formula: 
If(unique ID = unique ID, 
Then( If [Field 1 from table 1] != [Field 1 from other table] "Mismatch"))

Obviously this is not the correct way to write the formula... suggestions!? Would a Quickbase API be something I should try? 
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    This doesn't happen to be dealing with Telecom does it?

    Regardless, you are on the right track,  You need to get all your data into one spot.

    However, you might be able to keep it just two tables and have the source data (client csv file) be the parent of your files (QB records).  Then you would just have lookup fields.

    How many data points are you looking to compare?  Any other details about actual use case will help us have a better holistic view.
  • KallieGoodwin's avatar
    KallieGoodwin
    Qrew Trainee
    8 years ago
    No, not Telecom. But I see why you're asking many of the threads I've read similar to my question have been in that field. 

    It will be for auditing purposes, and right now it's on a few thousand records strictly to just compare what they have versus what we have.  

    I have it set up now with my source as my parent table in a relationship with our records as the child table. And I have the look up fields set (or a few for now). The more I think about, the more I think the third table is not needed. 

    But I am still stuck on writing the formula to compare the fields and print that the fields don't match. 
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Are they date fields?  what type of 'data' is being compared?

    The formula would most likely be a formula-checkbox.

    Call it "Data Doesn't Match";
    If([Parent Lookup Field]=[Child Data Field], false, true)

    But I'm sure you need to account for when either data point is blank (because if one or the other is blank, then they wont match.  And you don't need those on the report, or you might).

    If([Parent Lookup Field]="", false,
    If([Child Data Field]="", false,
    If([Parent Lookup Field]=[Child Data Field], false, true)))