Two tables in same list

Two tables in same list

  

Hello!

I am quite a new with Outsystems and also I'd like to state, that i have no programming experience. But I like Outsystems and trying to create management app for my team.

I have 2 tables in db. Contacts and Companies. Is it possible to display in the same list on mobile app? So far I have managed to get two lists, but the problem is, it is displaying them contacts and companies, but at first companies list and then contacts list. Can I merge these 2 tables into list?


Thank You! 



I presume there is some connection between your two table e.g. Contacts belong to a company and therefore a Contact would have a foreign key to the company.

If so simply join the tables together in an aggregate and create your list form the aggregate.


You can do this simply by just creating an aggregate with your Company table and dragging the Contact table to the aggregate which will then create the join for you.

If you want to use scaffolding, simply drag the company table to the Interface canvas which will create your basic list and UI for it , open the aggregate and drag your contact to it, which will create the join, and then open the UI and drag fields from your contact table to the list.


vemp temp wrote:

Hello!

I am quite a new with Outsystems and also I'd like to state, that i have no programming experience. But I like Outsystems and trying to create management app for my team.

I have 2 tables in db. Contacts and Companies. Is it possible to display in the same list on mobile app? So far I have managed to get two lists, but the problem is, it is displaying them contacts and companies, but at first companies list and then contacts list. Can I merge these 2 tables into list?


Thank You! 




Hi Vemp,

This is a data model thing. First you need to know what is a join and for what is needed. With that join, you are relating the data from one table to the other one. After that, you can have at least two scenarios:

1. Each company has at max one contact:

just use an aggregate and add the "join" between them.


2. Each Company, can have more then 1 contact: 

Create a webblock that receives the CompanyId and show a list of contacts for it. In each Company record you will see the contact list :)

Regards


Just to add. There is another topic for the second scenario:


https://www.outsystems.com/forums/discussion/32844/how-to-display-a-list-within-a-list/


Regards

Hi, guys!


Thank You for taking time to answere! The idea is get those records form different tables to same list. I don't want them being filtered. Just the simple list where contacts and companies are in the same list sorted A>Z. As far as i understand Your solutions will get them aggregated by the company and does not solve my problem. Same thing will be with meetings and tasks. Both have ProjectId, but i want to show them mixed in to tha same list on tha project dashboard.


TY

Vemp!

Hi Vemp,

You want the list of companies mixed with the list of contacts in alphabetical order. Meaning that if you have 5 companies and 3 contacts you want a list with 8 elements. Is that it?

You can do that by:

  • Having a Structure with the attributes that you need
  • having a variable of that type of Structure
  • having a new variable which is a List of elements of that Structure
  • do a foreach on the List of Contacts and another foreach on the Companies
  • For each element of the Lists, assign the attributes to the structure and do a ListAppend to append that structure to the new List.
  • Use the New List as source of the List widget

If you want the list sorted then you can use ListSort.

That should do it.

Cheers,

José

Solution

José Costa wrote:

Hi Vemp,

You want the list of companies mixed with the list of contacts in alphabetical order. Meaning that if you have 5 companies and 3 contacts you want a list with 8 elements. Is that it?

You can do that by:

  • Having a Structure with the attributes that you need
  • having a variable of that type of Structure
  • having a new variable which is a List of elements of that Structure
  • do a foreach on the List of Contacts and another foreach on the Companies
  • For each element of the Lists, assign the attributes to the structure and do a ListAppend to append that structure to the new List.
  • Use the New List as source of the List widget

If you want the list sorted then you can use ListSort.

That should do it.

Cheers,

José

Or, use a "simple" advance query with a union and ordered :)

Regards


Solution

Right! Thats wot I need, but lets start from the beginning now. 

  • Having a Structure with the attributes that you need - do I have to create 2 structures or is just 1 enough? Do I have to get data from database from both tables?
  • having a variable of that type of Structure - is it a list or record type?
  • having a new variable which is a List of elements of that Structure - i think that point i understood...
  • do a foreach on the List of Contacts and another foreach on the Companies - what is foreach?
  • For each element of the Lists, assign the attributes to the structure and do a ListAppend to append that structure to the new List.
  • Use the New List as source of the List widget


If you want the list sorted then you can use ListSort. - That is perfectly understood! :)


Ty

Vemp!

Miguel Sousa wrote:

José Costa wrote:

Hi Vemp,

You want the list of companies mixed with the list of contacts in alphabetical order. Meaning that if you have 5 companies and 3 contacts you want a list with 8 elements. Is that it?

