Skip to main content
Question

Using Relative Dates and App level variables

  • May 20, 2020
  • 0 replies
  • 42 views
Forum|alt.badge.img+5
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
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
------------------------------
Mark Shnier. Your Quickbase Coach
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
------------------------------
Terms & ConditionsAccessibility statement