Hello team,
based on a "Report Link" field I have added chart to my record form that prefilters the information shown in the chart below based on the opened record. However, I would also like to limit the displayed x-axis values (calendar weeks) based on the start date that is mentioned in the particularly opened record on top.
Does anyone of you know how this can be realized?
Qrew LegendI can think of a couple of ways to do this, depending on the specifics.
The "Parent" record has a [Start date] field. Can you tell me which weeks you want to show the chart based on the start date? Maybe give some examples?
I would like to display the records from three weeks before and after the currently opened/viewed record.
However, I would say that this is not a parent record; it is simply a single record that is currently open from the table.
Qrew LegendLet's call the "Parent" record the Reference record. Somewhere on that record there is a reference date and I will call the [Reference Date]
Let's call the table which holds the graphs the details table. on that table, I will refer to [Invoice Date]
Go to the reference table and make a new formula date field called Reference Date Sunday.
FirstDayOfWeek([Reference Date])
then go to the details table and make three formula date fields which will calculate the first day of the week of the week before the invoice date and the week of the invoice date and the week after the invoice date.
Invoice Date Week with formula
FirstDayOfWeek([Invoice Date])
Invoice Date Week minus 1
AdjustWeek([Invoice Date Week], -1)
Invoice Date Week plus 1
AdjustWeek([Invoice Date Week], 1)
then on the Reference record table make three report link fields to match Reference Date Sunday with respectively those three fields in the details table.
then put three charts on your reference record
If that is good enough, then we're done. Otherwise, I have another method or two but they're a little more complicated to set up.
@Mark, do you know whether there is any chance to make this formular work in QB ?
Qrew LegendThe other approach which will work if you do not have simultaneous users of the chart at the same time is to create an admin record in a single record table with record id =1.
then you make. Formula URL button to populate the value of the reference date into that record. Then lookup that value down to all details cords. Then have. Formula in details that calculate es to true if the date is with the plus minus 28 day range.
This can be enhanced further using the user focus method to allow for simultaneous users.
so the concept is that the charts are not initially exposed. The user click the button and puts the parent record in focus by writing its record id# and the reference date into that admin record.
Then the record is refreshed during that same URL button click and form rules now display the chart.
Feel free to contact me if you need one on one assistance t9 getting this set up. It will take under an hour of consulting time. Maybe 30 Minutes. mark.shnier@gmail.com
