How to display a list of lists, separated by a new text line?

[sorin_mihai]

Hello,

So I am trying to do something I’m not really sure how to make, but then again, I know what I need to obtain and I am really confused about what I’m getting :))

---

So in a task management app, the Structure of the app is as follows:

• A Project has many Objects
• An Object has many Tasks
• A Task has many Times

A Time is the Time spent by a User on a certain Task. So the Users Complete Times to Tasks as they Work on that Task (in order to produce an Object which is part of a Project).
So the Total of Times spent on Completing Tasks will at the end, amount to represent the Workmanship spent on that Project.

Now I would like to add the possibility to add, when completing a Time (through a Form), the Materials Used while working on the Task. So I just simply create a LongText Column in the Times Table, where Users, as they fill in the Time, also add in LongText the Materials they used (plywood, laquer etc.)

So far so good.

A Task has Many Times. And of course, since the Users fill in the Materials they used through Times, I would like to be able to see in the Task, all the Materials used for that Task. So I made a Virtual Column with this Formula:

[Related Times][Time - Used Materials]

and in the detail view, I get the Materials filled in the different Times, separated by a comma.

1. First question - is there a way to get these different Materials filled in at different Times, separated by a new row? So that the next Material from a different Time is displayed in the next line?

But now the weird part, where I get completely lost, is when of course, I would like to see also, all the Materials used in an Object. So I make another Virtual Column in the Object Table and put in the Formula:

[Related Tasks][Task - Materials Used]

and this one is like this:

Now you can imagine, I would like to have also all the Materials displayed for a Project.
Any advice here?

What I would like to get is that in a detailed View of a Task/Object/Project, have a list of all the Materials which are filled in by the Users, every time they fill in the Form of a Time Related to these Tasks/Objects/Projects, like this:

• 2 sheets of plywood
• 1 l of laquer
• 4 hinges
• 4 sheets of plywood
  …

Thank you, and sorry for the long story, but I’m in the blind here and I’m not sure how to put it clearly AND shortly.

Thank you,
Sorin

[WillowMobileSys]

Just my gut reaction that I hope sparks and idea.

From a Data perspective, The way I would handle this is by creating a Materials table. I would have columns for Project, Object, Task and Time. Each entry is added at the Time level in your Form. Then I can query at each higher level for the set of records I need:

Task Level - Select for Project, Object and Task
Object Level - Select for Project and Object
Project Level - Select for Project

From a display perspective. I might first try using an Inline table. You can use a LINKTOFILTEREDVIEW() filtering based on which Level you are at. Then I would try to take advantage of the automatic grouping functions to aggregate the counts.

If you don’t find an AppSheet provided view that you like, I BELIEVE you can always fall back on a Virtual Column that builds the Display list from the Material table like you indicated above:

• 2 sheets of plywood
• 1 l of laquer
• 4 hinges
• 4 sheets of plywood
  …

[sorin_mihai]

Yes, I thought of that but it wouldn’t serve the purpose because I would need the users to fill in the Materials Used in the same form as Times. So the Materials must be a column in the Times Table …
I’ll hang a little more to this option as I tend to think, also from a gut feeling about what Appsheet can do, that this is doable.

[WillowMobileSys]

But isn’t that want you need - the ability to enter Materials info at the Times Form Level? The rest of the Levels are just aggregated information.

[sorin_mihai]

Yes, … maybe I didn’t understand what you suggested.
So there’s a Materials Used Table.
What is in this Table? All the Entries from the Times - Materials Used Column?
I think I see what you mean … but it seems like a very complex solution.
You think a more direct way is not possible? without a new table?

I just thought I must have missed something about how to display a list of lists basically ( this must be the name for my question post I guess)

[WillowMobileSys]

In your case its not just a list of lists, its an aggregated list.

Based on my experience, I think it will become “very messy” to try to build an aggregated list from a LongText column. Maintaining such entries in a table is usually much more efficient.

But this is just based on my background. Others may have a clever way that I am not aware of to do exactly what you are looking to do.

[Marc_Dillon]

So if you want each list item to be on a newline in a LongText display, use:

