Discussions

Expand all | Collapse all

Can a formula ever use different field types?

  • 1.  Can a formula ever use different field types?

    Posted 05-22-2017 22:07

    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.  


  • 2.  RE: Can a formula ever use different field types?

    Posted 05-22-2017 22:46
    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.


  • 3.  RE: Can a formula ever use different field types?

    Posted 05-23-2017 01:27
    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])


  • 4.  RE: Can a formula ever use different field types?

    Posted 05-23-2017 17:17
    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.


  • 5.  RE: Can a formula ever use different field types?

    Posted 05-23-2017 17:23
    no problem,

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

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


  • 6.  RE: Can a formula ever use different field types?

    Posted 05-23-2017 01:31
    Thank you so much for showing me the way!