Seems that Report B has all the required info, then Report A is not needed ??
Put parameter "Transaction Date" with type date.
Create Calculated field of
if [Date]>=([Transactioni Date]-7) and [Date]<=([Transactioni Date]+7) then "show" else "hide" END
(And Filter by "show")
Put [Product ID]'s Quick Filter.
Using Parameter "Transaction Date" and Quick Filter of "Product ID" can bring expected view.
transaction_date.twbx 19.5 KB
Sorry for not being clearer. I am using it for other tracking but try to use Sales to illustrate which create more confusion.
Here is what is needed :
Report A is the product table with transaction dates.
When we select on this record, Transaction date will be send to Report B.
In Report B, which is about the equipment history used to make the part.
Basing on the Transaction date it will list all the +/-7 days equipment history of the transaction date to see if there were some abnormality happened in the equipment before or after to denote if the product could be affected by this equipment.
e.g. This product is returned. It could be due to the poor Equipment Maintenance done a day before. By listing +/- 7 days of equipment history, we are able to trace if abnormality happened that can be a cause of the failure.
We can also use similar actions to check if products list that the product was produced by the same machine within +/-7 days
Thanks and REgards
Little bit struggled to think out, and still not sure this meets the requirement, anyways here is something.
The difficulty is we cannot have Linked field between table 1 and table 2. (That should be one to multiple)
Then I combine Prod ID and Trans Date as calc field, then feed them into table 2 using parameter.
Finally, decompose parameter in table 2.
Parameter display is not excellent, but still enough to distinguish Prod name/ID.
==== Solution from here ====
We have two data table set.
1. Trans_date 2. Event date
Create Calculated field in Table1
[Prod ID , Date]
[Product ID]+"_"+str([Trans Date]) << *** You can change "_" to whatever which is not used as product name/ID>>
Create Parameter using list in Table 1 - [Prod ID , Date] (parameter update is manual task each time)
Show parameter Control
In Table 2 as primary data,
Create calculated field
date(mid([Parameter 1],find([Parameter 1],"_")+1,10))
if [Date]>=[Transaction Date]-7 and [Date]<=[Transaction Date]+7 then "IN" else "OUT" END
Filter by [Date_Range]="IN"
Left([Parameter 1],find([Parameter 1],"_")-1)
if [Transaction Date]>[Date] then "Before"
elseif [Transaction Date]=[Date] then "Exact"
elseif [Transaction Date]<[Date] then "After" end
==> Now you can select Product Name from parameter, then table picks up event last 7 days, exact day, next 7 days.
Attached workbook is created by version 9.0
product_date.twbx 53.3 KB
I'm interested that I could help your request or not.
When you have a chance, could you take a look, and let me know if you have any questions.