Join all rows if a column into one row in differnt table

[jase1979]

I'm trying to achieve something that was reasonably easy in another app dev environment. 

I have a table with about 10 records, I want to take all of the rows from that column and join the rows into a single row in a column of another table. 

I could do this with the other software using a computed column that used a "joined list".

So far I've looked at REF_ROWS and CONCATENATE, however, I can't seem to get it to work and I'm not sure if it is the best solution using appsheets. 

Could you please help?

[WillowMobileSys · Accepted solution]

If you need the contents of the target column to update whenever the source is changed, then make the target column a Virtual Column.  It will update on a Sync.  I'm not certain if data updates will also affect a change but you can test that.

If for some reason that doesn't work well enough for you, the only other way to make it update automatically is to explicitly update the column through actions when the source changes.  If you go this route then use a "normal" column.

[WillowMobileSys]

---

@jase1979 wrote:

I have a table with about 10 records, I want to take all of the rows from that column and join the rows into a single row in a column of another table. 

---

to paraphrase, I think what I'm reading is "take all the values of a column for the 10 rows in table 1, combine them into a list and assign that list to a column in table 2".  Do I have that paraphrased correctly?

Yes, this is a simple expression like this:

table 1[source column]

This expression returns all of the values of the source column in table 1 as a comma separated list.

[jase1979]

I think that's correct. I wanted all of that data from every row in the specified column to be pasted into on cell in another table. Thanks for the reply.

[jase1979]

I tried this, in my case "Current Report[Current Comments]" however I get nothing in the cell. 

[WillowMobileSys]

---

@jase1979 wrote:

I tried this, in my case "Current Report[Current Comments]" however I get nothing in the cell. 

---

Make sure the cell you are copying into is of an appropriate LIST type OR Long Text.  Please show us how you configured it.

[jase1979]

I've tried list and long text both in a column and virtual column. Still nothing.

[jase1979]

It seems to be working now. I also changed the source column to long text. There were a few sentences in each row. Thanks for you support. 

[jase1979]

Does the solution you offered copy the contents from the source or reference the contents?

For my application, I need the target cell to update automatically when the source cells are changed. 

[WillowMobileSys · Accepted solution]

If you need the contents of the target column to update whenever the source is changed, then make the target column a Virtual Column.  It will update on a Sync.  I'm not certain if data updates will also affect a change but you can test that.

If for some reason that doesn't work well enough for you, the only other way to make it update automatically is to explicitly update the column through actions when the source changes.  If you go this route then use a "normal" column.

[jase1979]

How would I go about including a SUBSTITUTE function in the same expression? e.g Where ever I there is a "#N" replace it with a name from another column.

I know that I can do this "SUBSTITUTE( "#N", "#N", "Jase") how do I combine it with the above solution which is a virtual column generated by "Current Report[Current Comments]"?

[WillowMobileSys · Accepted solution]

---

@jase1979 wrote:

how do I combine it with the above solution which is a virtual column generated by "Current Report[Current Comments]"?

---

I haven't tested this but I believe you can do this:

SPLIT(SUBSTITUTE(Current Report[Current Comments], "#N", "Jase"), " , ")

SUBSTITUTE() returns a text value.  SPLIT() is used to return the text back into a LIST of text values.  HOWEVER, since you are placing it into a Long Text column anyway, you may not need the SPLIT() portion at all.  I would try without it first.

[jase1979]

Thanks again. I didn't need to use the split function.  It worked without. 

[jase1979]

Well, the expression result works, however, it doesn't carry across to the virtual column. 

[WillowMobileSys]

I

---

@jase1979 wrote:

however, it doesn't carry across to the virtual column. 

---

 I don't know what you mean by this.  Please elaborate.

[jase1979]

I meant when I clicked test expression, it showed the result in the correct manner on the left-hand side of the test window, but not on the right-hand side. It seems to be working fine now though. 

[jase1979]

I'm getting an unwanted comma in my expression result. 

SUBSTITUTE(Current Report[Current Comments], "#N", [Name]) 

The expression above joins all rows from the column "Current Comments", however, it displays a comma in-between every join. I don't want this comma. How could I remove it?

[WillowMobileSys]

You can do another SUBSTITUTE(). 

Note that once you remove the commas, you will have no way to separate the values back out into a list again - from this resultant column that is.

To remove the commas do this (assumes replacing comma with space):

SUBSTITUTE(SUBSTITUTE(Current Report[Current Comments], "#N", [Name]), "," " " )

Adjust the SUBSTITUTE() as needed to get the desired spacing.