Use Servoy relation instead of DISTINCT clause

Classic Servoy
1

Hi,

I have a line items table with records related to other tables via foreign keys:

------------------------------------------------------------
| pk_id | promo_date_id | promo_id | product_id | store_id |
------------------------------------------------------------
|   1   |        1      |    10    |    100     |   1000   |
|   2   |        1      |    10    |    100     |   1001   |
|   3   |        1      |    10    |    100     |   1002   |
|   4   |        1      |    11    |    101     |   1000   |
|   5   |        1      |    11    |    101     |   1005   |
|   6   |        1      |    12    |    100     |   1003   |
|   7   |        1      |    12    |    100     |   1004   |
|   8   |        2      |    15    |    102     |   1000   |
|   9   |        2      |    16    |    100     |   1006   |
...

I am showing, from the Promo_date table, a tabpanel with a list of related Promos. From there, I would like to show another tabpanel with all related unique products. Is there a way to achieve this result only using Servoy relations, without doing a SQL query with the DISTINCT keyword on record select/show? I’m thinking of performance improvements, and also down the line I will need to create reports with this information and would like to avoid putting in a sub-summary part just to make sure I only have one record per product. Any help would be greatly appreciated.

Thanks in advance,

Ben

2

Okay, looks like it’s time for some of my famous ASCII art… :roll:

Here is an idea of what I’m trying to achieve:

-----------------------------------------------
|  PROMO  ||         ||           ||          |
|  DATE   ||  PROMO  ||  PRODUCT  ||  STORES  |
|---------||---------||-----------||----------|
|   _ _   ||   _  _  ||   _   _   ||          |
|   _1_   ||   _10_  ||   _100_   ||   1000   |
|    2    ||    11   ||           ||   1001   |
|    3    ||    12   ||           ||   1002   |
|    4    ||         ||           ||          |
-----------------------------------------------

-----------------------------------------------
|  PROMO  ||         ||           ||          |
|  DATE   ||  PROMO  ||  PRODUCT  ||  STORES  |
|---------||---------||-----------||----------|
|   _ _   ||         ||   _   _   ||          |
|   _1_   ||   _10_  ||   _101_   ||   1000   |
|    2    ||   _11_  ||           ||   1005   |
|    3    ||    12   ||           ||          |
|    4    ||         ||           ||          |
-----------------------------------------------

Any takers?

3

You have to upgrade your ASCII art to SQL art and you’re all set :D

4

Good one, Jan! :D

But seriously, I figured out a SQL way of getting the intended result, by using reusable tabpanel forms and the .getFormContext() function on record selection of the parent tabpanel to define the child tabpanel’s foundset using a query.

Still would be a nice option to have though, methinks…

5

Ben,

Have a look at databasemanager.convertFoundSet().

Rob