Grouping on two fields

[BlurredVision]

Hope someone can help me with this.

I have two fields:


ActiveProcess_ID and ArchiveProcess_ID which are located in seperate tables and linked via an outer join to a master/primary table.

I attempted to write the following formula, so that I can group on it, but it seems to ignore the ArchiveProcess_ID field and only displays the ActiveProcess_ID.

Numbervar Process_ID;
if not isnull({RV_ActiveProcess.ActiveProcess_ID}) then
Process_ID := {RV_ActiveProcess.ActiveProcess_ID} else

if not isnull({RV_ActiveProcess.ActiveProcess_ID}) then
Process_ID := {RV_ActiveProcess.ActiveProcess_ID};

Process_ID;

I hope this is enuf information to go on, How can I get around this? I am using v10 of CR, against an SQL DB.

Thank you.

 

[Turkbear]

Hi,
That kind of formula cannot be used for Grouping
( Or anything much: it has 2 tests ( the same test, even!)both of which set Process_ID to {RV_ActiveProcess.ActiveProcess_ID}!)


What grouping do you want ?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BlurredVision]

My bad on the formula...

Numbervar Process_ID;
if not isnull({RV_ActiveProcess.ActiveProcess_ID}) then
Process_ID := {RV_ActiveProcess.ActiveProcess_ID} else

if not isnull({RV_ArchiveProcess.ArchiveProcess_ID}) then
Process_ID := {RV_ActiveProcess.ArchiveProcess_ID};

Process_ID;


What I need is it to check the active id field then the archived id field.

I have the following information in two tables

Active Archived
1851 0
1852 0
0 1921

With that, i would want to group to display

1851
1852
1921

Discounting null values

 

[lbass]

Those aren't null values. And how would you join the tables? It might make better sense to use a union statement in a command to combine the two tables to use as your datasource, as in:

select 'Active' as type, RV_ActiveProcess.`ActiveProcess_ID` as ProcessID, RV_ActiveProcess.`otherfield1`, RV_ActiveProcess.`otherfield2`
from RV_ActiveProcess
union all
select 'Archive' as type, RV_ArchiveProcess.`ArchiveProcess_ID` as ProcessID, RV_ArchiveProcess.`otherfield1`, RV_ArchiveProcess.`otherfield2`
from RV_ArchiveProcess

Then {command.ProcessID} will hold both active and archived dates and you can group on it. If you need to distinguish active from archived records, you can use the type field to distinguish.

-LB