Help

Automate tick box and Your result type is not a number or a date.

Topic Labels: Automations Formulas
1269 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Imee_Lee
6 - Interface Innovator
6 - Interface Innovator

Hello,

So currently, we do have a tick box on this view and manually tick it once it is the latest valuation.

Screenshot 2023-06-01 at 12.45.00 PM.png

Do you have any ideas on how can I automate this? without doing the manual ticking?

Another issue, I created this formula :

 

IF({latestVal Date} = {Val Date},{Valuation Amount}," ")
 
But it's giving me this output
Screenshot 2023-06-01 at 12.47.30 PM.png
I know that this could be done when you format it through currency but I'm having this error 
 
"Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date."
 
So what I've done is update the formula to 
 
IF({latestVal Date} = {Val Date},{Valuation Amount},0)
 
and it's giving me this result 
 
Screenshot 2023-06-01 at 12.49.51 PM.png
 
The main goal of this formula is to not include those records that are blank or zeros. Is there any missing from the formula that I have set up?
 
Thanks
5 Replies 5
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hi Aimee! 

re: Do you have any ideas on how can I automate this? without doing the manual ticking?

To do this, check out this example base.  The idea is to create another table, link the relevant records to it (thereby grouping them together as needed), use `MAX(values)` to find the latest date, and then use a lookup and a formula field to bring it back to your original table and to find the record with the latest date

---
re: The main goal of this formula is to not include those records that are blank or zeros. Is there any missing from the formula that I have set up?

If you'd like the field to not show anything at all when it's not the latestValDate, try changing your formula to:

 

 

IF({latestVal Date} = {Val Date},{Valuation Amount})

 

 

Screenshot 2023-06-01 at 1.13.28 PM.png

Thank you Adam,

It worked! There's only 1 thing remaining.

There are 2 same latest valuation dates, which means it will get the amounts of these dates.

Imee_Lee_0-1685597157139.png

 


Is there any way just to get the higher amount?

Here's my current setup:

Imee_Lee_1-1685597157167.png

 

Is there any conditions that I need to add on this part?

Imee_Lee_2-1685597157168.png

 

 

Hmm, instead of a lookup, could you try using a rollup with `MAX(values)`?  That should give you the highest value

Imee_Lee
6 - Interface Innovator
6 - Interface Innovator

Here's the catch on the bank vals tab = The formula you shared earlier worked it won't add the 0 amounts anymore.

When I do the roll-up on the properties it is giving me the highest amount when I do the`MAX(values)`.(which I need)

But - on this roll up the $0 is showing again, is there anything that I missed on this part?

 

Screenshot 2023-06-01 at 1.40.10 PM.png

 

Here's the current roll up:
Screenshot 2023-06-01 at 1.41.41 PM.png

Could I get a screenshot of a view that shows the "latestVal Amount copy" field with all of the records linked to this record that's showing the $0 in the rollup please?