COUNT from another table?! (Dynamic Formula)

[Atlas]

So, I have two tables DATA and PROJECTS and both of them has the same column name “[PROJECT]”.
I added virtual column to DATA table, trying to count the names from PROJECTS table, with the app formula below. It counts all the project names… I want to count the names which I have already selected in my DATA table in PROJECT column.

COUNT(SELECT(PROJECTS[PROJECT], [PROJECT]="PROJECT"))

Thanks for help.

[WillowMobileSys]

If I am following you correctly, you have added a Virtual Column in your DATA table which you have named PROJECT. It hold the named of the assigned project. You now wish to count the DATA items that have an assigned project. I’ll assume you wish to count only UNIQUE items.

If this is right, then you can simply do this:

COUNT(SELECT(DATA[PROJECT], ISNOTBLANK([PROJECT]), true))

The “true” value tells the SELECT() to include in the resulting list only unique values - i.e. no dups.

Please clarify if I have missed something.

[Atlas]

Thank you Jhon for your suggestion, but this gave the same results.

Results

728 project name in the table:
COUNT(SELECT(PROJECTS[PROJECT], ISNOTBLANK([PROJECT]), TRUE))

21 UNIQUE project name
COUNT(SELECT(PROJECTS[PROJECT], ISNOTBLANK([PROJECT]), FALSE))

DATA table is my parent table which I use adds and updates,
PROJECTS table is my reference table which I use PROJECT names to reference for PROJECT column in my DATA table for project and bloc names.

Project names in the PRJOECTS table are not unique, it has iterant names of projects.

I added VC in my DATA table, to see how many iterant name are in my project table…

I hope I explained it well.
Thanks for your help

[WillowMobileSys]

Atlas:

I want to count the names which I have already selected in my DATA table in PROJECT column.

I think you’ll need to elaborate on this request above. We must be missing some details to understand what it is you are after. Images of your data tables helps a lot.

[Atlas]

İt’s my fault sorry for bad explanation and taking your time.

Here you can see my tables

DATA TABLE

PROJECTS TABLE

PROJECTS TABLE CONTENT

As you can see in the projects table I have iterant project names, but I have many unique bloc names. What I want to do is to learn how many blocs I have in the same projects. To see that, it would be easier to count project name.

When user fill out the data form, first they choose project name. I hope to find out how many times this project name has passed in the projects table.

[WillowMobileSys · Accepted solution]

Ok, so your DATA table has a Ref column to the PROJECTS table. And since Project ID is the key in PROJECTS, the DATA table will store that Project ID.

Atlas:

What I want to do is to learn how many blocs I have in the same projects.

To rephrase this, you want your Virtual Column named “countif trial for projects” to show the count of the number of unique Blocs that have the same TEXT in the column [Project] of the PROJECTS table based on the Project ID selected in the DATA table? Do I now have that correct?

If so that expression would be:

COUNT(
      SELECT(PROJECTS[BLOC], 
             [PROJECT] = ANY(SELECT(PROJECTS[PROJECT], 
                                    [PROJECT_ID] = [_THISROW].[PROJECT]
                                   )
                             ), true
            )
      )

The second SELECT() is there only to retrieve the name of the project so… you could simplify this by adding a [PROJECT NAME] column to your DATA table and assign it [PROJECT].[PROJECT]

Then the expression above could be written as:

COUNT(
      SELECT(PROJECTS[BLOC], 
             [PROJECT] = [_THISROW].[PROJECT NAME], true
            )
      )

---

CAUTION: Because your input for the project name in PROJECTS is TEXT and not selected by a dropdown, you will likely get instances where the typed in project name is misspelled or entered with a slightly different spelling. This will throw off your numbers. I would use a dropdown to select these names.

[Atlas]

@WillowMobileSystems You are awesome!

This is exactly what I wanted. Thank you so much, I have learned from that a lot…

COUNT(
      SELECT(PROJECTS[BLOC], 
             [PROJECT] = ANY(SELECT(PROJECTS[PROJECT], 
                                    [PROJECT_ID] = [_THISROW].[PROJECT]
                                   )
                             ), true
            )
      )