AnsweredAssumed Answered

Display records by specific date columns

Question asked by Salem on Feb 17, 2015
Latest reply on Feb 18, 2015 by philmodjunk


Display records by specific date columns


Hi All,

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)]

                 SetField[site_diary::day1; site_diary_manpower::count]
            End If
       Go to Record/Request/Page[Next;Exist after last]

End Loop.


*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.