topic Re: Count only if another field has a 4 digit number in App Development

Count only if another field has a 4 digit number

Applicable88 — Tue, 17 Mar 2020 07:26:36 GMT

Hello friends,

absolute beginner here.

I have two columns . One column name is QNUMBER and one of the others name is QMTEXT.

QNUMBER is always a unique number, but QMTEXT can be a text or fix range of a 4 digit number.

So I only want to count the QNUMBER's , if there is a 4 digit number in the specific QMTEXT field to the QNUMBER field.

Example:

QMNUM			QMTEXT
321456			1000
325478			1456
415464			Store No.1
321456			9878
546446			Machine 1
454445			1597

So in the example there is only a Count of 4 since the other fields have a text or not a 4 digit number.

Your help is appreciated.

Thank you very much.

Re: Count only if another field has a 4 digit number

Vegar — Tue, 17 Mar 2020 07:45:47 GMT

You can try this expression:

count({<QMNUM={"=Len(QMTEXT)=4"}>}QMTEXT)

But it will only work if you QMNUM is unique as you say. (In you example you had duplicate rows of QMNUM=321456)

Re: Count only if another field has a 4 digit number

Applicable88 — Tue, 17 Mar 2020 11:46:25 GMT

Hey Vegar,

thank you so much so far. That can work out. But I have another problem...

QMTEXT isn't just a independent column itself. Its also a link with another table.

Assume that a 4 digit code is always a fixed ID of a coordinate. So we know that 4000 is a machine table in Room A/Hall C and 3984 could be a cnc lathe in Room B/Hall D.

So there is already a range of coordinates range from 4000 to 9999 in our company, defined in Table2

The first table I showed you was a table for people reporting failures. So instead they put a random text in QMTEXT, they should put the 4 digit number of the coordinate of the machine instead. So we can identify the report number (QNUM) with the coordinates on the other Table1.

Now with your count({<QMNUM={"=Len(QMTEXT)=4"}>}QMTEXT) since the tables are connected I also count the number of all fixed coordinates of Table2 instead of the problems links to the coordinates in Table 1.

To make it clear: I only want to count QMNUM when there is a 4 digit number in column QMTEXT on the same Table 1, without containing the count of the linked column COORDINATES with the 4 digits of Table 2.

Re: Count only if another field has a 4 digit number

Vegar — Tue, 17 Mar 2020 12:28:37 GMT

What if you count QMNUM instead of QMTEXT? I think that will solve your issue.

count({<QMNUM={"=Len(QMTEXT)=4"}>}QMNUM)