I'm trying to make a field that lists the missing information in that row of a table

I made a simple doc linked below, to illustrate what I’d like to do.

Mainly Id like the top table to calculate a list in “What’s Missing” of what fields aren’t checked for a quick reference of what we need from each person.

The second one is there for reference of what it should look like in its current config.

Any and all help is greatly appreciated!

I don’t see any people in this example document. I also cannot see the relationship between the desired list and the list itself. This makes it difficult to understand what the requirements are.

Hi Bill, Thanks for looking at this.

I should have been clearer, you can think of it as a validation checklist. That is to say it keeps track of what information has been validated (by a human) relating to a certain request and unit.

So For example “Full Name” refers to whether the name submitted by a client sufficiently matches their ID for contract purposes. The chart itself as presented will be correlated with another table that actually contains all the details like name, ID Photo, Etc.

Then my other table calls on the Labels to send emails by template (Solved).

I.e. If I’m missing Rta and PMT

The auto emails look something like:

Dear {Name},

We Require The following to complete your request:

  • Please Submit your RTA

  • Please submit your Payment Of ${x} to {y}

Thanks,

Project team.

Using the formula:

concatenate(Format([DB Email Template].Filter(Templates=“Email Open”).Message, thisRow.[Purchaser Names]) , thisRow.Template.Message ,[DB Email Template].Filter(Templates=“Email Close”).Message)

Fyi Template column is the same as “What’s Missing” in the above table.

Okay - makes sense.

As an AI-first developer, I’m inclined to think about a problem like this in ways it can be simplified and streamlined for users to push this workflow through at a high pace. While CFL is capable of building a refined process, the effort is complex and tedious as you have learned. :wink:

In my view, this should be possible with no complex Coda [CFL] iteration formulas at all. Here’s the output from my approach.

It’s a single table that extracts a summation of the list of missing items. It is real-time; if you check off an item, the AI will update the list of Missing items.

I think you can make a copy with this link.

I extended it to also generate the outbound email message. This is also updated as items are checked off. The email messages themselves could be automatically dispatched, or manually reviewed and dispatched with a button.

The prompts I used for each of the AI-generated fields are shown below. This video walks you through the construction of the AI fields.

List of Missing

You are an expert who watches the status of information submissions.

I will give you the status of each submission requirement. A status of FALSE indicates the submission item has not been received.

You will generate a bulleted list of items that have not been received.

Submission Requirements:
<list of fields>...

Email Reminder

You are an expert who watches the status of information submissions. I am the compliance administrator responsible for gathering this information.

I will give you the list of missing items.

If all items are submitted, you will generate a thank you email message.

You will generate an email message to the submitter requesting each missing item in a bulleted list.

Example:
Dear {Submitter First Name},

We Require The following to complete your request:

* PMT
* RTA
* Signed Agreement

Thanks,

Project team.

Submitter: <nameField>
Submitter email address: <emailAddressField>

Missing items: <listOfMissing field>
3 Likes

I copied your doc, Bill, only to learn how you communicate with Coda AI. Learning a new language now :slight_smile:

1 Like

Thank you for this, its exactly what I was looking for.

1 Like