varying formulae criteria based on user selected options

[humidor]

I would like to have a form where a user submits a start date and a priority for a given customer. Based on what the user selects for the priority I want the table to generate a schedule (for customer service followups) from the given start date. For example a customer MARY and HIGH priority might generate followups 1 day, 5 days and 30 days after the start date. Alternately a customer JOHN with LOW priority might only generate followups 30 days, 60 days and 90 days after the start date. How best to set this up? I can see how to use formulae to create the folowup dates but how to vary the intervals based on priority setting?

[Mathilda]

On 7/12/2017 at 7:53 AM, humidor said:

I would like to have a form where a user submits a start date and a priority for a given customer. Based on what the user selects for the priority I want the table to generate a schedule (for customer service followups) from the given start date. For example a customer MARY and HIGH priority might generate followups 1 day, 5 days and 30 days after the start date. Alternately a customer JOHN with LOW priority might only generate followups 30 days, 60 days and 90 days after the start date. How best to set this up? I can see how to use formulae to create the folowup dates but how to vary the intervals based on priority setting?

Hi, I would use CASE statement in formula field. Add 3 formula fields: 1st followup, 2nd followup, 3rd followup

E.g. for the 1st followup date:

CASE

WHEN [@field:priority]='HIGH' THEN  DATEADD (dd , 1 , [@field:date_submitted]) 
WHEN  [@field:priority]='LOW' THEN  DATEADD (dd , 30 , [@field:date_submitted]) 

END

You just need to change number in the Dateadd formula for two other followup fields. You can read more here and here

Cheers!

[humidor]

Great - is there also a way to allow the user to override the calculated value from a formula using the submission form? Seems like when these fields are created they cannot be added to the submission form?

[humidor]

Caspio tell me I have an invalid column name when the "priority" field in your example is a 'list-string'? Is this a limitation?

UPDATE - looks like so: http://howto.caspio.com/tables-and-views/data-types/list/ "

• At this time, List fields cannot be used in Rules, Charts, Pivot Tables, Grouping, Aggregations, Parameters, Formula Field, and Calculated Field. Lists will be supported in more areas in future releases."

That is kind of a deal-killer for me, wonder if there's a workaround?