Forum Discussion

RyanParr's avatar
RyanParr
Qrew Assistant Captain
8 years ago

Can a formula ever use different field types?

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.  
  • RyanParr's avatar
    RyanParr
    Qrew Assistant Captain
    8 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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])
    • RyanParr's avatar
      RyanParr
      Qrew Assistant Captain
      8 years ago
      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.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      no problem,

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

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