Struggling with condition for format rule

[BOTFORCE]

Hello community,

I am building an App for a GYM. When a new customer signs in for a subscription he gets advice about lifestyle, nutrition, shakes, etc. The Gym send the related pdf’s (documents) to the client by using the app functionality, - but only in a certain order over time. In this app I want to apply a format rule:

Rule: only the already send documents should highlight green.
I have this format rule, which makes all documents all the time green highlighted: IN([ATTACHMENTS], LIST([_THISROW].[ATTACHMENTS]))

Table structure: CUSTOMERCHECKIN
primar key: uniqueid
Enumlist (Names of Documents): attachments
foreignkey (to the CUSTOMER table): nhtc_id

I am able to send multiple documents per E-Mail and expected result is only to highlight the documents green which already have been sent.

Thank you and best regards,
Robert

[WillowMobileSys]

The app will need some way to know when a document has been sent. Probably the easiest way is to explicitly list each PDF as sent. To do this, add a column to the Customer table for [Sent PDF]. As each PDF is emailed, update the [Sent PDF] list. Then use this list to determine which Attachments are marked green.

[BOTFORCE]

Hi John,

thank you for your reply,

I have a send-date in my columns, the issue I’m struggling with is that I use an EnumList. I can select and send multiple docs at once. So my table content looks like this:

UniqueID	NHTC_ID	Attachments	Sendedatum
3270dd90	Customer 1	Limalaya	23.12.2020
023f94fc	Customer 2	Breakfast Continuum	23.12.2020
6a9cda93	Customer 3	Info HFM	23.12.2020
a34e728b	Customer 4	Breakfast Continuum , Limalaya , Mittagessen	23.12.2020

As you can see, customer 4 received already 3 documents with one e-mail.

Thx.
Robert

[WillowMobileSys]

Robert:

Ok, we need some more details…in the view above, the Attachments field shows ALL of the possible PDF’s? Is that correct? And you want only those that have already been sent to be highlighted. Where, in the app, do you get this list from and how is it defined?

[BOTFORCE]

Yes, the Attachments field shows ALL of the possible PDF’s in the screenshot. All of them are highlightes green alltough I only send two of them.

“And you want only those that have already been sent to be highlighted”: Yes

This list is an EnumList hosted in the table : KUNDENCHECKIN

Thx

[WillowMobileSys]

I totally understand you have the KUNDENCHECKIN table and the Attachments column reflects a list of PDF files that have been sent and the date sent. In your example above, you mentioned a row for Customer 4 reflecting 3 PDF’s sent. I presume you will later have more rows for Customer 4 with additional PDF’s sent?

I’m a little confused about how the sample view you’ve included is being used. It seems to be a view into the KUNDENCHECKIN table. Are users inserting rows and selecting which PDF’s need to be sent next?

Is your intention to help them by highlighting which PDF’s have already been sent? Is the selection list hard-coded into the column?

Bottom line, I don’t think EnumList selection items can be individually highlighted. Though this conversation seems vaguely familiar to me. I’ll see if I can find it.

In any event, if I am following correctly, I think there might be better way to handle this.

[Marc_Dillon]

WillowMobileSystems:

Bottom line, I don’t think EnumList selection items can be individually highlighted.

I’ve been following along, and I’m not so sure about that either.

Perhaps if the EnumList had a base type of Ref, and the format rules were actually applied to the referenced table. Although that may bring about a whole other set of issues…

I’d also like to know what @Robert’s intended purpose of this EnumList is? Do you wish to just display documents sent vs documents not sent, or are you also wanting the ability for users to trigger certain documents to be sent via email?

[BOTFORCE]

Well, the userinterface is not for the customers but for the personal trainer (PT). If the PT selects the client from a list he can see details about this client like:
Coaching Subscription, Payments, Supplements, Documents provided according to training schedule.

Now we assume the PT wants to sent the next set of documents. As the PT has hundreds of clients, He should be able to see which documents has already been sent and which not. Therefore it would be fine the have the documents sent already highlighted and the others not. This is how the userinterface looks like. The PT selects (multiple select) the relevant documents and clicks on “Save”. The the Workflow automatically sends the email to the client with the selected PDF-Files. Then it would be nice to highlight also the other button’s in green the next time the PT wants to send new pdf-files.

