Comparing 2 tables and displaying values not present in one

Community Discussions
,
1

I have 2 tables. one is called Client_Master and the second one is Client_Characteristics. The first table has 380 entries and the second one has 5 entries. common across both is the Client Name column. i am trying with a form to capture data and filter where i want to see only entries in filter which are missing in the second table(client_characteristics). at this point it shows all the 380 entries from client_master table however i want to see only the ones which are not present in client_characteristics table ( 5entries). i used the formula however it didnt work
=filterdiff(filter(Client_Master,"Client_Master[Client Name] <>%", "XXX"),filter(Client_Master,"Client_Characteristics[Client Name] <>%", "Client_Characteristics[Client Name]"))

3

hi @AjyT-6da5, welcome to Honeycode and the forum.

If you look at the example here:

you see that filterdiff only works on one table out of the box. There may be ways to combine tables to show the differences that you are looking for but I'd need to know more about what you are trying to do.

  • Are you just trying to find customers that appear in Client_Master but not in Client_Characteristics? (just names, not any other attributes)
  • Which table will you be "capturing data" into Client_Master or Client_Characteristics?
  • Is there a rowlink between Client_Master and Client_Characteristics tables?
  • What is the role of these tables and what is their relationship?

Thank you.
DT.

5

I am using a filter in app where i want to see customer names which are missing from client characteristics table only

7

hi @AjyT-6da5, thank you for clarifying,

I think you are asking to see the list of Clients that have no Characteristics assigned to them, so you can see Clients that need to be attended to and assigned Characteristics; an Exception list of sorts. If that's the case here is a simple solution:

  1. Create a Clients_Master table

  2. Create a Clients_Characteristics table

  3. Rowlink "Name" from Client_Characteristics table to the original Clients_Master table, so you can only choose predefined clients from the Clients_Master

  4. In the Client_Master table add a field called "NumberOfInstances", set the Column Formula to:
    =FILTER(Client_Characteristics,"Client_Characteristics[Name]=%",THISROW())
    Now it looks like this:

    Screen Shot 2020-08-20 at 1.55.24 AM
    Screen Shot 2020-08-20 at 1.55.24 AM802×626 33.9 KB

    clients that have no records in the characteristics table will have 0 rows, and the clients with characteristics will appear at least once.

  5. In your screen set the Source of the List component to
    =FILTER(Client_Master,"Client_Master[NumberOfInstances]<%",1)
    like so:

    Screen Shot 2020-08-20 at 1.57.33 AM
    Screen Shot 2020-08-20 at 1.57.33 AM1884×696 56.3 KB

At this point you can see only the customers who are in the Master table but do not appear in the Characteristics table.

Also, you can create the characteristics table wide or narrow. Wide looks like this:
ClientA | Characteristic 1 | Characteristic 2 | ... | Characteristic N
ClientB | Characteristic 1 | Characteristic 2 | ... | Characteristic N
Narrow looks like this
Client A | Characteristic 1
Client A| Characteristic 2
....
Client A | Characteristic N
Client B | Characteristic 1
Client B| Characteristic 2
....
Client B | Characteristic N

Depending on your needs, either one should work. In general the Narrow one is more extensible in the future, because you don't have to change table structure to add a new Characteristic, and you can also make Characteristics a picklist from a list of characteristic types. You are then creating a many to many (Client to Characteristic) table.

Hope you like this simpler solution :slight_smile:
Let us know if you have more questions, or if I missed anything.

Cheers,
DT.