Duplicate Values Between Two Tables

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"

)

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

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?

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.

Mez’s formula will work, but a formula query on every row of a big member table can slow the app down. A relationship gets you the same yes/no natively, and it works fine even when all the data comes in through imports.

Make Provider ID the key field in Preferred Provider, since it’s already unique there. Then create a relationship from Preferred Provider to Member Assignments and pick the existing Provider ID field in Member Assignments as the reference field. Both Provider ID fields need to be the same type.

Add a formula checkbox in Preferred Provider called [Preferred] with this formula:

true

Then add [Preferred] as a lookup field in Member Assignments. Providers on the preferred list show checked and everyone else stays unchecked. If you want the words, a formula text field does it:

If([Preferred], "Yes", "No")