I have two entities (A & B) that are in a many-to-many relationship. I have one attribute in A that is also shared with B - meaning if I create a new attribute entry in A, it will also appear in a dropdown in B on the "BDetail" screen. There can be multiple entries in B of that same attribute entry from A, but there can only be one in A. Example: We create a new entry "E1" in A. For that specific entry E1 we can create as many entries in B as we want.

Now when I am on the A's screen I want to show (in a separate column, could be with a checkmark, doesn't matter) if any of the entries in B corresponding to this entry in A has a certain value in a certain attribute ("Attr" below).

I put an if statement inside a column and used

GetB(GetAs.List.Current.A.BId).B.Attr

as the condition. but that leads to an internal error.

Hi Osacc,

I don't completely understand your question yet but I can help you why the GetB is giving you an error.

The GetEntity Entity Action whill throw errors when it can't find the identifier u used as an Input Parameter. In your case where you don’t completely know if your value B exists using the GetEntity action isn't a good idea. Rather I think you should do this check in a Screen Action.

Kind regards,
Martijn Habraken

Martijn Habraken wrote:

Hi Osacc,

I don't completely understand your question yet but I can help you why the GetB is giving you an error.

The GetEntity Entity Action whill throw errors when it can't find the identifier u used as an Input Parameter. In your case where you don’t completely know if your value B exists using the GetEntity action isn't a good idea. Rather I think you should do this check in a Screen Action.

Kind regards,
Martijn Habraken

Hi Martijn,

So to check if an identifier exists I wrapped the if in another if-statement with the following condition:

not(GetAs.List.Current.A.BId = NullIdentifier())

This got rid of the internal error. However, now I get nothing in that column, because all the top level if-statements (from above snippet in this comment) return false. That should not be the case, because B does have entries with same attribute identifiers as A.

Also, what do you not understand in my question? I realize I could've been a lot better with my wording, but this is a pretty complex problem (for me, at least) and it wasn't the easiest to come up with a formulation.

Edit: I've added B as a source in A's Preparation and used

not(ATable.List.Current.B.Attr = NullIdentifier())

in the outer if and

ATable.List.Current.B.Attr=2

in the inner if, but that yields the same result.

Edit 2: I guess to put it simply, I want to see in entity A values from entity B correponding to the current identifier from entity A. Then I want to determine if any of those values are equal to some value (2 in the example above).

Hi Osacc,

Could you provide a snippet of your example data model to let us help you better.

So, you will have a few options:

  1. Gather the data in the aggregate/SQL that is the source for the Table Records. This is probably the most performing option because all calculations are done inside the database.
  2. Use expressions when rendering the page that aren't using any new aggregates/SQL's te determine the result. I have the feeling you are trying this right now.
  3. Use expressions when rendering the page that for each row of A check in the database if the record in B is present. This isn't that performing because you will have more calls to the database.

Kind regards,
Martijn Habraken

What do you mean when you say example of data model? How could I go about getting it?

I think you're right in that I'm doing 2) (if I'm understanding you correctly). But I need to do 3) (or 1)) instead, because I need to traverse an array of values in B corresponding to a single value in A. I don't know how to traverse it in OS. Frankly I'm not even sure what to traverse.

Solution

Hi osacc,

Maybe you need something like this?

You basically do a LEFT join (With or Without) between A and B where the JOIN condition is: 

A.Id = B.AId and B.Attribute1 = 2

This will join only the rows where there is a match between A and B and where the attribute in B is the one you're looking for.

Then you group by the attributes you want from entity A and after groupping, you add a Count of the B Id (identifier) attribute). As the Count does not count NULL values, if there aren't any matches in B, you will have 0 here, any other value will mean that there are matches...

Is this what you need?

Cheers

Solution

You can create Entity Diagrams. I just wanted to make sure that we are talking about the same datamodel.

Is this what you have in mind?

Martijn Habraken wrote:

You can create Entity Diagrams. I just wanted to make sure that we are talking about the same datamodel.

Is this what you have in mind?

Mine is a many-to-many. So there's two lines (one more that goes the opposite way).

If you don't want to group by and want to keep multiple matchings between A and B regardless of B having or not the value you're looking for:

