function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Katerina ParkinsKaterina Parkins 

Workflow or Code?

Hello, I have a custom object with STATUS field which I'd like to auto-populate based on whether the object's child object(s) RECEIVED date field is populated. (or the child's STATUS field if the date field would cause issues) These two objects have a master-detail relationship. There is never a set number of the child objects and can vary from 0 to 15 and they all have their separate RECEIVED dates.

I would like the master object's STATUS to be auto-updated based on the 'lowest' number of the individual entries in the child object i.e. if 2 entries are marked but one isn't then the STATUS field would say 'Partially' until they are all populated, then the STATUS would change as ALL IN.

Can this be done using roll-up summary and field update workflow or would this have to be done by a code? I can't figure out how to create a formula which would check the fields for entries and then update the master status field accordingly. 

Thanks so much for your help.

Katerina 

 
Best Answer chosen by Katerina Parkins
James WooleyJames Wooley
Would it be possible to do this with a couple of roll-up summaries?

One rollup summary would count all child records, so 15 if there were 15 children.
One rollup summary would count all child records, but with a filter criteria that the date field is not null. so if of the 15, 5 had dates, this value would be 5.
Then you could auto-set the status based on the difference between these two fields? I.e. 15-5 !=0 therefore, status = 'Partially'. or if all had dates, 15-15==0 therefore set the status to 'All In'.

I've not validated that all of the above is possible, but I can't think of any reason why it wouldn't be!

All Answers

James WooleyJames Wooley
Would it be possible to do this with a couple of roll-up summaries?

One rollup summary would count all child records, so 15 if there were 15 children.
One rollup summary would count all child records, but with a filter criteria that the date field is not null. so if of the 15, 5 had dates, this value would be 5.
Then you could auto-set the status based on the difference between these two fields? I.e. 15-5 !=0 therefore, status = 'Partially'. or if all had dates, 15-15==0 therefore set the status to 'All In'.

I've not validated that all of the above is possible, but I can't think of any reason why it wouldn't be!
This was selected as the best answer
Katerina ParkinsKaterina Parkins
That sounds promising! I've done the two rollup summary fields but I also need help with writing the formula to auto-set the status ... I've written out the values, but I can't seem to put it all together - I would really appreciate any help. Thank you. :)

Files_Expected__c >  Files_Received__c   then ISPICKVAL(Data_Status__c, "Data Partially In")
Files_Received__c = BLANK  then ISPICKVAL (  Data_Status__c, "Data Not In")
Files_Expected__c  =  Files_Received__c then ISPICKVAL (Data_Status__c, "Data In")
Katerina ParkinsKaterina Parkins
Or I guess I could just do the workflows separately with a field update for each of the options. That's probably the easiest ... ?
James WooleyJames Wooley
Hi Katerina,

Do you need to be able to manually set the Data_Status__c field, or should this always be driven by this roll-up logic? If the field is always driven by logic, I would change this field to a TEXT formula field. Then the formula for that field would be:
 
IF(
    Files_Expected__c >  Files_Received__c,
    "Data Partially In",
    IF(
        Files_Received__c = 0,
         "Data Not In",
         "Data In"
    )
)

If this Data_Status__c field cannot be a formula, you will struggle to set it to the value you want. You'd have to update via a workflow rule and a field update. But a picklist field can only be set to a specific value, not a formula, in a field update. If this is the case you may need to look at a coded solution.
James WooleyJames Wooley
Yes good point, this could be 3 separate workflows, assuming you have a good place to start in terms of filter criteria.
Katerina ParkinsKaterina Parkins
Hi James, that worked perfectly!!! Thanks so much. 
I'm really struggling on the formula front, I always overcomplicate things - any pointers as to any blogs/documents where I could learn?
Again, thanks for your help. I might be back - not trying to sound threatning by any means :)
Katerina ParkinsKaterina Parkins
The text formula field worked but the separate workflows didn't for some reason ... 
James WooleyJames Wooley
A good tip for formulas is to lay them out as I did in my response. This helps to see how the formula is built up, and stops you getting confused about which closing backet belongs to which opening one!

I'd also suggest you use AND and OR rather than && and ||. Again this just helps you lay out the formula in a logical way.

Another tip is that when writing a validation rule, Salesforce is already evaluating your formula and if it evaluates to true, the error will occur. So for example, instead of writing
IF (
     Checkbox__c = true,
     true,
     false
)

You can just write
Checkbox__c

As this will be true when the checkbox is true, and false when the checkbox is false, just like the example with the IF() statement. If you always use this concept, you'll find complex formula become quite a lot simpler.

I find the Salesforce documentation the best place to start for help. It explains what each function does and gives some useful examples of each.
Katerina ParkinsKaterina Parkins
Ignore me ... both options are working :)
James WooleyJames Wooley
Oh and the formula trailhead is probably quite good although I've not done it myself!

https://developer.salesforce.com/trailhead/en/module/advanced_formulas