Good evening/Morning

I have attached an image to hopefully help to describe the issue.

I am trying to create an aggregate that will return a list of types and a virtual field to define if it is active.

The problem I am having is that the table only stores entries that have been selected, so I need to do a join by comparing a table twice


The agreement list is a 1 to 1 with a productId, The agreement ID will then store any types that have been selected in the AgreementDataShare table The lookup table links the Agreement Table with the Datatype.


I have created a Field in the aggregate as 'IsActive' if a datatype ID is found in the Lookup table it is set to true.


What I need is if the ID is NOT found to include the row but set as Inactive.


I have lost about 2 inches of my hairline from my fringe over this!




Jim Crawford wrote:

Good evening/Morning

I have attached an image to hopefully help to describe the issue.

I am trying to create an aggregate that will return a list of types and a virtual field to define if it is active.

The problem I am having is that the table only stores entries that have been selected, so I need to do a join by comparing a table twice


The agreement list is a 1 to 1 with a productId, The agreement ID will then store any types that have been selected in the AgreementDataShare table The lookup table links the Agreement Table with the Datatype.


I have created a Field in the aggregate as 'IsActive' if a datatype ID is found in the Lookup table it is set to true.


What I need is if the ID is NOT found to include the row but set as Inactive.


I have lost about 2 inches of my hairline from my fringe over this!




Hi Jim, 


Why don't you use an AdvQuery to do that, instead of an aggregate?

You can perform that with a LEFT JOIN and a CASE statement in your SELECT.


Kind regards,

Hugo


Hello Jim, if I understand correctly you want to assign a value to a virtual field you create on the Aggregate when you don't have an Id.

So what I suggest is, create a With or Without join between the tables you mentioned and add a new Attribute where you check if the Id of the joined table exists or not. The boolean value will be assign according to the existence of the Id you are checking.

On the following example I check if the Author.Id exists, if Author.Id = Nullidentifier() my new attribute will be IsInactive = True.

I hope it helps.


Best regards,

Sumeiya