cancel
Showing results for 
Search instead for 
Did you mean: 

Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Highlighted
Roast Chef
Roast Chef

Formula Help - Calculate MINOF Across Tabs where No Entry is Possible


Having a lot of trouble coming up with formula that will allow MINOF Date to be calculated across 4 Detail tabs, where it's possible to have no entry on 1 or more of the tabs.

This formula works fine if each of the referenced tabs has an entry...

$F.MINOF(($D.Service,$D.Maintenance,$D.Support,$D.Modifications),

'SVMXC__Start_Date_and_Time__c')

If one of the tabs doesn't have an entry, then we get the 1/1/1970, 12am return. 

I have tried all manner of IF statements to only apply MINOF to tabs that have valid entries, and it seems MINOF does not handle nested formulas fully to end up with api field names.  I cannot get any combination of these to work -- for example, the following only returns the MinOf the last tab's Start Date and Time Field even though all are filled.

$F.MINOF(($F.IF($F.GREATERTHAN($F.SUMOF($D.Service,'Work_Duration__c'),'0'),$D.Service,''),

$F.IF($F.GREATERTHAN($F.SUMOF($D.Support,'Work_Duration__c'),'0'),$D.Support,''),

$F.IF($F.GREATERTHAN($F.SUMOF($D.Modifications,'Work_Duration__c'),'0'),$D.Modifications,''),

$F.IF($F.GREATERTHAN($F.SUMOF($D.Maintenance,'Work_Duration__c'),'0'),$D.Maintenance,'')),'SVMXC__Start_Date_and_Time__c')

StartDateandTime.jpg

I'm hoping to be missing something terribly obvious here!

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

What if you used a nullif or isnull and set the value to a NOW timestamp so that you still returned the MIN

Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Hi Scott - it seems MINOF only accepts <tab name> as defaulting to $F.NOW() if null always overrides result to NOW().

0 Likes
Reply
Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

We ended up just sticking with javascript on this, as every attempted combination failed -- perhaps would still be useful for others if anyone knew how to solve something like this.

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Is this value required on the FSA side or the browser?

Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Hi Scott - it is applied in MFL and online through the same SFM.

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Is there a target field that is also the same format? Correct me if I am wrong but you want the overall minof any of the declared line types?

0 Likes
Reply
Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

That's correct - same type.  We have no issue filling from one tab via MinOf, or from multiple tabs with MinOf by using a list of $D.XXXXX tabs if they ALL have entries.  Where the problem comes is where we have a null value on one of the tabs.  Using an IF and trying to omit a null value doesn't seem to work in any combination -- I either end up with 1/1/1970, NOW(), or a MINOF just one of the tab values, depending on how I structure the formula.  I tried every conceivable way of doing this (to me), short of writing out IF statements for 4! combinations.

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Work Details Object?

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Also, what type of SFM is it? Standalone to edit existing records? Forgive me for the sporadic questions. I am just trying to make sure I am processing the most accurate set of variables.

0 Likes
Reply
Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Much appreciated.  Standalone for editing existing record.  Header Object is Work Order, and Tabs are Work Detail of different qualifying criteria.  We have a bunch of dependent rules and such which makes the tabs necessary. 

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Which data model are you guys using? Work Order with multiple Products Serviced lines or Parent Child work order record types with a single Installed Product per child work order? Does this apply to all line types or just the ones specified at the beginning of this thread?

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Last question I think. Are you returning the solution to the header?

0 Likes
Reply
Highlighted
Sushi Chef
Sushi Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Trying everything just short of using duct tape, it seems that the Formula editor would certainly benefit from isnulls, nullifs, or where clauses.

With that being said, SFDC handled it well. I subbed your line types for what we had in the box I was working out of.

Minof.PNG

In the filter criteria, I used "not equal to" to pass on null values.  It's a shame that the SFM formula throws a 0 when theres no record.

Highlighted
Roast Chef
Roast Chef

Re: Formula Help - Calculate MINOF Across Tabs where No Entry is Possible

Thanks Scott.  Indeed no issue when doing this on the Salesforce side.  Like you, I tried every manner possible to try to evaluate/exclude null condition but had no luck.  It would definitely be helpful to have some expanded functionality in formulas, especially in moving away from Javascript.  I believe there is an Idea on allowing formula set to On Load, Before Save, After Save, and I'll probably expand on that Idea a bit based on this investigation so far.  Thanks again for your help!