Help

Formula Rollup field with Linked Name + Rich Text Information

608 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jaron_Abelsohn
4 - Data Explorer
4 - Data Explorer

I’m trying to use a Airtable to track status on a project that has different potential vendors and customers. I need a way to bring status updates [notes from rich text]* into a field that is aligned with specific milestone [rows]. There can be multiple status updates in the same field e.g. Vendor A has status “xyz”, & Vendor B has status “abc”

My approach has been to create a link between my project table and Vendor table, and then create a rollup field for Status. The issue I’m having is if I add more than one vendor, there’s no way to tell which status update is assigned to which vendor. I think the right approach is to have a formula that would enable concatenation of Vendor Name and the related text from lookup and can display the information in a more readable format

  • "Vendor A Name + “Status Update”
  • “Vendor B Name” + “Status Update”

Etc.

Would greatly appreciate help on solving this issue.

*note ideally I could parse the rich text and take the entry from latest entry, or entries dated within a certain time frame, but I don’t think this feature is supported.

1 Reply 1

You are correct in that you’d likely need a formula field to combine the two fields:

{Name field} & " " & {Status update field}

And you can pull in only the latest status update using a Rollup or Lookup field with with the “only certain conditions” option enabled. Name this field {Latest Status Update}

You’ll need a {Date} field assigned to each status update, and a Rollup field named {Latest status date} with the aggregation MAX(values) in whatever table these multiple status updates are being summarized in. Then in the Status Update table add a Rollup or Formula field named {Is latest?} that asks IF({Date} = {Field pulling the "Latest status date"}, 1, 0). Set the {Latest Status Update} field’s conditions to only look for records with a value of 1 in the {Is latest?} field.