Creating metadata when user adds to multi-select lookup

Let’s say a user is in a “Recipes” table and they’re adding ingredients via a standard lookup to an “Ingredients” table. The lookup is set to multi-select, so they can come back any time and add more ingredients. What I want to do is extract the metadata when someone adds another item in the lookup.

For example, the ingredients lookup might already have “carrots”, “onions” selected. A user comes in and selects “salt”, so now there are three items selected in the lookup. What I want to be able to find later is:

  1. what ingredient was added (“salt”)
  2. who added it
  3. what time it was added

I thought I could solve it with a separate table (e.g., “Ingredients Added”) and an automation, such that a change to the “Ingredients” drop down list would trigger the creation of a new row that captured 1-3. The problem is I can’t pull the ingredient or the user. The automation creates the new row and adds the date/time using Now(), but I can’t figure out how to get it to pull the reference to the ingredient or the user who added. I thought I could get the user, but the only options to use when creating the row in the automation are the automation bot and the doc owner.

Any thoughts?

Hi Andy. I’m not in computer now, but what I’ve in mind : I would set an automation that will copy the content as TEXT from your lookup column after you add something, to a « old ingredients » column. This automation can run just after your first one.

So when someone updates the list

  1. The column is changing but the « old ingredients » list is still the same as the data have been pasted as value
  2. Your automation runs and see the new contents. Then you can somehow substract new and olds value to have the added ingredients.
  3. The end of your automation will replace the « old ingredients » as new one.
    4 ready to new modification

Don’t know if that would work

I can check that later on computer

Cheers

Nice, thanks Quentin! I’m still experimenting, but I think that’s got the ingredient part solved.

Now just need to figure out a good way to identify the user.

The row property CreatedBy() might help you here :blush:

I thought so too, but it’s only picking up automation bot or the doc owner atm. I’m experimenting with a few things but not really sure atm…

That’s a good question, when an automation run, if you want to use User() value, which user will it take into account if several users are connected in the same time :slight_smile: ?

I’m trying [step 1 Result].ModifiedBy() - think it will work.

1 Like

Yep, I think we have your whole proces, at least in our minds :stuck_out_tongue:

1 Like

That’s indeed an interesting question to which I don’t really have an answer :sweat_smile:

But, as automations have to do the server side of Coda, I guess that would be the user actually triggering the automation first :innocent:

I’ve found that topic while searching how to solve that mystery

I don’t know much about your setup, but I probably would have used a button to add an option :innocent: (I’m a button kind of maker :blush: )

I thought about a button, but in this case I’m trying to make it as painless as possible for the user.

Got it working with modifiedby! Thanks for working through this with me!

Now I can create a timeline of all ingredient changes to the “Recipe” table. I can see who’s updating Recipes and what Ingredients they favor adding.

Of course recipe/ingredient is just an example, not what I’m actually using this for. Will post an update when I’ve got my actual use case cleaned up.

1 Like

I also use fruit example when I’m handling nuclear code or 7-digits salary informations :sunglasses: :smiley:
Happy to have provided a little help on that interesting subject :wink:

In either case I hope the fruits are stable safe!

1 Like