Can a formula ever use different field types?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

I’m trying to put a Remaining Depreciation formula together, but it uses a duration and a currency field.  From what I've seen, using two different field types in a formula does not work. Is there any way around this or will the two different field types never work in a formula? 


Here are the details: I’ve got two tables, Inventory & Inventory History.  Inventories can have many Inventory Histories/

 

The Inventory Histories table contains

Date Sent to Client (a date field)

Date Returned (a date field)

Days in Field (A duration formula)

 

The Inventory table contains:

Total Days in Field (a summary duration field from the Inventory History table)

Cost (a numeric currency)

Depreciation Term (days) (a duration field)

Remaining Deprecation (a numeric formula) THIS IS THE FORMULA FIELD THAT I'M TRYING TO CREATE.  
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
  • apprehensive

Posted 1 year ago

  • 0
  • 1
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
In case it helps.. The Remaining Deprecation would preform this function: 1. Divide the Deprecation Term by the Cost to calculate the a cost per day.  2.Take the cost per day and times it by the Total Days in Field and then subtract it from the Cost.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Re: will the two different field types never work in a formula? 
In fact, two different field types can always work in a formula because there are a whole set of function to convert from one field type to another.

Try this.

var number DepreciationCostPerday = [Cost] / ToDays([Depreciation Term]);

[Cost] - $DepreciationCostPerDay * ToDays([Total Days])
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
Is it possible to prevent the value from going below zero?  Once the Remaining Deprecation value hits $0 it would stop calculating (or something like that) etc.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
no problem,

var number DepreciationCostPerday = [Cost] / ToDays([Depreciation Term]);

Max(0, [Cost] - $DepreciationCostPerDay * ToDays([Total Days]))
Photo of Ringoparr

Ringoparr

  • 782 Points 500 badge 2x thumb
Thank you so much for showing me the way!