Forum Discussion

JamieStrauss's avatar
JamieStrauss
Qrew Member
5 days ago

Duplicate Values Between Two Tables

Hi Everyone,

I will preface this with formulas are new to me. 

I have two tables

Table 1 (Preferred Provider): This is the master table of only the preferred providers identified by Provider ID

Table 2 (Member Assignments by Provider): This table is the universe of all member assignments-both preferred and non preferred. Providers are identified by Provider ID

What formula (and formula field) do I use in Table 2 that will look for a Provider ID match in Table 1 that will populate the new field in Table 2 as yes or no?

This app won't have user entries so I don't think I need a relationship. I just need to be able to match the preffered providers assigned in Table 2 to the master preferred provider list in Table 1.

I tried something super simple which, as you likely know, didn't work.

IF(Size(GetFieldValues(GetRecords("_DBID_ASC_PREFERRED_PROVIDERS.PROV_ID = _DBID_MEMBER_ASSIGNMENTS_BY_PROVIDER.PROV_ID"))  0,"Yes","No")

 

formula query

10 Replies

Sort By
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    5 days ago

    Is your DBID Alias correct?  Go into the Settings of the table; scroll to the bottom to Advanced and then at the very bottom of that screen is your Table's "Real" name (not the name you have on the menu bar).  That could be why you're getting an issue with your table name not being recognized.

    Furthermore, while formula queries are amazing; and can solve for a large number of use cases; they are prone to size-calculation restrictions; meaning that if either of your two tables has enough data in them the formula will simply be unable to calculate (without warning). 

    An alternative strategy is to use Pipelines to create "join" table records which can create a record for each associate member if the Provider ID exists in the providers table, so there is both a [related provider] and a [related associate provider] in the join-table.  Then you can use the standard summary/lookup fields within the relationship to determine IF (has summary function) there are Preferred Providers for that Associate Member or how many (count function) and even which ones they are (summary combined text). 

    If your table of Preferred Providers or Associate Members is going to grow very large, you might want to consider this strategy now; rather than waiting for the Formula Query to eventually fail to calculate at some random date in the future.

    • JamieStrauss's avatar
      JamieStrauss
      Qrew Member
      4 days ago

      Thank you! There will be tons of data in this app so I went the pipeline route. It worked!!

  • JamieStrauss's avatar
    JamieStrauss
    Qrew Member
    5 days ago

    Sadly, with this: If( Size( GetRecords( "{8.EX.[CAP_TIN] }", [_DBID_ASC_PREFERRED_PROVIDERS] ) ) > 0, "Yes", "No") -using a formula check box field, the syntax error I get is the table name is not a recognized field. This can't be that difficult, am I making it too difficult? Or do I just sadly, have to manipulate my excel file before I upload?

    • Mez's avatar
      Mez
      Qrew Assistant Captain
      5 days ago

      Ah! Instead of 'Yes' / 'No' you can just use True, False (as is). 

      If( Size( GetRecords( "{8.EX.[CAP_TIN]}", [_DBID_ASC_PREFERRED_PROVIDERS] ) ) > 0, True, False)

      • JamieStrauss's avatar
        JamieStrauss
        Qrew Member
        5 days ago

        Do I need to do something so the member assignment table recognizes the preferred provider table? Everything looks good except it returns again as the table name is not a recognized field.

         

  • JamieStrauss's avatar
    JamieStrauss
    Qrew Member
    5 days ago

    Thank you! You have the intent perfect!

    I can't use a key on the PROV_ID field on the Provider table because there are duplicates as they participate in different plans. So I tried your second formula and recieved a syntax error on the first quotation

    If(Size( GetRecords( “{ 8 .EX.[CAP_TIN] }”, [_DBID_ASC_PREFERRED_PROVIDERS] ) ) > 0, "Yes,"No")

    I rechecked your formula and moved the quotations to ({"8".EX.    -this returned a syntax error on the first .

    I think I'm just getting old. Ha! Help

    • Mez's avatar
      Mez
      Qrew Assistant Captain
      5 days ago

      Yes, I edited my response as I missed two double-quotes; in the query and around 'Yes'. 

      Using what you've supplied, try this:

      If( Size( GetRecords( “{8.EX.[CAP_TIN] }", [_DBID_ASC_PREFERRED_PROVIDERS] ) ) > 0, "Yes", "No")

  • Mez's avatar
    Mez
    Qrew Assistant Captain
    5 days ago

    You are so close. I like the syntax; wish it could be this easy. 

    Assuming you don't care if more than one record is returned from GetRecords(), then you will just want to check the Size() function has calculated a value greater than zero (at least one record was returned), then mark 'Yes', otherwise 'No'. I don't think you need to get a field value from the other table (Provider main table), since you're just interested in a record existing; I could have a misunderstanding here. 

    If(

      Size(

        GetRecords( { "fid of Prov_ID on 'Member_Assignments'".EX.[PROV_ID] }, [_DBID_ASC_PREFERRED_PROVIDERS] )

      ) > 0, "Yes",

    "No"

    )