Page 1 of 1
Sublist on Edit Form
Posted: Sun Mar 28, 2021 1:16 pm
by 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
Re: Sublist on Edit Form
Posted: Sun Mar 28, 2021 3:03 pm
by 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“.
Re: Sublist on Edit Form
Posted: Sun Mar 28, 2021 5:15 pm
by 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
Re: Sublist on Edit Form
Posted: Sun Mar 28, 2021 5:35 pm
by kev1n
You can try something like this:
Code: Select all
SELECT * FROM customer WHERE customer_id IN (#ord_customer#)
Re: Sublist on Edit Form
Posted: Sun Mar 28, 2021 8:08 pm
by 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)
Re: Sublist on Edit Form
Posted: Sun Mar 28, 2021 8:55 pm
by kev1n
Inspect the Browse SQL using the Developer Console (F12)
Re: Sublist on Edit Form
Posted: Mon Mar 29, 2021 7:48 am
by kev1n
The values have to be separated with commas and apostrophes, like '2341234123','23424234','342534562456'
Re: Sublist on Edit Form
Posted: Mon Mar 29, 2021 11:07 am
by 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
Re: Sublist on Edit Form
Posted: Mon Mar 29, 2021 12:54 pm
by 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:
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);
Re: Sublist on Edit Form
Posted: Tue Mar 30, 2021 11:53 am
by oli
Thanks a lot for your support ... it's working now!
