convertFoundSet still not working

Classic Servoy
1

I’m looking forward to the convertFoundSet feature but since it was released in RC3 it has had the same bug:

EXPECTED BEHAVIOR
Imagine my users are looking at a found set of orders and they want to then look at a found set of the customers who made those orders. How is this done? I had thought I would use convertFoundSet.

Same need for the opposite: users looking at a found set of customers (say it’s their high-end customers) and want to see all orders they’ve ever made.

PROBLEM
convertFoundSet as it is now does not do this. If customer #26 has 10 orders, it would show that customer 10 times!

Please look in the attached solution.

  1. create 3 records in the “customer” table with custID 1,2,3, and then create 3 records in the “marked” table with custID 3,3,3
  2. then if you click on the method “convertFoundSet” from the marked form it actually will display, over in the customer form, 3 instances of the customer with ID 3 when we know there should only be 1!

Is it also true that your plan for this feature is that there will be no limitation on the number of records in either the starting or the ending found set?

Thank you.

2

yes we don’t do a distinct or something on it
you are saying that you want to show id 3 3 times. because you are marking it.. if you don’t want it 3 times don’t mark it..

3

Johan, the fact remains, it’s displaying records that don’t exist in the table.

To put it another way, if I have a found set of orders and i want to see the people who made those orders and I use convertFoundSet, it must show only 1 instance of each unique person.

Is this clear?

Thanks

4

No it is not clear
it doesn’t show you any records that are not in the database
it does show you 3 times the customer 3. But as i said that is what you also specify.
There are 3 records in the marked table. And for every record in the marked table you will get a customer.. even if this is id is already specified before.

Just add to the 3,3,3 a serie others like 1,2,3,2,1,2,3,3,1
and you will see that you will get exactly those.

So with convertFoundset you have to take into account that this can happen with relations a many->one relation.

5

Johan, could you please explain, then, how to achieve the following:

  1. My users are looking at a found set of orders and they want to then look at a found set of the customers who made those orders.

  2. Same need for the opposite: users looking at a found set of customers (say it’s their high-end customers) and want to see all orders they’ve ever made.

Thank you.

6

for 2 this is pretty simple and will give you the results you want..

convertFoundset(foundset(of the customers),customer_to_orders);

will give you exactly the orders of all the customers in that foundset..
And only one order because a order never has 2 customers (or am i mistaken??)

for 1 you have youre double (or more) entry problem yes.

convertFoundset(foundset(of the orders),orders_to_customer)

will get you a customer for every order that is in the order/main foundset!
so it is possible thet you get this:

customer1 (pk=100)
customer2 (pk=200)
customer3 (pk=300)
customer2 (pk=200)
customer4 (pk=400)
customer1 (pk=100)

if you had 6 orders displayed in youre orders foundset..

7

For convertFoundSet to be useful to users, they need to be able to sort and print on what is returned.

I don’t know what SQL code is behind convertFoundSet, but would it be possible to use DISTINCT somewhere?

8

no not possible in sql, or we need to generate some kind of in query/sub query. But not all databases do support this.

you say that you want to sort. if you want that then distinct is not possible. because you can’t distinct on one column and sort on another..

i’ll give you an example:

c1|c2
10|A
20|B
10|Z

now you do this:

select distinct(c1) from X order by c2

what should the order be ??

should you see:

10|A
20|B

or

20|B
10|Z

??