Welcome to Portal

?Unknown\pull-down

Welcome to Zoho Cares

Bienvenido a Soporte de Zoho

Search our knowledge base, ask the community or submit a request.

Hello everyone!

 

It’s Wednesday and we are back with yet another hack.. 

 

I'm sure you've used formula fields to meet some requirements specific to your business. Let's consider an example each for external (customer-facing) and internal facing scenarios where you'd have used formula fields. 

 

i) You are an insurance company and have added a formula to determine the age of your customers to check eligibility for a particular policy. 

ii) You have over a hundred potentials and have created a formula to calculate the commission for your sales representative based on the amount in the potential and stage.

 

Now, in both these cases, you'd want to apply to the earlier records as well, right? Here's a simple hack that lets you do update the formula to the records already created.

 

1. Click on the tools icon at the top right corner and go to Setup.



2. Go to the module concerned, say Leads.




3 You'll find a list of new fields that can be used inside the layout on the left side panel. Select the check box, and drag and drop the field inside the layout and rename it appropriately and save the layout. 





4.Go to leads and select the records that are already available using the corresponding checkbox and click on the three dots at the top. Now,select mass update for the checkbox field that you created .



 




What happens now is a mass update of all the records triggering the formula action as well.


PS: Please note that you can only mass update up to 500 records at a time, by selecting 100 records and navigating to the next 5 pages thus selecting 500 in total.

 

 

Try this and let me know if it helped! Until next Wednesday!


PS: Edit to the post. Initially we had the option to update the formula field based on a field update, but this would update the formula field every time when a record is updated. Hence, now to update a formula field you have to make sure the field to be updated must be invoked with the formula field. Here is a example based on a user a requirement. I have a checkbox field called "Mass update check" In order for the formula field to update based on the checkbox field I have to invoke this field within the formula. Here is the formula with the "Mass update check" check box field invoked.

If(Tostring(${Candidates.Mass update check})=='true',Abs(Datecomp(${Candidates.Candidate Updated Time},${Candidates.Phone screen complete}))/24/60,0)

Check and let us know if you have any further clarification.





3 users find this useful.
35 Replies
Reply
16 more
  • 3 years ago

@Mark Lee

Hi ALL,

I was hoping you could further assist me with my related question on this topic.?
I tried to use the example above but was unsuccessful.

My dummy checkbox field is 'Mass update check'
My formula field is 'Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name})
How do I incorporate the dummy checkbox into the above formula.?

Thanks for any assistance.
L.

@Leslie.

Try the formula syntax below:

If(${Contacts.Checkbox,Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}),Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}))

Let us know if you require any further clarification.

  • 3 years ago

@Pheranda

This is what I enter per your suggestion above.

If(${Contacts.Mass Update check,Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}),Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}))

It does not work.
Error - Syntax Error. Check the examples for any functions you're using to see if you formatted them correctly. Make sure your fields are formatted like this: ${Module Name.Field Name}.

Any other suggestions?
Thanks!

@Leslie.

We missed to add }  in the formula syntax shared above.

If(${Contacts.Mass Update check},Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}),Concat(${Contacts.Preferred Name},' ',${Contacts.Last Name}))

This should work. Let us know if you require any further clarification.

  • 3 years ago

@Pheranda

Thank you Pheranda! That did the trick!

Hi All,

How do i mass update my formula field. My formula field is to covert date/time field to date field.

Datepart(${Quotes.Created Time})

I tried to key-in formula as below but with error.

if(${Quotes.Testing Checkbox},Datepart(${Quotes.Created Time}),Datepart(${Quotes.Created Time}))

Please help, thank

Hello Daniel,

I would suggest you to try the below formula:

If(${Quotes.Testing Checkbox},Datepart(${Quotes.Created Time}),Datepart(${Quotes.Created Time}))

If should not be within quotes. Check and let us know if you have any further clarification.

@Sunderjan formula is working fine. Thank you

Glad to know this worked Daniel. Thank you for keeping us posted.

I did this, and it worked: if(3>2, formula, formula)

Hi,
I have a similar issue

I've created a checkbox field called Mass update in the review module.

The existing formula is:

If(((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30)<0,0,((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30))

How do I incorporate the mass update checkbox in the formula to trigger the update?

Thanks,
Laura

Hello Laura,

I would suggest you to try the below formula:

If(${Quotes.Testing Checkbox},If(((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30)<0,0,((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30))
,If(((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30)<0,0,((Datecomp(${Reviews.Date review completed},${Reviews.Review due date})/60/24)-30)))

Check and let us know if this works.

  • 3 years ago

Hello,

Trying to do the same and struggling.
I have added a checkbox named "Mass Update Formula" and would like these to be included in all formulas so that I can mass update all records.

Current Formula:
${Business Record.Contract Rate (GROSS)}*${Business Record.Total Room Nights}

Hello,

I would suggest you to try the below formula:

If(${Business Record.Mass Update Formula},${Business Record.Contract Rate (GROSS)}*${Business Record.Total Room Nights},${Business Record.Contract Rate (GROSS)}*${Business Record.Total Room Nights})

Let us know if you have any further clarification.

The easiest way that I found was adding Modified Time in the formula like this:

If(Year(${Your Module Name.Modified Time}) == -1,0,

[your original formula here]

)

How would i apply a dummy check box to update this formula?

Dummy check box is called "Mass update check"

Abs(Datecomp(${Candidates.Candidate Updated Time},${Candidates.Phone screen complete}))/24/60

Hello,

You can apply the check box in the formula as suggested below.

If(Tostring(${Candidates.Mass update check})=='true',Abs(Datecomp(${Candidates.Candidate Updated Time},${Candidates.Phone screen complete}))/24/60,0)

Check and let us know if you have any further clarification.

  • 8 months ago

This no longer seems to work for new formula fields

Hello Venessa,

Can you help us with the formula that you are using and your use case so we can check and help you with this.

Looking forward to hearing from you!

Reply to Sam JayA
/* */
  • 12
  • Insert
  • Plain text
Add Comment
(Up to 20 MB )
    Stats
    13 followers
    35 replies
    8557 views
    Follow

    Subscribe to receive notifications from this topic.