Hello,

Entity B is a child of entity A and I am building a list of entity B joined to and filtered by entity A.  If a local variable is set, I also need to join entity B to entity D via junction entity C, but if the variable (the ID of entity D) is not set I do not want to join to entity D at all.  The list of entity B is output to a form.

How do I do this in Outsystems ?

Cheers, Richard.

Hi Richard,

By using a more permissive Join condition, you can create the Joins you describe and not have them affect your total result list in case the table has no match.

My question is, is there a real need to make the join truly "optional"?

So, let's see if I understood...

Does entity B have an FK to entity A?
And entity C has an FK to entity B, and entity D has an FK to C?

If so, I have this example.

(plus the filter for A)

Cheers.

Eduardo Jauch wrote:

So, let's see if I understood...

Does entity B have an FK to entity A?
And entity C has an FK to entity B, and entity D has an FK to C?

If so, I have this example.

(plus the filter for A)

Cheers.

Thank you for taking the time and effort to create a form of prototype !  I rushed my question a little because I thought sensible people would not be working over the weekend :-).  But I think I need to provide a bit more detail to see if we can get to a solution.  You were nearly right, except that Entity C has a foreign key to Entities B and D. What makes it more interesting is that Entity D also has a foreign key to Entity A, so the solution for Entity B should also work for Entity D.  I have created the entity diagram below to show the relationships.


I have 3 combo boxes on my web page, 1 each for entities A, B, and D, and I am trying to achieve the following behaviour:

1.  The combo boxes will initially be populated with the unique values for A, B, and D.

2.  If the user selects A, then B and D will be filtered by A.

3.  If the user selects B, then A will automatically be selected (if not already selected) and D will be filtered by B via C.

4.  Likewise if the user select D, then A will automatically be selected (if not already selected) and B will be filtered by D via C.

I have managed to achieve (2), (3), & (4), but (1) is not initially showing unique values for B and D, instead it is showing the intersection of these entities with C.  I suspect that I don't quite have the right join types for populating B and D via C.

I'm really appreciating the help you guys are providing.

Cheers, Richard.

Hi Richard, 

To populate the combo boxes with unique values, you will need to create one aggregate per combo box and use Group By to isolated the unique values. 

The last aggregate to show the list will probably not use the IDs but Code or Name to make the joins. 

I'll try to provide an example at night (when I have more time). 

Cheers

Hi,

I am looking at your data model and have some questions.

1. In D, can exist more than one pair Name/Code with the same values pointing to different records in A?

2. In B, can exist more than one pair Name/Code with the same values pointing to different records in A?

3. In D, can exist different pairs Name/Code pointing to the same record in A?

4. In B, can exist different pairs Name/Code pointing to the same record in A?

5. In C, can exist a record that points to a B and a D pointing to the different records in A?

Cheers

Eduardo Jauch wrote:

Hi,

I am looking at your data model and have some questions.

1. In D, can exist more than one pair Name/Code with the same values pointing to different records in A?

2. In B, can exist more than one pair Name/Code with the same values pointing to different records in A?

3. In D, can exist different pairs Name/Code pointing to the same record in A?

4. In B, can exist different pairs Name/Code pointing to the same record in A?

5. In C, can exist a record that points to a B and a D pointing to the different records in A?

Cheers

Hi Eduardo,

In answer to your questions:

1. No

2. No

3. Yes

4. Yes

5. No

Cheers, Richard



Hi,

Please, take a look at this and see if this is more or less what you need.

Cheers

Eduardo Jauch wrote:

Hi,

Please, take a look at this and see if this is more or less what you need.

Cheers

Hi Eduardo,

Thank you so much for creating the prototype file which I had a good look at and a bit of a play with.  Unfortunately it did not give me all 4 behaviours that I was looking for above.  I had arrived at something similar and in the end I settled for a compromise that gave me all of 1-4 except the last part of 3 & 4: where if B is selected then D will be filtered by B via C and vice-versa.

The issue here is that the form is very constrained in the sense that you have to define the source aggregate (and only one aggregate) for the form.  I have extensive experience with a different low-code development application (otherwise known as CASE tool) called CA Gen which is used in corporations and government organisations.  In CA Gen you do it the other way round: you define the target form for the aggregate and you can do it dynamically within the code.  This leads to a much higher degree of flexibility while still maintaining integrity and also provides better performance because you only need to define the entities and the joins you need in any particular scenario.