The table is being updated with one records. In the columns Attachments, you can find the names of one or more documents sent. The granularity for this table is the send date and not the name of documents. The reasons is that the PT otherwise need to repeat the entry multiple times instead doing it at one.

Tx. again and br.
Robert

[WillowMobileSys]

Robert:

The PT selects (multiple select) the relevant documents and clicks on “Save”. The the Workflow automatically sends the email to the client with the selected PDF-Files.

Ok, I think it’s clear now. Based on this comment above, one glaring problem is that there is nothing preventing a PDF from being selected and sent additional times. The standard to prevent that from happening is to remove the “invalid” list items - i.e. PDF’s already sent - and only show those the PT can send.

To make this happen there needs to be a slight design change. Create a table with the list of PDF file names in it. Then, as @Marc_Dillon mentioned, update the EnumList column to have a base type of “Ref” with the base table that of your new PDF table.

Once setup, you can use a SELECT() expression in the Valid_If to pull in as selection items only those PDF’s that have not yet been sent.

With this setup, users may no longer care to see PDF’s already sent. BUT, if they do, then I would recommend a display only column (probably a Virtual Column) for Sent PDF’s and obviously assigned the list of PDF’s sent to that customer from your KUNDENCHECKIN table.

[BOTFORCE]

Robert:

IN([ATTACHMENTS], LIST([_THISROW].[ATTACHMENTS]))

First of all, Merry Christmas and thank you for supporting me.

I have created - as recommended - this reference table with pdf-list (CHECKIN - with following attributes
[CheckinID], [Attachments], [Link]). In the KUNDENCHECKIN Table I referenced the Attachments attribute to the CHECKIN Table and is working flaweless. As the display to see what has already been send and not is not unimportant I created a Format Rule.

Then I went to the Formatting Rules (having green bullets of those documents that has been sent for the selected client) and created a Rule with the following condition which is not working: IN([ATTACHMENTS], LIST([_THISROW].[ATTACHMENTS]))

Are you able to help me with the condition?

Many thnx,
Robert

[WillowMobileSys]

Sure!

I not completely clear how you are referencing the new table. Could you show what the new table looks like AND what the KUNDENCHECKIN Table now looks like?

[BOTFORCE]

Thx:

NHTC_ID = Customer ID
BR,
Robert

[BOTFORCE]

I hope you didn’t missed my update.
Thx
Robert

[WillowMobileSys]

No. I started to reply but wanted to think about it.

First thing, because you want to be able to select multiple PDF’s to send, you can change the Attachments column in the KUNDENCHECKIN Table back to EnumList. BUT, edit the column and set the Base Type to “Ref”. Due to a bug, you’ll need to save at this point and then go back to the column to see a Base Table property which you can then set to the CHECKIN table.

These changes will get the column back to multi-select capable.

Indicating the sent PDF’s for a particular user using Format Rules is still going to be challenging. Do you have your heart set on using a visual indicator? Or will it suffice to show the sent PDF’s as a separate list?

[BOTFORCE]

Thank you so far! I can live of course with he list of sent documents.
Thx!

[WillowMobileSys]

Ok, for the list of Sent PDF’s, add a List Virtual column to the KUNDENCHECKIN Table and set its App Formula to this expression:

SPLIT(SELECT(KUNDENCHECKIN[Attachments], [NHTC_ID] = [_THISROW].[NHTC_ID]), ",")

I am not 100% if SPLIT() will do the job needed. I’ll test when I can. The idea though is to take a List of Lists and just make it a single List.

---

In your Attachments column in the KUNDENCHECKIN Table, update the Valid_If property so that already sent PDF’s are not shown and can’t be selected. You would use an expression like:

Select(CHECKIN[Attachments], NOT(IN([CheckinID], [<<Name of new Virtual Column above>>]))

[BOTFORCE]

Did everything you wrote, I have the following issue: My Attachments are marked with a yellow triangle with the text “This Entry is invalid”

[WillowMobileSys · Accepted solution]

Sorry, my goof. In the Valid_If for the Attachments Column in the KUNDENCHECKIN Table, the beginning of the expression should be:

Select(CHECKIN[CheckinID]...

Valid_If should ALWAYS return a list of row keys.

[BOTFORCE]

Thank you so much! Where can I sent you a couple of beer cans?

Everything is working so fine, and I learned a lot more!