Query a common matching field in two tables by formula (Company name)

Table1: Database_Contacts - columns: FirstName, LastName, Company, Phone
Table2: Database_Deals - columns: CompanyName, Product Quantity, UnitPrice, Total, isCustomer

formula at column: isCustomer

Both column defined by the formula are highlighted in pink and green colors.
Result: The formula at isCustomer column always shows the result “false”, even in case of a match for Company name. Is there a better way to identify a company match (isCustomer == true) using a formula?

Hi @Claude_Falbriard,
please, have a look at this example and see if it fits your needs:

I provided two possible functions (isCustomer and isCustomer Alt the latter likely more performant).

From what you say, I think you could have some more advantages if you approach tables from a more relational perspective (i.e. using row references instead of searching trough strings values).

Let me know if you need further info.

it works like a charm! Thanks … now using: Database_Contacts.Company.Contains(thisRow.CompanyName)

You’re welcome!

Again, maybe take some time to think if your model is the most efficient possible.

disclaimer: I’m ossessed with that… :wink:

Dear @Federico_Stefanato,

I always like it when I see that there are given more solutions to one and the same question, thanks sharing :handshake:

More and more I become aware of the fact that choosing the right/best “data model” is the most important. It’s finally the foundation of how your doc/app will work and understood by the user.

Since you mentioned:

Maybe you have some take-aways you want to share in this community :question:

1 Like

Good point @Jean_Pierre_Traets: you are right and thanks for this trigger.

Sometimes I just feel I don’t want to “overload” with unwanted information.
At the same time, talking about the best (or even right) data model could lead to well known “digital jihadism”(s).

Anyway, to be more concrete and related with the aforementioned example, rule of the thumb might be: aren’t we sure that this Company isn’t a first class table itself?
I’m not a fan of structured data in many cases, but these are the contexts where some relationship coherence might pay, in the end.

If we have to lookup something by fetching its name throughout, we should smell something… suspicious.
Therefore, a more relational alternative implementation of this example could be like:

Let me know if this is something useful for anyone (@Claude_Falbriard especially).
Feel free to ask/suggest variations.


1 Like