LinkedIn
Twitter
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
Solved
1 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.
