I've tried a number of different attempts to get this but to no avail. Here's a summary:
we have a project which can have 0,1 or many related records(call them reports)
I would like to show in one table all the customers and whether they have 0 / 1 / many reports (plus some basic fields to go with it.)
We can then add a simple list box to allow the user to filter on those projects with reports and those that don't.
The approach as I see it is:
1) build a small, single field table with the project id's
2) do a left-outer-join to the reports table - this will list all projects and whether they have a report or not
Now I managed to get this to work. But I then want to reload all the data again and check the 'report.date' field. If it contains a value, set a Boolean - Y, else N. Then I want to use this flag to filter the record. But it's this last bit that doesn't want to run without an 'cannot recognize field' error.
I have both tables loaded already (from *.qvd files.)
I then do:
reporttemp:
load
projectid
resident projects;
left outer join(reporttemp)
load *
resident reports;
<it works ok up to here>
I then try:
finaltable:
load *,
if(reportdate <> null, 'Y','N')
resident reporttemp;
<but it doesn't recognize the 'reportdate' field, or any field from the reporttemp table.> If I look at the list of available project fields from a sheet object, the table name has an unexpected prefix (reporttemp-1.) or something like this.
Has anyone tried to build something similar and have an alternative approach, or know what I'm doing wrong here?
