Add row with a column value that matches a select/relation value

1

Let’s say we have a fleet of boats with a table of scheduled work with a column for boat, a column called “punchlist item” for specific fixes and a column called “routine item” for fixes that come from a table called Routine Maintenance Master List.

The “routine item” column type is a reference to the table Routine Maintenance Master List.

To make it easier to build a schedule of work items for a specific boat I have set up a way to create rows for multiple maintenance items for a selected boat.

There is a checkbox column in the table Routine Maintenance Master List (actually a view…). You check off as many items as you want to add to the schedule. Then, you click on the button “Add checked items to schedule”. It adds the checked routine items to the work schedule for the selected boat (from a page filter console called “Select Boat”). Then it unchecks each item in the Routine Maintenance Master list that was added to the schedule.

The rows are added to the schedule for the selected boat (because the column value is filled correctly) but the “Routine Item” values are simply text and NOT references back to the Routine Master List table, which is what I want. Is there a way to do this? It seems like the value of the page filter [Select Boat] is treated as a referenced value from the boat master list. But, the value from the filter loop, CurrentValue.Item, is returned as text.

The formula for the button is:

[Routine Maintenance Master List].Filter(
  and([Add to Sched] == true,Contains(Boats,
      [Select Boat]))).
  Foreach(
    runActions(
       [All Tasks All Boats].AddRow( 
          [All Tasks All Boats].[Routine Item], CurrentValue.Item, 
          [All Tasks All Boats].Boat, [Select Boat]),
        currentvalue.AddOrModifyRows(currentvalue,[Add to Sched], false)))

(Yes, the maintenance items are unchecked correctly…)

2

Aha. I fixed it!

Instead of using the value CurrentValue.Item I changed the name in the source table to match the destination table–don’t know if this was needed.

I used just CurrentValue: this linked the [All Tasks All Boats].[Routine Item] to the row from [Routine Maintenance Master List]. I guess that because this was ‘inserted’ into a column, it knows to display the matching column.

Now the formula looks like this:

[Routine Maintenance Master List].Filter(
  and([Add to Sched] == true,
      Contains(Boats, [Select Boat]))).
  Foreach(
    runActions(
       [All Tasks All Boats].AddRow( 
          [All Tasks All Boats].[Routine Item], CurrentValue, 
          [All Tasks All Boats].Boat, [Select Boat]),
        currentvalue.AddOrModifyRows(currentvalue,[Add to Sched], false)))

(OK: an aside here–I changed the column name in the [Routine Maintenance Master List] back to ‘item’ and it still worked. So, it sort of like putting a reference to the row in the destination table. Somehow Coda knew which column was the referenced from the source table. But, I can’t tell where I set that… Is this the right way to think about it?)

3

I guess one can’t change a post more than once…

Quick check of docs: the default column value to show in a relation in the destination is the display column of the source, which makes sense. So that is why this works.

4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.