Unique values in separate rows

[kirdudarenko]

Please help me to automatically transfer unique values from one table to another in each separate row.
Perhaps this task is quite easy, but I can’t find an answer to it, I hope you can do it.

[Felipe_Arnhold]

Hello @kirdudarenko

Before I can help you, I have some questions.

1. This second table, do you want that it has all collumns from the first one?
2. If yes, do you want to summarize some values to these rows that are duplicated? If yes, how? If not, which data must be copied? The first, or does it have some criteria?

Regarding what you want to do:

1. how the data will be inserted into the table? Forms, csv import, manual user?
2. Can the columns (water, sugar) have different values for the same fruit?
3. It is expected that you have multiple rows for the same fruit?

I ask these because depending the answers, I would recommend to convert the Fruits into a select list or even a second table.
Anyway you will need some action (button or automation) to do what you want to do. So you will need to press a button manually or setup an automation to do it for you.

Best Regards
Arnhold

[kirdudarenko]

Hi, Arnhold!
Thank you for responding so quickly.
This table is a simplified example. I need to understand how to automatically collect data from one table and transfer it to separate rows of another. Since with the formula “unique” I have all the data collected in 1 row.
The most important thing is unique names and the amount for their purchase.

[Felipe_Arnhold]

In this case, you need to set up the formula. The goal is to iterate over the first table (filtered as needed) and add rows to the second table.

In your document, I added a button and a table (with the same structure as the main table). The formula in the button performs the action you want.

Let(Fruits.Fruits.Unique(), uniFruits,
  uniFruits.ForEach(
    Let(Fruits.Filter(Fruits = CurrentValue).First(), fruitRow,
      AddOrModifyRows(UniqueFruits,
        Fruits = fruitRow.Fruits,
        UniqueFruits.Fruits, fruitRow.Fruits,
        UniqueFruits.[Water, %], fruitRow.[Water, %],
        UniqueFruits.[Sugar / 100g], fruitRow.[Sugar / 100g]
      )
    )
  )
)

• Let(Fruits.Fruits.Unique(), uniFruits, …): This returns a list of unique fruits and “saves” the list in the uniFruits variable. The Let() function assigns a name to an expression, making the code more organized and readable. This list is a list of strings, not references to rows, so for each item, you’ll need to retrieve the row in the table to copy the content of that row.
• uniFruits.ForEach(…): Iterates through each unique fruit in the list. Inside the ForEach, currentValue holds the current element of the iteration.
• Let(Fruits.Filter(Fruits = currentValue).First(), fruitRow, …): This portion of the formula retrieves the entire row based on the fruit name. I added the First() function to get the first instance. If a fruit is duplicated, only the first occurrence will be considered; all others will be ignored. You can adjust this as desired. This is done using the Filter formula, where the Fruits column value must match the current iteration value. The row is then “saved” in the fruitRow expression.
• AddOrModifyRows(UniqueFruits,
  Fruits = fruitRow.Fruits,
  UniqueFruits.Fruits, fruitRow.Fruits,
  UniqueFruits.[Water, %], fruitRow.[Water, %],
  UniqueFruits.[Sugar / 100g], fruitRow.[Sugar / 100g]): Here, data is being inserted into another table. The AddOrModify() function is useful here. If a row in the second table (UniqueFruits) already matches the fruit name, the content will be modified; otherwise, a new row will be added. Note that this won’t delete a row in UniqueFruits if the row in the first table has been deleted. To manage that, you could add the RunActions() formula to delete all rows from the second table before adding the new ones.

Since you’re more interested in the “how,” I’ve added some explanation. The key is the filter you use in the source table to ensure only the desired data is considered. For example, if you want to summarize rows with multiple entries, you could remove the First() function, allowing Filter to return a list of rows. The AddOrModify formula could then look like this, where Water is averaged across entries and Sugar is summed:

scss

Copiar código

AddOrModifyRows(UniqueFruits,
        Fruits = fruitRow.Fruits.First(),
        UniqueFruits.Fruits, fruitRow.Fruits.First(),
        UniqueFruits.[Water, %], fruitRow.[Water, %].Average(),
        UniqueFruits.[Sugar / 100g], fruitRow.[Sugar / 100g].Sum()
)

Best regards,
Arnhold

[kirdudarenko]

I didn’t expect it to be that complicated and need to create a button. No wonder I couldn’t do it. I’ll be able to look at it in detail in a few hours. Hopefully, I will be able to translate the formula into the main table by analogy.
Thank you for your help and your time.

[Felipe_Arnhold]

I added a second option to visualize the data in your doc.
In this case I created a view instead of a new table. This view is filtered to show only the uniques value. To do this correctly, I added a number column that correspond to the level or the occurrence of that fruit.
It is calculated in this way:

Fruits.Filter(Fruits = thisRow.Fruits).Find(thisRow)

The Find() formula return the position of the row. So the view shows only the rows with level one. In this approach you dont need the action, but you will not be able to summarize the data if needed, since the view has in fact the data itself, so changing in the view will change in the main table.

Best regards,
Arnhold

[kirdudarenko]

I admire your solutions, I think I will need them in the future, I will definitely remember them.
But unfortunately none of the solutions fulfill my task, because they work with displaying data, but not with summing.
I made a table “Table_Result” at the very bottom of the program.
Where I showed how I used to achieve the result.
The disadvantage of my method is that I have to select each time a “fruit” from the list of non-unique ones. And I would like that if I added for example “Kiwi” - it would be added to a new row, and if “Apple” - it would be added to the sum of the existing row.

[Math_24]

what exactly are you trying to do?

[Coda_Solutions]

Hi,
There are many ways to do something in Coda. I had a similar situation with the Google Drive pack. I wanted to pull out the unique file types in to another table (tbl_FileType) via a button.

The first part of the formula is set up such that I will only get unique values and I will not keep adding those unique values to the secondary table every time I push the button.

sync_GDrive.Filter([sync_File type].In(tbl_FileType.Name).not()).[sync_File type].Unique().

The second part of the formula is telling Coda that for each unique value add a row to the secondary table and put the CurrentValue in it.

ForEach(AddRow(tbl_FileType,tbl_FileType.Name,CurrentValue))

Now, while you can add a formula to calculate the total value for each unique fruit in the button, it’s not really good practice as the button becomes unnecessarily complex. Instead, put the total calculation formula in a column of the secondary table with a formula like:

I created a column in my Google Drive table called Numbers to represent the Sum in your Fruit table and a Total column in my File Type table to represent your Total Sum in table 2.

Here’s the formula:

sync_GDrive.Filter([sync_File type]=thisRow.Name).Number.Sum()

So, this is what I would do to make it work. The more experienced Coda gurus could probably improve it.