173229 - no subject - We have a Portal Report that work on ver

You can achieve this by creating an expression rule which takes your 2 inputs, and decides which one to return by utilizing isnull() as you do above - then in your dynamic grid utilize: apply(rule!yourExpressionHere,merge(pv!cdtHighLevelEstimate1.tDeptAssigned,pv!cdtHighLevelEstimateLevel2.tDeptAssigned)).

Your custom expression rule would have 2 inputs, say Estimate1 and Estimate2, and work as:

=if(isnull(ri!Estimate1),ri!Estimate2,ri!Estimate1)

Thanks csteward, let me explain what I am trying to achieve a bit more ...

The expression takes two input CDTs (both multiple)
pv!cdtHighLevelEstimate1
pv!cdtHighLevelEstimate2

Each CDT contain text fields that represent:
Department/Division
Status
Due Date
Started
Completed
Estimate

Uptil now, I was able to obtain the attached table using the following expression:

=if(
and(rule!fdCheckIsNull(pv!cdtHighLevelEstimate1),rule!fdCheckIsNull(pv!cdtHighLevelEstimate2)),"No Data available for Pipeline View.",

rule!fdGetPipelineViewHeader() &
apply(rule!fdGetPipelineViewRow,
merge({"text1"}, {"text2"}, {"text3"}, {"text4"}, {"text5"}, {"text6"})
)
)

The question is: I would like to have the table populated from CDT arrays following the rules below:

1- if(isnull(pv!cdtHighLevelEstimate1.tDeptAssigned),pv!cdtHighLevelEstimateLevel2.tDeptAssigned,pv!cdtHighLevelEstimate1.tDeptAssigned),
2- if(isnull(pv!cdtHighLevelEstimate1.iTaskId),pv!cdtHighLevelEstimateLevel2.tStatus ,pv!cdtHighLevelEstimate1.tStatus),
3- text(todate(if(isnull(pv!cdtHighLevelEstimate1.iTaskId),pv!cdtHighLevelEstimateLevel2.dTaskDueDate,pv!cdtHighLevelEstimate1.dTaskDueDate)),"mmm dd, yyyy"),
4- text(todate(if(isnull(pv!cdtHighLevelEstimate1.iTaskId),pv!cdtHighLevelEstimateLevel2.dTaskStartTime,pv!cdtHighLevelEstimate1.dTaskStartTime)),"mmm dd, yyyy"),
5- text(todate(if(isnull(pv!cdtHighLevelEstimate1.iTaskId),pv!cdtHighLevelEstimateLevel2.dTaskEndTime,pv!cdtHighLevelEstimate1.dTaskEndTime)),"mmm dd, yyyy"),
6- index ( {cons!FD_DD_HIGH_LEVEL_ESTIMATES_DISPLAY , cons!FD_DD_HIGH_LEVEL_ESTIMATES_UPDATED_DISPLAY} , lookup ( {cons!FD_DD_HIGH_LEVEL_ESTIMATES_VALUE , cons!FD_DD_HIGH_LEVEL_ESTIMATES_UPDATED_VALUE}, if ( isnull ( pv!cdtHighLevelEstimate1 .iTaskId ) , pv!cdtHighLevelEstimateLevel2 .tHighLevelEstimate , pv!cdtHighLevelEstimate1 .tHighLevelEstimate ) , - 1 ) , "Not Available" )

Do you think there is a way to use the merge() function for that? Will it loop on the CDT array accessing each field? Is there a right syntax for this?

Thanks

I believe you will be able to use apply() and merge(), you will just need a few helper rules. For example, to test this out with columns 1, 2 and 3, create these helper rules:

rule!getPipelineData1(ri!data1,ri!data2):

=if(isnull(ri!data1),ri!data2,ri!data1)

rule!getPipelineData2(ri!data1,ri!data2,ri!data3):

=if(isnull(ri!data1),ri!data2,ri!data3)

Then the table would be defined as:

=ncolumntable(
          {"text1","text2","text3"},
          apply(rule!getPipelineData1,merge(pv!cdtHighLevelEstimate1.tDeptAssigned,pv!cdtHighLevelEstimateLevel2.tDeptAssigned)),
          apply(rule!getPipelineData2,merge(pv!cdtHighLevelEstimate1.iTaskId,pv!cdtHighLevelEstimateLevel2.tStatus,pv!cdtHighLevelEstimate1.tStatus)),
          apply(fn!text,apply(fn!todate(apply(rule!getPipelineData2,merge(pv!cdtHighLevelEstimate1.iTaskId,pv!cdtHighLevelEstimateLevel2.dTaskDueDate,pv!cdtHighLevelEstimate1.dTaskDueDate))),"mmm dd, yyyy")
)

Try that out, let me know if it works from you - we can expand from there.

Note, I noticed a type in the table expression. Try:

=ncolumntable(
          {"text1","text2","text3"},
          apply(rule!getPipelineData1,merge(pv!cdtHighLevelEstimate1.tDeptAssigned,pv!cdtHighLevelEstimateLevel2.tDeptAssigned)),
          apply(rule!getPipelineData2,merge(pv!cdtHighLevelEstimate1.iTaskId,pv!cdtHighLevelEstimateLevel2.tStatus,pv!cdtHighLevelEstimate1.tStatus)),
          apply(fn!text,apply(fn!todate,apply(rule!getPipelineData2,merge(pv!cdtHighLevelEstimate1.iTaskId,pv!cdtHighLevelEstimateLevel2.dTaskDueDate,pv!cdtHighLevelEstimate1.dTaskDueDate))),"mmm dd, yyyy")
)

We are retrieving 2 tables data from the query rule. for couple of columns the data is empty.
for example: taskDueDate is coming from second table but the table does not have any record. so here we are failing. Please help us.

Is the query rule not retrieving data when table 2 is empty? Have you tried the expressions above? Any additional information you can provide as far as exactly what is happening would he helpful.

Yes the table 2 data is empty. we have tried the above expression. its failing when w try to check the null values which is returning the empty row.