I’m trying to generate weekly status reports from my project management app: it works, but I had to hard-code the date range for each week into a distinct view: so I have 52 views (for 2022), each of which hard-coded with a begin+end date range. I would much rather have a single view, with a filter that allows the user to select the date range and updates dynamically in response.
I thought I might be able to do this with an automation button, but there seems no obvious way to use the button to modify the filter. I did consider creating a one-row database which would hold the desired data range, and then use an action button with ‘update’ action to update that row, but then I couldn’t see how to use that value in the filter.
I also couldn’t see any way to provide a pre-determined list of date ranges for the user to select: I could create a 52-row database with the date ranges hard-coded, so the user could specify “week 1”, “week 2”, etc. (this would be ideal), but I can’t see any way to use that dataset in an action button: I seem to need the user to select a date range explicitly each time.
I do (now) notice that I can embed javascript (calling the fibery API) in an action button, so I’m guessing that if this IS possible, then scripting it will probably be the mechanism, but I can’t figure out how to do it: the flow I would like to provide is:
ask the user to select from a list (“week 1” … “week 52”) pulled from my “Weeks” dataset (which defines the 52 weeks’ date ranges)
use that selection to look up the date range from Weeks
update the filter in the “weekly status” view to use the selected date range.
(Of course I’d be even happier to generate the user selection menu (“week 1” … “week 52”) programmatically, and then map the user’s selection to a date range programmatically, rather than using the “Weeks” dataset, but it’s no great hardship to create the “Weeks” dataset if that’s the only way.)
Ideally this would all be done (only) via the “weekly status” view, so that it would become a dynamically updating view controlled by the button. (To replace my current 52 different weekly status views).
Not sure I fully get what you’re trying to achieve, but it sounds like you need a formula field for week number.
And then you can use this field as a filter in any views.
It would be possible to use an algorithm like the one described here to get the week number:
Thanks! Yes, I can figure out how to map a week number to a date range, if necessary (ie. if I don’t/can’t use a lookup table encoded as a Database), but I can’t figure out how to prompt the user for a week number, then use the resulting date range in the filter automatically: is there an example I could follow? ie. when I look at the date filter UI I can only see explicit date selectors: I don’t know how to reference a variable from my script (or even if I can).
I have implemented a “last week” status report, and that works great: but I also need to be able to generate a report for a specific date range, even if it’s in the past, hence this thread.
Reading your answer more carefully: I see you’re recommending a formula field: that sounds promising: but it would need to be a field of a Database, I assume: would I just create a new single-row Database, specifically to host that field? And what would be the input to that formula? I guess an action button? Sorry: I’m still rather confused about the details! What I want to achieve is:
user is prompted for a week number (1-52)
the week number is converted to a date range (eg. 1 → [2021/12/31,2022/1/7]).
the date range is provided to the (pre-existing) date filter in my weekly status view (currently that’s a table view).
the user is presented with a table of tasks, correctly filtered by the provided date range.
I can probably figure out how to create the mapping from week number to date range (#2 above) (ie. I could certainly do it in javascript, presumably it won’t be hard to convert that to a formula?). But the rest of the sequence is beyond me so far.
Ideally the prompt (action button?) for the week number would be part of the existing weekly status (table) view, and so as long as the user is interacting with that view, he/she could repeatedly re-enter week numbers and the view would dynamically update to show the correctly filtered tasks.
… so I experimented a bit and I was able to add an action button that prompts the user for a date range (I guess I could prompt for a week number and then add a formula to convert it to a date range, but I’ll worry about that later); however, my conceptual difficulty is: I want my weekly status to be based on my “Tasks” Database (in the fibery meaning of “Database”), which means I have to use a “Table” view of “Tasks” to generate the report: but the action button only appear if I select one or rows of the table view of the “Database” that it’s associated with, and it updates the selected row(s). Whereas I need a button that accepts a date range which can be applied to all rows of the Tasks Database (even if none are currently visible due to the current filter). I’m still lost as to how to achieve this. It seems like this should be doable per @Chr1sG’s comments but I just don’t see how. Any help gratefully received!
Just to understand a bit better, can you clarify exactly what you are trying to achieve (forget about buttons, formulas etc).
My guess is that you have a database of Tasks, with a date field (I assume a single date, not start and end dates?)
You want a view in which the user can see a list of all tasks whose date falls within a specific week of the year, right?
And you want the user to be able to choose the week of the year to be used in this filtered view. Is that all correct so far?
Hi: yes, that’s exactly correct: the Task database has a [Done Date], and also a [Week End Date] (which is derived from [Done Date] as the immediately pending Friday). I want a view where the user can select any Friday, and see all the Tasks with a [Done Date] within the 7 days leading up to that Friday (or ie. where Task.[Week End Date] == the selected date). I currently have 52 of those views, each with a hard-coded date filter to pick up the appropriate Friday. But I’d much prefer to have a single view, with a button/input field where the user can select the particular Friday and the view would dynamically update.
I do have something working that’s about halfway: I created a Week Database, with 52 rows (one for each Friday in 2022), and a relation to Task on [Week End Date], and a smart folder with level = Week; that effectively gives me 52 views automatically that I can select, and each one shows me the list of Tasks. But what I really need is a table view, because that’s the only view I can export in a useful manner. (The main reason for generating these weekly status reports is so that I can export them and email them to our CFO).
Yes, you’re right: actually that’s why I created [Week End Date] - to make updating the filter as painless as possible - but it still requires knowledge of the calendar: one has to know which date to enter into the date field.
I guess I should have added: I want to share this as a template and encourage my colleagues to use it (and hence possibly get the company to adopt it): not all of them will find it intuitive to go through the workflow of modifying the filter each time and looking up dates in their calendar to do so: I was hoping to be able to create a simply UI with a “Week 1… Week 2… Week 52” selector, which would automatically update the filter.
BTW I have a spin-off question, which I’ll throw in here in case it’s an easy yes/no: I was hoping to be able to create [Week End Date] (the date of the next Friday) from [Done Date] like this:
where getDay() would be the javascript function that maps Sunday → 0 and Saturday → 6, etc. and % is the javascript modulo operator, but getDay() doesn’t exist and nor does a modulo operator; so in the end I did it via brute force:
Might there be a way to use the formula after all? I could only see a way to include javascript in an action button, not in a formula.
BTW even with these niggling issues I’m blown away by the power of fibery: it’s already vastly exceeded my expectations for how much of what I want to do is possible out of the box (I just wish it didn’t use “Database” to refer to what I think of as a table: I have to keep twisting my mind to adapt to the terminology!).
You’re right that getDay() doesn’t exist yet, but I think it will soon
Also, for the modulo operator, the best you can do is a workaround making use of the roundDown function.
