Discussions

Expand all | Collapse all

Using Relative Dates and App level variables

  • 1.  Using Relative Dates and App level variables

    Posted 10 days ago
    Hi all:
    I have what I think is a simple problem and I probably have even solved it in the past but would like some help advancing my skills.

    I have field that counts records when a particular date falls within or during the fiscal year.  This is how it is written right now

    If([Date1]>=ToDate("10/01/2019) and [Date1]<=ToDate("09/30/2020), [CountField2],0)

    I created an app level variable of CurrentFiscalYear=10/01/2019-09/30/2020  but I am really clueless on how to use it.
    I also saw both the XML and URL examples in the API Guide but I really get clueless on which of these I should embed in my  field as well as what "type" of field my field should be to use the XML. Formula Numeric??  

    Any help is appreciated.

    Thanks.

    ------------------------------
    Melissa Freel
    ------------------------------


  • 2.  RE: Using Relative Dates and App level variables

    Posted 10 days ago
    I personally dislike Application Variables as they are too obscure and no one but the Admin can see them or change them.  So IO will suggest a way I would suggest.

    Make a new table called Fiscal Year Dates. 
    Make two date fields here for [Fiscal Year Start] and [Fiscal Year End]. 
    Create 1 record and then lock down permissions so no one, even the Admin can Add or Delete. Decide which Roles are allowed to Modify.

    The record will be Record ID# = 1.

    Make a relationships to your detail record so 1 Fiscal Year Dates has many Details. Let it create a relationships field for Related Fiscal Year Date, but rename it after creating the relationship to be called [Link to Fiscal Year Dates (=1)].  Then change the field type to be a formula Numeric and the formula will be 

    1

    That will connect to Record ID# = 1 in the dates table.

    Lookup those two date fields and use them in your formula.




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Using Relative Dates and App level variables

    Posted 6 days ago
    mark;
    Thanks, yes, I agree that app level variables are nebulous and not easy to use.   I am assuming that I could change record 1 to a different fiscal year when necessary based on what you described above?

    ------------------------------
    Melissa Freel
    ------------------------------



  • 4.  RE: Using Relative Dates and App level variables

    Posted 6 days ago
    Yes, that is the whole point of exposing it on record in a table. That way you can give everyone visibility but control who has edit ability.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Using Relative Dates and App level variables

    Posted 10 days ago
    Melissa,
    I believe global variables are always text fields.  You would include your variable in a formula using the bracket notation [CurrentFiscalYear]
    So the formula would have
    var text fiscalYear = [CurrentFiscalYear];
    var date start = ToDate(Part($fiscalYear,1,"-"));

    ------------------------------
    Neil Schneider
    ------------------------------