Why does BulletedList create 'clickable' links

Hi Guys,

I have a table with “investments” as the display column, then a series of payment dates, with a checkbox for recording it it was received.

Which itself is generated from another table which calculates the payment dates using a button and FormulaMap/AddRows. I’m trying to make a document formula to display some information from this table. For example: “You have 4 missed payments from ADNOC, XXX, YYY”. Doing this:

Filter([Payment Verification],Date< Today() AND Received=false).BulletedList()

Results in this:

36%20AM

unique(Filter([Payment Verification],Date<Today() AND Received=false ))

Results in this:

00%20AM

And this:

Filter([Payment Verification],Date< Today() AND Received=false).List()

Give me:

09%20AM

Now I can’t figure out:

  1. Why does using BulletedList() at the end of the formula return Clickable Links which open the Investment, where List() returns the dates
  2. Why does Unique() not work to filter out the second ADNOC? I assume it’s because the Filter() returns 3 dates? What does Unique() look at in the returned list, if one thing i’m getting printed is a name and the other is a date
  3. How could I combine these returns to display something like:

You have X missed payments from [ADNOC] and [PETROFAC]

Those two being clickable so I can mark a payment as received?

@Karim_Noseir

Maybe you can refine this into exactly what you want?

Hmm.

So i’m basically making another column with a concatenation. I used:

Filter(markPayments,Date<Today() AND Received=false).missedPaymentRef.BulletedList()

This works with a new column missedPaymentRef with the concatenate as you suggested. Some Q’s:

  1. Why mark display column as you suggested?
  2. Still don’t know why BulletedList returns something different than just List()

@Karim_Noseir

My understanding is that the Display column uniquely identifies the row. I think this is why you were having issues with the Unique() formula. An investment name used repeatedly in the Display column is, by definition, not unique. Pairing it with a payment date using Concatenate() creates a value in the Display column that is actually unique to that column.

They do entirely different things :point_down:

Ah. I actually noticed a difference; ADNOC now opens the row from another table (containing investments) not the row from Payment Verification which lets me click the checkbox.

I’m not sure how creating the row is actually pulling the @ADNOC for a reference when i create the row, and then in the two formula references.

For the list types, how is:

BulletedList(value) - Renders a bulleted list from the provided list or array.
List(item) - Creates a list from the provided items.

Substantially different? One makes a list from a list or an array and has (value) and one make a list from items (different than lists or arrays?) and has (item) (also different than value?). Not sure what the differentiation is there.

@Karim_Noseir

[Investment Name] is the Display column in the [Investment] table.
[Payment] is the Display column in the [Payments] table.

An Investment Name cannot be received or not received. Only a Payment can be received or not received. Like this:

CloudApp

List() creates a new data structure that looks like this in the UI: [a,b,c]

BulletedList() renders an existing data structure with bullets to make it easier for humans to read.

Play with the formulas and it will make sense.

I think the difference is how you are getting the inv name. You’re doing a table lookup, i’m not.

Is there any chance i could share my sheet with you so you can have a look?

Payment Checks has the tables, overview has the list

https://coda.io/d/_dJY6IpBmlA8

Thanks for your help till now.

1 Like

@Karim_Noseir

You’re basically there – when I opened your doc, it was working as you’d wanted. You’ve achieved that by hard coding “@” references as shown below. This works, but you have to hard code it, which isn’t user friendly.

All that remains now is to change that column from hard coded “@” references to a Lookup column, as shown below.

Fun model, btw.

1/2: hard coded “@” reference

2/2: lookup column