Forum Discussion

MelissaFreel1's avatar
MelissaFreel1
Qrew Cadet
5 years ago

Using Relative Dates and App level variables

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
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years 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
    ------------------------------
    • MelissaFreel1's avatar
      MelissaFreel1
      Qrew Cadet
      5 years 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years 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
        ------------------------------
  • NeilSchneider's avatar
    NeilSchneider
    Qrew Cadet
    5 years 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
    ------------------------------