Do a LEFT join (With or Without) between A and B and add a new calculated field in B with the following expression:

B.Attribute1 = 2

You will have a list of A REPEATED for every match with B and a new column that has TRUE if the B attribute is equal to the value you are looking for, and FALSE otherwise.

Cheers.

Eduardo Jauch wrote:

Hi osacc,

Maybe you need something like this?

You basically do a LEFT join (With or Without) between A and B where the JOIN condition is: 

A.Id = B.AId and B.Attribute1 = 2

This will join only the rows where there is a match between A and B and where the attribute in B is the one you're looking for.

Then you group by the attributes you want from entity A and after groupping, you add a Count of the B Id (identifier) attribute). As the Count does not count NULL values, if there aren't any matches in B, you will have 0 here, any other value will mean that there are matches...

Is this what you need?

Cheers

Eduardo,

Yup that's almost what mine looks like. I did not have this

 and B.Attribute1 = 2

part, so I've added it. Regarding the Count part, after grouping by attributes in A I got a whole bunch of errors like this

Can't identify 'A' element in expression.

and this

Can't identify 'B' element in expression.

inside the table for every attribute I grouped by.

Also I cannot count the BId because I don't have a BId column on the list (only the B attributes).

Hi, 

The errors happens because before, you had Entity.Attribute, but now, you have only Attribute. Just fix the expressions. You will see that the grouped attributes will be accessed without referring the entity name, like YourTableRecords.List.Current.Attribute.

Regardung the Count of the B.Id, if your entity does not have an I'd (primary key), count any other field, as it should work as well. If B is an external entity imported with Integration Studio, chose a mandatory field. 

Cheers 

P. S if you group by B attributes, you may have repetitions (for A). 

Eduardo Jauch wrote:

P. S if you group by B attributes, you may have repetitions (for A). 

I tried the second approach and it kinda worked, but yes I did get repetitions for A.

Will try and fix the first attempt and report back.

Hi osacc,


Just for your information, if you do have a B id, but it doesn't show in your aggregate columns, that might be just hidden.  Can be recognised by a small number above the list, click it to view the hidden columns.

Dorine

Hi osacc. 

Do you know how to work with group by in OutSystems? 

Imagine that you have an aggregate with two entities A and B. You are showing records in a Table records. You want to show only attributes of A, so you have, example, two columns in the TableRecords with the following expressions in them:

TableName.List.Current.A.Attribute1

And

TableName.List.Current.A.Attribute2

But them you get repetitions, because they have a 1 to many relationship. So, you group by Attribute and 2 of A, and get the errors you mentioned, because once you group attributes, only them are available to be used. So you just fix your Expressions to:

TableName.List.Current.Attribute1

And

TableName.List.Current.Attribute2

Without the name of the entity. 

Cheers

Eduardo Jauch wrote:

Hi osacc. 

Do you know how to work with group by in OutSystems? 

Imagine that you have an aggregate with two entities A and B. You are showing records in a Table records. You want to show only attributes of A, so you have, example, two columns in the TableRecords with the following expressions in them:

TableName.List.Current.A.Attribute1

And

TableName.List.Current.A.Attribute2

But them you get repetitions, because they have a 1 to many relationship. So, you group by Attribute and 2 of A, and get the errors you mentioned, because once you group attributes, only them are available to be used. So you just fix your Expressions to:

TableName.List.Current.Attribute1

And

TableName.List.Current.Attribute2

Without the name of the entity. 

Cheers

It worked! Thank you so much! One more question: so I've found entries with values not equal to 2. Now I also want to include entries with no values (empty). I've tried something like this:

A.Id = B.AId and (not (B.Attr = 2) or (B.Attr = NullIdentifier()))

but that still does not return the empty sets.

To avoid the xy-problem: I want to make sure that there's at least one corresponding value in B for each value in A in a certain attribute and all values are equal to 2. If either of the two conditions aren't satisfied I'll put a mark in the A's table.

Sorry, you want lines with at least one B value equal to 2, or all B values must be different from 2?

I am confused. Sorry. 

They must be different from 2 OR they must not exist. In that case count them in.

