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 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:
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
Solved! Go to 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:
In the Tasks Table, I made the Virtual Column LongText- Tasks - Materials Used. It has:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("-------------------
โ,[RelatedTimes][Time - Materials Used]), โ- ,โ , โโ),โ,","
โ),โ โ,โ"),"
โ,โ
โ),โ//"),"
-//",""),"-//",""),"//","")
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.
โ-----------------
-//"
or if it had more the one Time added, looks like this:
"-----------------
-//โ
or if it had no Times added looks like this:
โ-----------------
/$/โ
In the Object Table, I made a Virtual Column LongText - Object - Materials Used. It has:
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]<>"-------------------"
)
So thatโs That.
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:
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.
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.
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)
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.
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.
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], โ,โ,""),"
โ,โ"),"- โ,โ
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 โฆ
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!!
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:
In the Tasks Table, I made the Virtual Column LongText- Tasks - Materials Used. It has:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE("-------------------
โ,[RelatedTimes][Time - Materials Used]), โ- ,โ , โโ),โ,","
โ),โ โ,โ"),"
โ,โ
โ),โ//"),"
-//",""),"-//",""),"//","")
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.
โ-----------------
-//"
or if it had more the one Time added, looks like this:
"-----------------
-//โ
or if it had no Times added looks like this:
โ-----------------
/$/โ
In the Object Table, I made a Virtual Column LongText - Object - Materials Used. It has:
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]<>"-------------------"
)
So thatโs That.
Wow!
Great!
Try some list subtraction.
List("" , โaโ , โbโ , โโ) - List ("") = List (โaโ , โbโ)
Or maybe in your case
[List] - List("-")
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |