Forum Discussion

JamieStrauss's avatar
JamieStrauss
Qrew Member
8 days ago

Identifying identical values between two table

Hi Everyone,

It's been a while since using QB and my brain appears to have lost QB memory!

I have two tables: 

  1. Preferred Providers eligible for auto-assignment
  2. All Providers that have been auto-assigned (regardless of preferred designation)

I am looking for a formula that will look for a TIN match between both tables and pull into table 2 under new field "Preferred Provider"

Ultimately, I need to identify the auto assigned preferred providers without scrubbing a list of thousands of TINs.

Any help is greatly appreciated!

formula query
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    8 days ago

    Jamie,

    If this is a process to be run periodically, a Pipeline with a Search Step can easily compare the fields.

    One way is to run when you add a record to the first table.

    Step B will search Table 2

    if there is no match a.tin= b.tin

    then create a new record in Table 2

  • Mez's avatar
    Mez
    Qrew Cadet
    6 days ago

    Does the Preferred Provider table only contain unique records based on TIN, and is TIN the key field? If yes, you could create a relationship, if one doesn't exist, between these two tables {'Preferred' is parent, 'All' is child} using the existing TIN field on the "All" table as the reference (so you can keep your existing data intact) and then add a lookup field for "Preferred Provider" from the parent table (assuming a checkbox) to the All Providers table. 

    Or, similarly without a relationship and using pipelines, in addition to what Don has mentioned, depending on frequency of updates to the "Preferred" table you could trigger on new event or have a run once pipeline to update all existing records on the 'All' table, then if the TIN is the key field using the Look Up a Record action will either result in a record found or not. If TIN is not the key field but this table only contains unique records based on TIN, limit the search query to only return one record. Then use a conditional step to evaluate this (if exists), then you can have a happy path (update the 'All' record field [preferred provider]) or needs further action path (send an email or post a message or assign a task). 

    edit: Just noticed you tagged as formula query. If "Preferred Provider" field on the 'All Providers' table is a formula field checkbox you can: 

    If TIN on "Preferred" table is the key (assuming field ID 6):

    GetRecordByUniqueField("key", 6, [_DBID_All_Providers]) inside Size() and if it's 1, then true, false.

    If( 
  Size( GetRecordByUniqueField("key", 6, [_DBID_All_Providers]) ) = 1,
  true, 
  false
)

    OR

    Use GetRecords("{6.EX.[TIN]}", [_DBID_All_Providers]) inside Size() and if it's 1, then true, false. 

    If (
  Size( GetRecords( "{6.EX.'"&[TIN]&"'}", [_DBID_ALL_PROVIDERS]) ) = 1,
  true,
  false
)