If that condition is satisfied I'll put an X in the A table.


One other way to put it: I want to check if all the B values are equal to 2, and at least one B value exists for every A value. If that condition is satisfied I'll put a checkmark.in the A table.


I think the result would be equivalent in either case, as I'll just put a checkmark or an X respectively for the rest of the values (please do correct me if I'm wrong).

Sorry, but the first affirmation is not the same thing as the second. 

That's why I am confused. 

It is necessary to make some assumptions, like that you want all A, no matter if B matches, and wanna know if at least one B matches. 

For this case is like this in the left Join condition:

A.Id = B.AId and ((B.Attr <> 2) or (B.Attr = NullIdentifier()))

All A will enter, only B that satisfy the Join conditions will enter. Count B. I'd or other attribute from B and group by attributes from A. A count = 0 for one A means no B matches the conditions. 

If I finally understood... 


Eduardo Jauch wrote:

Sorry, but the first affirmation is not the same thing as the second. 

That's why I am confused. 

It is necessary to make some assumptions, like that you want all A, no matter if B matches, and wanna know if at least one B matches. 

For this case is like this in the left Join condition:

A.Id = B.AId and ((B.Attr <> 2) or (B.Attr = NullIdentifier()))

All A will enter, only B that satisfy the Join conditions will enter. Count B. I'd or other attribute from B and group by attributes from A. A count = 0 for one A means no B matches the conditions. 

If I finally understood... 


Sorry yeah they're not the same.. But the result should be the same regardless of which one I use, right?

Also, that's the join condition I'm using, and the A entries that don't have any corresponding B entries still don't get counted in. I want something like (array.length == 0) and was under the assumption that (B.Attr = NullIdentifier()) does pretty much that. Is that not the case?

Sorry, 

I probably was sleeping. 

To get the first: 

A.Id = B.AId and (B.Attr <> 2)

This is enough if the left table is A and the right table is B and you perform a With or Without join (left Join). 

All records of A will appear regardless of having a match with B where the attr is different of 2, but B information will appear only for A records where both conditions are true. 

Than you group by A attributes you want to show, as this removes repeated A records when there are multiple Marches with B with Attr <> 2, and count B. Id (Identifier) preferably, or other attribute, to know how many Bs with attr <> 2 there are for each A. As count do not count Null values, 0 means no match, while 1 or more means matching. 

Cheers 

Eduardo Jauch wrote:

Sorry, 

I probably was sleeping. 

To get the first: 

A.Id = B.AId and (B.Attr <> 2)

This is enough if the left table is A and the right table is B and you perform a With or Without join (left Join). 

All records of A will appear regardless of having a match with B where the attr is different of 2, but B information will appear only for A records where both conditions are true. 

Than you group by A attributes you want to show, as this removes repeated A records when there are multiple Marches with B with Attr <> 2, and count B. Id (Identifier) preferably, or other attribute, to know how many Bs with attr <> 2 there are for each A. As count do not count Null values, 0 means no match, while 1 or more means matching. 

Cheers 

It doesn't work with that condition. The values in A that don't have any corresponding values in B still do not get counted. So I don't get an X, when I should.


Or rather, both the ones that do have values but that are all equal to 2 and the ones that don't have any value are shown on the list as 0.

I need a way to distinguish between the two. Specifically I don't want the former to be shown. So I either want the latter to be counter as >0 or the former to not be on the list at all.

I hope this makes sense, if not, please do tell me, I'll try to paraphrase.

Just to be sure, correct me if I understood wrongly :

1. You want to show all records from A. 

2. You want to have a mark (an X) on lines of A that do not have any entry in B, or that have only entries whose B.Attr is Not 2.

Is that what you want? 

É. G. 

A TABLE

1 x

2 v

3 c

4 d