You can do that by:

  • Having a Structure with the attributes that you need
  • having a variable of that type of Structure
  • having a new variable which is a List of elements of that Structure
  • do a foreach on the List of Contacts and another foreach on the Companies
  • For each element of the Lists, assign the attributes to the structure and do a ListAppend to append that structure to the new List.
  • Use the New List as source of the List widget

If you want the list sorted then you can use ListSort.

That should do it.

Cheers,

José

Or, use a "simple" advance query with a union and ordered :)

Regards


Ty for Your time! What is "simple advance query with union and ordered?


vemp temp wrote:

Right! Thats wot I need, but lets start from the beginning now. 

  • Having a Structure with the attributes that you need - do I have to create 2 structures or is just 1 enough? Do I have to get data from database from both tables?
  • having a variable of that type of Structure - is it a list or record type?
  • having a new variable which is a List of elements of that Structure - i think that point i understood...
  • do a foreach on the List of Contacts and another foreach on the Companies - what is foreach?
  • For each element of the Lists, assign the attributes to the structure and do a ListAppend to append that structure to the new List.
  • Use the New List as source of the List widget


If you want the list sorted then you can use ListSort. - That is perfectly understood! :)


Ty

Vemp!

Miguel's approach is better.

You use one structure and a SQL query where you do a union of both tables and do the order by.

Cheers,

José


IMO Advanced queries should be avoided at (almost) all cost, and structures I believe count as Application Objects, which cost money, so I would avoid them if I could also.

Keith Matthews wrote:

IMO Advanced queries should be avoided at (almost) all cost, and structures I believe count as Application Objects, which cost money, so I would avoid them if I could also.

Hello Keith,

This is the information that I have related to AO's:

Tables 

Entities (Single and Multi-Tenant) 1 AO

Static Entities 1 AO

External Tables (Via Integration Studio) 1 AO

Structures 0 AO

Reference Entity from external Outsystems Espace 0 AO


It's important to explain why you think we should avoid Advance Queries at all costs and not only that we should :) Can you please explain your point?

Thank you.


Hi Keith,

Complementing what Miguel said:

Regarding AOs:

https://success.outsystems.com/Support/Enterprise_Customers/Licensing/Overview/Application_Object_count

Regarding SQL queries:

They should be avoided but not at almost all costs. Sometimes, (as in this post) it is much much more efficient to have the database return a complex query and sort it than to do two aggregates, an iteration on the lists and sort the list.

Nevertheless, here are a few guidelines regarding SQL queries:

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Cheers,

José

José Costa wrote:

Hi Keith,

Complementing what Miguel said:

Regarding AOs:

https://success.outsystems.com/Support/Enterprise_Customers/Licensing/Overview/Application_Object_count

Regarding SQL queries:

They should be avoided but not at almost all costs. Sometimes, (as in this post) it is much much more efficient to have the database return a complex query and sort it than to do two aggregates, an iteration on the lists and sort the list.

Nevertheless, here are a few guidelines regarding SQL queries:

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Cheers,

José

Thank you José for your extra explanation :) 


OK looks like I am wrong about structures counting as AO's, not sure if that has changed over the years.

I avoid Advanced Queries, because they are more difficult to maintain, not always understood by everyone, are not compatible across dbms's and particularly because they have caused me problems previously when migrating apps from one environment to another. OutSystems will do things automatically with aggregates that it won't do with advanced queries. They could be more efficient in some cases where the DBMS engine may be able to do the work faster than the generated code, they may also be less efficient if the query is badly written and/or the relevant indexes are not there to support the query. Not everyone can write good SQL whether it works or not.


From the link above

"The SQL element allows you to execute, test, and review custom SQL queries in your applications. The element provides flexibility in data manipulation, but we recommend using Aggregates when applicable. Aggregates are highly optimized and easier to maintain."


Hello Keith,

Maybe you are confusing Application Objects with Software Units?

Regarding SQL. Just a small add to the discussion.

You are right in your arguments. It is easier to maintain aggregates. It is database agnostic. It is optimized by the platform. Etc.

Unfortunately, in order to achieve this, you loose flexibility. And there are things you simply can't do with them. 

By my experience, a good query is easier to maintain than a messy aggregate associated with logic. And usually much more performative.

This is a case where a very simple query solves the problem, that would require much complex logic.

And in the end, if you will work with SQL databases, doesn't hurt to learn it ;)

Cheers

Eduardo Jauch