Sublist on Edit Form

[oli]

Dear Community,

I’d like to solve following problem in nuBuilder but couldn’t find a solution so far.

In the edit form of table „order“ I want to see a list of all „customers“ (in a table with detailed information like cus_phone, cus_adress, …, …) that are assigned to this specific „order“.
Ideal would be to assign or remove customers on this list view from/to the order.

Can someone help me in this regard please?
Thanks a lot!

BR,
Oli

[kev1n]

Hi Oli

You can use an embedded Browse form (Run Object-> iFrame) to display the list of customers.
Use a Hash Cookie in the subform's SQL to filter out the customers that are assigned to that specific „order“.

[oli]

Thank you, but it seems that I need some more information to get it running.

I added a RUN object (iFrame) for the Form "CUSTOMER" to the "ORDER" form.
There I can see a list of all customers.
The tricky thing is to filter out the customers that are assigned to this order only.
On the ORDER table there is a field "ord_customer". This field contains all ID's of already assigned customers (comma separated) but I don't know how to use it as a filter condition for this RUN object.
Adding #ord_customer# as a hash cookie to filter or record ID to the RUn object doesn't work.

Any ideas?

Thank you.

BR,
Oli

[kev1n]

You can try something like this:

Code: Select all

SELECT * FROM customer WHERE customer_id IN (#ord_customer#)

[oli]

I added the SQL statement into the field FILTER auf the RUN object.
Unfortunately I get no results.

That's my SQL statement:

Code: Select all

SELECT * from kontakt WHERE kontakt_id IN (#auf_kunde#)

• kontakt = Name of the table for customers
• kontakt_id = ID of the customer table
• auf_kunde = Fieldname where all assigned Customer ID's can be found

I changed the names of the tables to german language (that's why there are different tablenames)

[kev1n]

Inspect the Browse SQL using the Developer Console (F12)

[kev1n]

The values have to be separated with commas and apostrophes, like '2341234123','23424234','342534562456'

[oli]

I tried different SQL statements and expected following statement should work:

SELECT * from kontakt WHERE kontakt_id LIKE CONCAT('%', #auf_kunde#, '%')

Because in "auf_kunde" the ID's are stored as follows:
["60534a8c3624b39","6054c3ba9e5594b"] <== 2 customers are assigned to this "auftrag"

Using the Console I couldn't see any results by injecting different SQL statements (hope I used it correctly):

Screenshot 2021-03-29 105844.jpg

Just for confirmation: The SQL statement needs to be added into the field "Filter" of the RUN object, right?

RUNScreenshot 2021-03-29 110053.jpg

[kev1n]

I created a small demo here:
https://test.nubuilder.cloud/index.php? ... e2696a41ab

The Browse is filtered by the values in the field fil_text.

Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID#

In PHP BB (Before Browse), I use this SQL:

Code: Select all

$create = "CREATE TABLE #TABLE_ID# ";

$filter = str_replace(']', '',str_replace('[', '', "#fil_text#"));

$qry = "SELECT * FROM browse_filter_computer WHERE browse_filter_computer_id IN (".$filter.")";

nuRunQuery($create.$qry);

[oli]

Thanks a lot for your support ... it's working now!