B TABLE (id , AId, Attr

1 1 1

2 1 3

3 2 1

4 2 2

5 3 3

The result should be

A Id, X

1 X

2

3 X

4 X

Is that? 

Eduardo Jauch wrote:

Just to be sure, correct me if I understood wrongly :

1. You want to show all records from A. 

2. You want to have a mark (an X) on lines of A that do not have any entry in B, or that have only entries whose B.Attr is Not 2.

Is that what you want? 

É. G. 

A TABLE

1 x

2 v

3 c

4 d

B TABLE (id , AId, Attr

1 1 1

2 1 3

3 2 1

4 2 2

5 3 3

The result should be

A Id, X

1 X

2

3 X

4 X

Is that? 

Well for your example the result should be

1 X

2 X

3 X

4 X

Here's another example:

A TABLE

1 x

2 v

3 c

4 d

B TABLE (id , AId, Attr

1 1 1

2 1 3

3 2 2

4 2 2

5 3 2

The result should be

A Id, X

1 X

2

3

4 X

I'll then show a checkmark for all the other ones, in this case 2 and 3 (i.e. the ones that have at least one entry and are all equal to 2).


So, for both examples, the rules are:

a) Mark the lines in A that do not have any entry in B

b) Mark the lines in A that does have entries in B, but that have at least one entry where B.Attr is NOT 2.

In the end, you want to separate the entries in A that only have matches in B with attr = 2, from all the others.

Is that correct now?

Eduardo Jauch wrote:

So, for both examples, the rules are:

a) Mark the lines in A that do not have any entry in B

b) Mark the lines in A that does have entries in B, but that have at least one entry where B.Attr is NOT 2.

In the end, you want to separate the entries in A that only have matches in B with attr = 2, from all the others.

Is that correct now?

Yes for a) and b). No for that last part. If those two specific rules are satisfied then there's no need to separate anything. If it matches either a) or b) mark it with X.


Ok,

Take a look at this.

It gives you the results you showed (with the X in the same lines).

If you create in this code records that match my example or yours, the results will be the ones you showed.

For example, for yours:

Eduardo Jauch wrote:

If you create in this code records that match my example or yours, the results will be the ones you showed.

For example, for yours:

Are you sure that the newest version of your oml? I can't seem to be able to create anything.

Click in AS menu and add some A records and after that on the A records, you can add B records associated with them.

I'll attach it again in case I sent the wrong one...

Eduardo Jauch wrote:

Click in AS menu and add some A records and after that on the A records, you can add B records associated with them.

I'll attach it again in case I sent the wrong one...

How can I add B records? As soon as I create an A record an X appears next to it on main page.


Enter an A record Detail page and start adding B (it will appear the B table and the option to add or delete B records associated with this specific A record.

P.S. Sorry, I did this with scaffolding, didn't bother to make the interface more "friendly".

Eduardo Jauch wrote:

Enter an A record Detail page and start adding B (it will appear the B table and the option to add or delete B records associated with this specific A record.

P.S. Sorry, I did this with scaffolding, didn't bother to make the interface more "friendly".

Then where's the B table? I can only access the A table. I've already added a bunch of entries in the A table.


Hi,

Inside each A record (A detail page), after you created it, the B table will appear. Any B record you add will automatically be connected to the A record.

Oooh didn't realize the Text fields were clickable. This sorta works, but I get duplicates in A for every record that has 2 and something else in B. If it's just 2 then no dup, and if it's just something else also no dup.


Edit: dups start appearing as soon as I group by "if(B.Attr<>2, 1, 0)"

Hi osacc,

It is not possible to have duplicates of A, because the aggregate is grouping only by attributes of A. 

So, for example, record of A (text "E" has multiple records in B, two of them with attribute 2 two other values:

In the home screen, it appears only once:

If you look to the aggregate that feed this Table Records, you will see the following:

As you can see, only the A.Id and A.Text are grouped. So, it is not possible (at all) to appear repeated records of A.

Cheers.

The problem is that the calculated attributes with IF must not be grouped, but SUMmed.

Eduardo Jauch wrote:

The problem is that the calculated attributes with IF must not be grouped, but SUMmed.

OMG you're right, thank you so so much!! It would've probably not taken this long if I had paid more attention along the way, but thank you so freaking much for going through the entire solution with me!

haha,

Not a problem. I actually like very much this type of question, trying to find ways to do something with aggregates :)

Sorry, that took so long for me to understand what you needed.
I'm on vacations, probably my brain is also... xD

Cheers.