Forum Discussion
OK, so now we have a real question.
I suggest that you use a summary field to count the # of records (I will call them Orders) since the beginning of the April 1st fiscal year. We will create a new table with exactly one and only 1 record in it to hold the counter. It will have a summary field which will hold the counter.
There are a few steps.
1. Set up a new table to hold the counter, called say Order Counter. Create 1 record in it and then adjust the permissions for all the Roles so no one including the admin can add any new records. It will be [record id#] of 1.
2. In the detail Orders table create a formula numeric field with a formula of 1. call it say [Link to Order Counter Summary] it will calculate to the numeric value 1.
3. Build a relationship where 1 Order Counter summary record is related to all Order Records based n that numeric field.
4. Now we weed a formula to calculate the start of the fiscal year. Lets build this in the Orders table. Call it [Fiscal Year Start]. It will be a formula date field type
5. i have not tested this but the formula would be something like this
// determine the YYYY for the current fiscal year.
var number YYYY = if(month(today())>=4, Year(today()),Year(today())-1)
// the DATE function below will be April 1st of the current fiscal year.
Date($YYYY,4,1)
6. Now go into the relationship and make a summary field by clicking on the button on the left side of the relationship. Make it count the number of Orders, but put in a condition that the [order date] is greater than or equal to the value in the field [Fiscal Year Start].
Feel free to post back if you get stuck or my formula has any syntax errors that you cannot fix. Note that as soon as you build setup above the "counter" will immediately count for all existing records, so it will retroactively give you your summary counts for the current fiscal year.
I suggest that you use a summary field to count the # of records (I will call them Orders) since the beginning of the April 1st fiscal year. We will create a new table with exactly one and only 1 record in it to hold the counter. It will have a summary field which will hold the counter.
There are a few steps.
1. Set up a new table to hold the counter, called say Order Counter. Create 1 record in it and then adjust the permissions for all the Roles so no one including the admin can add any new records. It will be [record id#] of 1.
2. In the detail Orders table create a formula numeric field with a formula of 1. call it say [Link to Order Counter Summary] it will calculate to the numeric value 1.
3. Build a relationship where 1 Order Counter summary record is related to all Order Records based n that numeric field.
4. Now we weed a formula to calculate the start of the fiscal year. Lets build this in the Orders table. Call it [Fiscal Year Start]. It will be a formula date field type
5. i have not tested this but the formula would be something like this
// determine the YYYY for the current fiscal year.
var number YYYY = if(month(today())>=4, Year(today()),Year(today())-1)
// the DATE function below will be April 1st of the current fiscal year.
Date($YYYY,4,1)
6. Now go into the relationship and make a summary field by clicking on the button on the left side of the relationship. Make it count the number of Orders, but put in a condition that the [order date] is greater than or equal to the value in the field [Fiscal Year Start].
Feel free to post back if you get stuck or my formula has any syntax errors that you cannot fix. Note that as soon as you build setup above the "counter" will immediately count for all existing records, so it will retroactively give you your summary counts for the current fiscal year.
Mark, I know this is an old post, but I'm looking to do something similiar, and in a test app I tried your setup described (brilliant by the way!). But the summary field always holds the current total of records, so all records get the same counter value (the current record total). Does your solution address that and I misunderstood?
As a fix, I thought I'd use a snapshot to grab the counter at a point in time. But for some reason, my lookup field doesn't have the snapshot option shown. Thoughts? Other thought I had was to use a webhook or an automation in some fashion (haven't thought that through yet).
As a fix, I thought I'd use a snapshot to grab the counter at a point in time. But for some reason, my lookup field doesn't have the snapshot option shown. Thoughts? Other thought I had was to use a webhook or an automation in some fashion (haven't thought that through yet).