Unfortunately I don't have sufficient loose change in my pocket to afford CA Gen so I'll adapt to using Outsystems as best as I can !

Cheers, Richard.

Hi Richard, 

Maybe I didn't understand your requirement?

When you first state the problem, I was with the word "list", so I was working with the output being placed on a "List" in the screen.

But you are talking about a form...

A Form, in OutSystems, has a source that must be a single record.
Usually, we use the pair "List Page" + "Detail Page".

But it seems that you want to set the result directly to the form...? Is this correct?

The Form doesn't need to be attached to an aggregate. Its source needs to be a list, but it can be a local list, that you can change in code...

I confess that I am now a little at a loss here, about what exactly you need to do... :(
And I found this problem very interesting! :D

If you provide a bit more information we may find a way of doing what you need :)
I think the problem is really that I am not understanding entirely your requirement...

Cheers. 

P.S: Probably a print of the page you're trying to build would help me understand the requirement.

Cheers.

Eduardo Jauch wrote:

Hi Richard, 

Maybe I didn't understand your requirement?

When you first state the problem, I was with the word "list", so I was working with the output being placed on a "List" in the screen.

But you are talking about a form...

A Form, in OutSystems, has a source that must be a single record.
Usually, we use the pair "List Page" + "Detail Page".

But it seems that you want to set the result directly to the form...? Is this correct?

The Form doesn't need to be attached to an aggregate. Its source needs to be a list, but it can be a local list, that you can change in code...

I confess that I am now a little at a loss here, about what exactly you need to do... :(
And I found this problem very interesting! :D

If you provide a bit more information we may find a way of doing what you need :)
I think the problem is really that I am not understanding entirely your requirement...

Cheers. 

Hi Eduardo,

I agree that it is an interesting challenge !  I'm sorry my use of the word "list" sent you down the wrong track, what I meant was the dropdown list in the Combo box.  I have modified the oml file you sent me to show more detail of what I am trying to achieve.  Refer to the E Detail Screen below and the behaviour rules that I listed previously for a better understanding.  Once B and D have both been selected, then E can be created (or updated) and joined to them (I am choosing to do this via a foreign key to C at this stage).  If may also be worth mentioning that A (8 rows), B (~15,900K rows), C (~16,200 rows), D (~2700 rows) are all static data, although I am only defining A as a static entity.  So as you can see performance is going to be an important factor.

In CA Gen this would all be pretty simple but Outsystems appears to be somewhat constrained so I'm trying to work with what we have.  And I love the logo that you have used for the application - if only I had a nuclear powered brain ! :D

Cheers, Richard.

Hi Richard,

Please, take a look into page EDetail2 to see if it is more or less what you are looking for.
Notice that I did this very fast and so I didn't use the "best practices"... I just put something together.

Also, with this amount of records in entities B, C and D, I would not use combo boxes, as they will fetch everything from the database and your page will suffer a huge performance penalty. I would probably use some kind of Popup with a search bar that starts to fetch results only after 3 or 4 characters typed...

Cheers.

Sorry, I forgot the attachment... :D

Eduardo Jauch wrote:

Hi Richard,

Please, take a look into page EDetail2 to see if it is more or less what you are looking for.
Notice that I did this very fast and so I didn't use the "best practices"... I just put something together.

Also, with this amount of records in entities B, C and D, I would not use combo boxes, as they will fetch everything from the database and your page will suffer a huge performance penalty. I would probably use some kind of Popup with a search bar that starts to fetch results only after 3 or 4 characters typed...

Cheers.

Thanks again Eduardo !

I agree that due to the constraints in play here, there is a real performance penalty.  The popup with a search bar is an idea worth trying and it's a new one for me.  Where can I learn how to do this ?

Cheers, Richard.

Hi Richard,

You can use a real popup: https://success.outsystems.com/Documentation/11/Developing_an_Application/Design_UI/Inputs/Create_and_Use_a_Popup

Some of the functions here are deprecated, but I heard that in a new version of the platform there are substitutes.

You can also use a Modal to emulate a popup, with a web block that will do the search and trigger an event to warn about the selection.

Cheers.