SUBSTITUTE([list] , “,” , "
")

Now if you want each list, in a list of lists, to be on its own row, you have to be a bit more creative. Let’s start by creating another column in your Times table (I think), where you do:
SUBSTITUTE([list] , “,” , “*”) The asterisk can be any other delimiter here besides a comma.
Then in the parent record do:

SUBSTITUTE( SUBSTITUTE([list of lists] , “,” , "
") , “*” , “,”)

That substitutes the remaining commas between lists into newlines, then changes the other delimiter back to a comma.

Or at least that’s a solution I came up with for one of my apps. Hope I didn’t completely mis-read your question.

[sorin_mihai]

Hy,

Thanks for that. Your solution works.
I twisted it a bit in trying to avoid that extra column that substitutes the comma for * so that in the end you know what commas you actually want to keep - in the Times Column for Adding Materials Used in the Form ([TimeMaterialsUsed]), I put the initial value of "- ", so whenever someone adds a material in a Time, it starts with "- " (it’s also good because it suggest to the users to make it look like a list in case they used more than one material in a Time).
And then, in the Task Columnwhich gathers the materials in the Times ([TaskMaterialsUsed]), like you suggested correctly, I put this:

SUBSTITUTE([RelatedTimes][TimeMaterialsUsed], “,” , "
")

And the in the Objects and Projects I added another Substitute directly to the formula, like this:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Treburi Asociate][Treabă - Materiale Folosite], “,”,""),"
“,”"),"- “,”

• ")
  (that is not a bullet, it’s a - ")

And it does a great job, with one exception, maybe you know the answer.

So what I observed is that all those extra commas are there because, in an Object, when it gathers all the Materials Used in all the Tasks, if there are Tasks that have no materials used they end up as a space " ", NOT A BLANK or a nothing … so if there are any Tasks with no materials before some task with some materials, I will get a bunch of spaces before the first entry. Now, because of the formula I used with the "- " I get an empty row, which is clean enough, but is there a way/formula to delete an empty first row in a long text if there is one?

So this is the end resault now in Project, that gathers the materials used in all Objects, that gathers from all Tasks, that gathers from all Times:

And it’s good enough for me, but if I could also get rid of that empty row in the beginning …

[WillowMobileSys]

Sorry, I was under the impression you wanted to aggregate the entered materials lists. Meaning instead of showing two line items of “1 sheet of plywood”, you wanted instead a single line showing “2 sheets of plywood”.

Next time, just smack me!!

[sorin_mihai · Accepted solution]

Well, actually, next time I say I want to make a nice list of lists of longtext, you smack me.
6 hours into it I managed to make it work but it all became so stupidly complicated it’s just something I definitely don’t recommend doing.

But, just in case someone is searching for this and manages to follow the explanation, I will copy paste the formulas I used. All of the complications in these formulas are there to simply help with a nice clean display of all the Items, for my case. However, I recommend @Marc_Dillon solution with an extra column.

So here goes what I did:

---

In the Times Table, I made the Column LongText- Time - Materials Used. It has:

• the Initial Value of : “-”
• ShowIf : [Time - Materials]<>"-"

In the Tasks Table, I made the Virtual Column LongText- Tasks - Materials Used. It has:

• The Formula of:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("-------------------
“,[RelatedTimes][Time - Materials Used]), “- ,” , “”),”,","
“),” “,”"),"
“,”
“),”//")," -//",""),"-//",""),"//","")

• ShowIf:

AND(
Context(“ViewType”)<>“Form”,
[Task - Materials Used]<>"-------------------"
)

(so there were many issues with what I said initially that works … it didn’t work for all cases.

• the first concatenate() - adding the dotted line makes for Objects, what the Initial Value of “-” did for Tasks - it helps me see the beginning of a new see of Materials Used in a Task, at the Object level.
• Substitute (" - ,", “”) - this one clears all the repeating empty Times who had no Materials filled in, except the last one in case the last one was blank as well
• Substitute (" “,”") - for some reason it seems the “” is not “blank” but it is actually " " a space. So because of the first substitute, I would get a bunch of spaces, which I then reduce to one space with this second substitute
• Substitute ("
  “,”","
  ") - so that extra space sometimes remains in front of a line of Materials Used and this substitute removes that one
• Concatenate (,"//") - In case there are Tasks with Times added but no Materials Used, the last "-" will remain because it doesn't have a following comma to be detected by the first Substitute. So I add some extra non-common text at the end, like "//". So now this Task - Materials Used,if it had just one Time added looks like this:

“-----------------
-//" or if it had more the one Time added, looks like this: "----------------- -//”
or if it had no Times added looks like this:
“-----------------
/$/”

• Substitute( -//",""),"-//",""),"/$/","")) so the last three Substitutes check just for these three cases.

---

In the Object Table, I made a Virtual Column LongText - Object - Materials Used. It has:

• The formula:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Related Tasks][Task - Materials Used],“
, -------------------”,""),",","
“),” “,”"),"
-------------------","")"
-------------------","")

• ShowIf -
  AND(
  Context(“ViewType”)<>“Form”,
  [Obiect - Materiale Folosite]<>"-------------------"
  )

• Substitute “
  , -------------------”,"") - this first one deals with the empty Tasks that end up looking like “-------------------”. But I take the ones with the comma in front, because I want to miss the first one so that there is a line at the start of the display of my Object Materials used (this way I avoid that display problem with having and empty row as a started, now I have a line to separate this list from the other column displayed on top. It looks clean and solves a problem )

• Substitute (",","
  ") then for all remaining commas, I make a new row,

• Substitute (" “,”") then again, if there two or more empty tasks next to each other, I clear the empty spaces generated, with just one space

• Substitute ("
  -------------------","")"
  -------------------","") the I delete the last Task if it was empty or if there where more then one Empty Tasks at the end of the list.

---

In the Projects Table I made a Virtual Column - Project - Materials Used. It has:

• The Formula:
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Obiecte Asociate][Obiect - Materiale Folosite],“
  , -------------------”,""),",","
  “),” “,”")

• ShowIf:

AND(
Context(“ViewType”)<>“Form”,
[Proiect - Materiale Folosite]<>"-------------------"
)

• You got this by now.
  Curiously, things get easier the further you go.
  But the reason Projects are simpler than Object is also because I considered that there would not be the case of creating an Object with no Tasks (even if they are empty) to it.

So that’s That.

[Marc_Dillon]

Wow!

[Marc_Dillon]

Great!

Try some list subtraction.
List("" , “a” , “b” , “”) - List ("") = List (“a” , “b”)

Or maybe in your case
[List] - List("-")