Re: How do i match all the value in the column

167 · 08-19-2021 06:43 AM

I do i get the result true if all value are found
The ID must have all the group which each group must have all the items

match to this table

Thanks in advance

Aurelien

Hi @167

You may want to combine expressions like LIST(), IN(), and AND().

==> that’s not very clear to me, but here is what you may need:

AND(
  [ID]="A",
  IN([GROUP],LIST("1","2")),
  IN([ITEM],LIST("X","Y","Z"))
)

For reference:

167

Thank for fast reply.

All items ( x , y , z ) must be inside group ( 1 , 2 ) and must have the ID (A)

Items list can have 100++ item
Group list can have up to 10 group
ID only have 1

Aurelien

I don’t understand your explanation.
Can you illustrate with an example, or give more context/screenshot ?

(please tag me with a “@” so that I will be notified as soon as you answer)

167

I trying to do a checklist

REQUEST ID: Test 1
PLATFORM: IG, FB
ITEMS: A, B, C, D, E

i receive request(id: Test 1) from customer to photoshop item (A, B, C, D, E) and post on platform (IG, FB)

receive request (image 1)

Items list in the code will appear in the ref_rows for my easy access

Then i will attach each complete image to its code item and will appear in the related online post

Backend table

once i have attached all images and fulfill the request, the request should disappear from (image 1)

Aurelien

@167

Thank you very much for your details.
Can you share your table structure ?
Screenshots are best

167

Table structure for Create

Table structure for online post

product list (user will use the same product list, add in as enumlist for item for the request)

platform list

create request

online post (where i attached completed photoshop image)

Aurelien

Hi @167
Thank you very much for these further details !
You may want to make changes to your structure in order to achieve your goals.

Create Posting Table
(I assume this is the same as Create Request Table ? If so, please use the same name to designate it )

Change the key column to UID instead of Posting ID.
Also, if it’s not the case yet (but I believe so), lease set Initial Value expression : UNIQUEID()
That’s because key column must be unique, and allowing a user to enter it manually is risky and may lead to errors later.
Change Platform Type to EnumList, and when you click on the black pen, set the Base type to Ref, and Referenced table name to PlatformList Table.
Do the same for Code(#) , with Referenced table name to Product List Table

Product List Table

Change the key column to UID instead of Code, and check that UID has initial value expression UNIQUEID()
set the Label column, if not done yet, to the “code” column. That’s what the user will see in the enum list.

Online Post Table

Similarly to the Create Posting Table, please change PLATFORM Type to EnumList, and when you click on black pen, set the Base type to Ref, and Referenced table name to PlatformList Table. Do the same for ID CODE column.
If I’m correct, your column “Posting ID” is type Ref and refers to the createPosting table. If not, please correct it.
I suggest you change that column name “Posting ID”, both in your sheets and in your editor table, to “Request”.

By the way, I encourage you to use consistent column names and table names.

please make a choice between createPosting or createRequest for your table, and stick to it. I would suggest “Requests”, as simple as that.
columns “posting ID” that refer to the Requests table: call these “request” instead
columns “code (#)” that refer to product list ==> call these “product” instead
and so on

Once you are done with it:
In your Request Table :

Please add a new virtual column, that I suggest you call “CHECK_AllRequestDone”, set type Yes/No.
Assuming your Platform list is pretty short, you can use this expression:

AND(
  ISBLANK(
    UNIQUE(
      [Code]-
      SELECT(OnlinePost[Code],
         AND([_THISROW].[UID]=[Request],
             INDEX([_THISROW].[Platform],1)=[Platform]
         )
      )
    )
    -LIST("")
  ),
  ISBLANK(
    UNIQUE(
      [Code]-
      SELECT(OnlinePost[Code],
         AND([_THISROW].[UID]=[Request],
             INDEX([_THISROW].[Platform],2)=[Platform]
         )
      )
    )
    -LIST("")
  ),
  (repeat as often as necessary, and make the index number evolve)
)

Let us know if that works, and please share table structure as you did just before

167

@Aurelien

-LIST("")

this part, just leave it or what should be place inside?

i get error massage:
Cannot compare Ref with List in (INDEX([_THISROW].[PLATFORM],1) = [Platform])

167

@Aurelien

I try to go another way round

since my goal is to make the request disappear from (image 1) when all items are attached to the their assigned platform. I use the count function.

example: I have 2 platform and 5 items

platform 1 : a , b , c , d , e
platform 2 : a , b , c , d , e

COUNT([PLATFORM])*COUNT([ITEMS])=COUNT([Related ONLINE POSTs])

By this, I manage to make the request disappear when count are match.

Problem now is

[Related ONLINE POSTs] will show all posts related to the request ID.

How do I make the [Related ONLINE POSTs] show posts that matches list of platform listed in the request.

167

@Aurelien

I manage to make the [Related ONLINE POSTs] show posts that matches list of platform listed in the request by use expression below in vaild_if

ONLINE POST[PLATFORM]=ANY(LIST([PLATFORM]))

Thanks for helping