Portals based on Joins

Classic Servoy
1

I want to display portal rows which contain data from a directly related table and also from a ‘3rd’ table which holds additional related content

3 tables are used in this example: DATA, DATA_JOIN, APPLICATION.

Table DATA holds (part of) the information I wish to display in the portal rows. It is the place where “content” is held.

Table DATA_JOIN is a ‘shared’ table to support many ‘applications’ having access to DATA. It does this by using columns to identify which applications (and their records) refer to which DATA items.

Tables named something like APPLICATION will have a form which contains a portal where records are created and the display is to occur.

Here is my basic table setup.

DATA
-dataid (PK)
-displayValue (‘content’ - a string to be displayed in the portal)

DATA_JOIN
-data_joinid (PK)
-dataid (a pointer to a particular DATA record)
-appid (a pointer to a particular APPLICATION record)
-app_table_name (the table in the ‘application’ to which this join has been created - this allows me to support many-table access to DATA)

APPLICATION
-appid (PK)
-app_table_name (the table in THIS ‘application’ to which the join has been created)

So, how do I show in an APPLICATION form representing ‘app_table_name’ the related record data from DATA_JOIN (the trivial part) abd also the DATA values (the part which appears to be more difficult) ?

And then, how do i script a button within a portal row to perform the necessary inserts or updates ?

mtia

2

Hi Salernoj,

have you considered using tabpanels instead of portals. I find them times more smart, reusable and easy to handle than portals.

3

SalernoJ:
So, how do I show in an APPLICATION form representing ‘app_table_name’ the related record data from DATA_JOIN (the trivial part) abd also the DATA values (the part which appears to be more difficult) ?

And then, how do i script a button within a portal row to perform the necessary inserts or updates ?

Hi SalernoJ, automazione is right. Forget about portals, use tab panels instead – they are 100 times better. You can hide the tabs to make it look like a portal. In the tab panel, you show your a form based on your join table, so the DATA values are only one hop away…

Set up buttons and scripts to add and delete records in the join table on this form – very easy… works well.

4

Hee watch out! dont mess with portals! :-) :-)

portals can be set to: resizeable (yes/no)
portals can be set to: reordable (yes/no)
portals can be set to: sortable (yes/no)

All very handy tools, which you (still!) can’t do with tabpanels. :roll:

5

We allow sorting within a tab panel by attaching a method like this to the label at the top of each column on the form within the tab panel:

if (globals.gToggle == "")
{
globals.gToggle = "1"
forms.tab_letters.controller.sort('letter_name desc');
}
else
{
globals.gToggle = ""
forms.tab_letters.controller.sort('letter_name asc');
}

I just did not want to leave anyone with the impression that sorting within tab panels is not possible.

6

Create a form and set it’s View property to Table View (Locked). Use that as a subform in a Tab Panel… you get sortable, resizeable and reordable.

Am I missing something here? (which is often the case!)

Best,

Rich Coulombre

7

The responses here about tab-panels are very helpful indeed.
I have taken the advice and now use a combination of portals with tabpanels.

However, I am still be interested in achieving the data from 2 separate tables (via a join) in one single portal row.

The reason is that the DATA_JOIN table allows many APPLCATION records to point to the same DATA records. This means ‘sharing’ as opposed to ‘duplicating’.

The portal would show data from a select made from APPLICATION to DATA_JOIN and then create a (distinct) result set by a join to DATA. I guess the real question is “how do i specify SQL for portal rows ?”

thanks

8

Portals can only be controlled using relations.
Tabpanels can be controlled using relations OR by loading your own foundset.

In your case (without having fully read your post) I would place a relationless tabpanel and load the foundset with the controller.loadRecords and
your custom build SQL query.
(check out the sample code by right clicking loadRecords function.

//4) to load records in to the form based on a query (also known as 'Form by query')
//controller.loadRecords(sqlstring,parameters);
//limitations/requirements for sqlstring are:
//-must start with 'select'
//-must contain 'from' and 'order by' keywords
//-the 'from' must be a comma separated list of table names
//-must at least select from the table used in Servoy Form
//-cannot contain 'group by' or 'having'
//-all columns must be fully qualified like 'orders.order_id'
//-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like 'select a_id, b_id,c_id ...')
//-can contain '?' which are replaced with values from the array supplied to parameters function argument
9

Additional remarks after reading full post :wink:

1)create a form on DATA_JOIN table and place related fields to DATA table as well.
2)create a form on APPLICATION table and place relationless tabpanel showing formOnDataJoinTable.
Load this form by using ‘form By Query’ as mentioned in previous post.
I assume, having read that you want some kind of DISTINCT set of records which probably can only be done with a custom query, this would be the way go.