Display records by specific date columns
I am currently trying to set a field to display a value from a record of a specific date. For example when I have a scenario like such :-
Project Date Fruit Count
Project A 01/Feb/2015 Apple 5
Project A 02/Feb/2015 Banana 10
Project A 03/Feb/2015 Apple 2
Project A 03/Feb/2015 Banana 5
I am trying to be able to build a summary report where I can display as such :-
Sub-Category = Project A
<Fruit> <Day 1> <Day 2> <Day 3>
Apple 5 2
Banana 10 5
Treating this like a stock-take system, I enter in items and the count on a daily basis. Hence here I have 2 main tables one to capture the daily_id ( containing the dates) and another table item_id (containing fruit_type and count). At the end of the month, I want to be able to see the count of my items day by day.
For a start, I am creating fields to store the counts for each day (Ex: Field;DAY1 = count from 01/Feb/2015). Which means I will be creating 31 new fields. I then proceed to creating a script to help me find the records for each day and store the counts into my (DAY*)fields.
So far this is what I have.
Show All Records
Go to Record/Request/Page [First]
If[site_diary::date = Date(site_diary::prevMONTH; 1; site_diary::prevYEAR)]
Go to Record/Request/Page[Next;Exist after last]
*prevMONTH - is to help me identify the previous month.
*prevYEAR - is to help me identify the year of the previous month.
Hope someone can shed some light on this.