Formula Help

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
I am trying to create a specific "field" that will show a start date for a specific task. I know that there are simple ways to show a start date for a task in a report but I need to show the start dates for multiple tasks in one report. I am thinking that creating a new field that will show a task start date would work but I am open to suggestions. Thank you in advance!
Photo of Chris Baker

Chris Baker

  • 330 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
I'm not understanding.  Do you have a relationship where where One Project has Many tasks and you are trying to get some start date(s) rolled up to the Project Table?


Photo of Chris Baker

Chris Baker

  • 330 Points 250 badge 2x thumb
Correct. Our projects have a task list with multiple tasks, their start and end dates and their current status. The report I am trying to build (table report) would show multiple tasks with their start dates.   
OK, fortunately even at 30,000 feet I can still read minds.  (Heading home now after a ski week).

So your lucky day was December 16th, 2018.  We got a new feature called Combined Summary Text.  It's a new kind of Summary field you can create on the left side of your relationship between Projects and Tasks.

It can summarize up to the first 25 unique values from your child record, so in your case up to 25 tasks.

The trick here is that you need to have text fields to summarize and not other field types such as dates.

My suggestion is to create a single field on the Task record to hold the Task name and the start date.

for example:

[Task Name] & " " ToText([Start Date])

Then make your combined summary text field called [Tasks and Start Dates (raw)].

So, a good start, but then you will likely complain that the field is too wide and bubble-like in appearance when viewed on our report.

So a bonus suggestion is to use this formula in a new formula text field to get it to list vertically. Maybe call it [Tasks and Start Dates].

var text value = ToText([Tasks and Start Dates (raw)]);

List("\n",
Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")),
Trim(Part($value,11,";")),
Trim(Part($value,12,";")),
Trim(Part($value,13,";")),
Trim(Part($value,14,";")),
Trim(Part($value,15,";")),
Trim(Part($value,16,";")),
Trim(Part($value,17,";")),
Trim(Part($value,18,";")),
Trim(Part($value,19,";")),
Trim(Part($value,20,";")),
Trim(Part($value,21,";")),
Trim(Part($value,22,";")),
Trim(Part($value,23,";")),
Trim(Part($value,24,";")),
Trim(Part($value,25,";"))


Photo of Chris Baker

Chris Baker

  • 330 Points 250 badge 2x thumb
Thank you so much for all of the help!. I think I am on the right track but I am running into an issue creating the initial field in the Task table. For the field type, I chose "Formula - Text" (I'm not sure if this is the correct way to go) and when I type in the task name as part of the formula it says that "field [Design Prep] is not identified". 
Can you tell me the name of the field that has the name of your task. Can you also tell me the name of the field that has the start date for the task?

If you go into settings for the table and then fields it will give you a list of all the field names.

In addition, in the box where you type in the formula just below it there’s a drop-down box which will provide you the field names.
Photo of Chris Baker

Chris Baker

  • 330 Points 250 badge 2x thumb
I may be misunderstanding what you are asking but the task names (from the task list) do not currently have "fields" associated with them. The start date field however does have a name (Design Start Date - and the field type is Date).
Perhaps I need to rephrase the question.

You are asking to roll up a summary of the child Tasks up to the Parent project record. I have made the assumption that there are multiple Tasks as child records to each Project.

You are saying that you want to show the start date of the respective tasks and the field that holds the date is called [Design Start Date].

Is there is a field you want to use which has an identification of what the task is?

I’m assuming that you would have a field called something like Task Name or Description.

Alternatively, you can contact me directly at QuickBaseCoach.com for one on one Assistance.