In the previous post I showed some options how you can represent a many-to-many table relationship in a report using the LISTAGG Oracle function.
In this post we will edit a record and see how we can represent the data in a Form and save the data back to the different tables.
First I create a Form on the main table (customers) by just following the wizards.
Next I'll add a Shuttle item to the page: P2_PRODUCT_IDS
Finally for the item source value we can't use a Database Column as the data is in a different table, so we enter the select statement to get all the product ids for that customer. Note that the Source Type needs to be set to SQL Query (return colon separated value), so it returns 1 or more product ids.
The selected products will be shown on the right in the shuttle.
Here's how the Form looks like when we select John Dulles who's interested in two products (Jacket, Business Shirt):
When we move Products from left to right and the other way and hit Apply Changes we need to store those values.
Add a new Process after the build-in Process and call it Save Products with this code:
We first store the selected products in an array (l_vc_arr2) which apex_util.string_to_table is doing.
Next we delete all (possible) records that are not selected. You could remove the last line in the where clause so all products for that customer are deleted (if you add all the selected ones later again), but if you're auditing that table your info in not correct as that person might not have actually deleted it.
I added some debug info in the process too.
Finally we loop through the array and check if the record already exists in our table, if it doesn't we add it. Again here you could not do the lookup if you are dropping all records in the delete statement and just add all selected again.
I typically have a condition on this request to not run when the request is Delete.
In the online example (click on the edit icon in the report) I dropped the Create and Delete buttons in the Form, but if you keep them and want everything to work, there're two more things you have to do:
-) For the Create - in the "Automatic Row Processing (DML)" Process (of the Customer table) you need to specify P2_ID in "Return Key Into Item" field so the next process (the one you see above) has a value for P2_ID (= customer id).
-) For the Delete - you need to add another process before the "Automatic Row Processing (DML)", so the child records get deleted first, before the automatic row process deletes the customer.
In the next post I'll give an example of working with this data in a Master-Detail form.
10 comments:
Dimitri,
Thanks for the article. However you can improve on your shuttle tieback process a bit by getting rid of the looping.
Assuming you have a table of products named dimi_products with product_id as it's primary key, your delete would remain the same but your insert section could be simplified to a single query statement:
delete
from dimi_customer_product
where customer_id = to_number(:P2_ID)
and ':'||:P2_PRODUCT_IDS||':' not like '%:'||to_char(product_id)||':%';
insert into dimi_customer_product (customer_id, product_id)
(select to_number(:P2_ID)
, product_id
from dimi_products
where ':'||:P2_PRODUCT_IDS||':' like '%:'||to_char(product_id)||':%'
minus
select customer_id
, product_id
from dimi_customer_product
where customer_id = to_number(:P2_ID));
Thanks for the comment SSentinel, your solution is definitely a good option in this case
Thanks,
One thing not handled by this code is lost update detection. APEX takes care of this regular forms, both single record and tabular, but when you do the updates yourself from a shuttle or other form element this protection is lost.
I've taken to adding a hidden element to my forms with A data source similar to that of the shuttle e.g.:
Name: P2_PRODUCT_IDS_LUD
Display As: Hidden
Source Used: Always, ...
Source Type: SQL Query (return single value)
Maintain Sess State: Per Session
Source Value:
SELECT LISTAGG(product_id,':') WITHIN GROUP (ORDER by product_id)
from dimi_customer_prduct
where customer_id = :P2_ID
Then creating an Item Level Validation on the the shuttle using the PL/SQL validation method. I've used a PL/SQL Function Returning Boolean, but a function returning Error Text or throwing a PL/SQL Error would work as well. For the validation expression I use something like this:
DECLARE
l_db_val varchar2(4000);
BEGIN
SELECT LISTAGG(product_id,':') WITHIN GROUP (ORDER by product_id)
INTO l_db_val
from dimi_customer_prduct
where customer_id = :P2_ID;
RETURN NVL(l_db_val,'#') = NVL(:P2_PRODUCT_IDS_LUD,'#');
END;
Then make sure it has an appropriate message:
The Shuttle Data has changes please refresh this record and try again.
Notice that the selection statement in the PL/SQL block and P2_PRODUCT_IDS_LUD is nearly identical to the one in P2_PRODUCT_IDS but it uses LISTAGG to get the colon separated list instead of relying on the source type to do the aggregation.
Dimitri,
I am quite a newby in developing application based on APEX. I stumpled over your Blog.
I have read this post about the Shuttle fields and I do really appreciate this one. This Shuttle fields caused me quite a headache.
I actually have a question how to 'extend' the shuttle in that way , that there could be more information presented than just the many-to-many relation. In my case there a additionaly information stored within the 'table-in-the-middle'. For instance there could a date information on how long a specific relation could be valid. Currently I feel like I 've been treading water to get close to an solution. I would really appreciate it if you would point me to a possible solution.
Regards
Thomas
Hi Thomas,
You could concatenate your shuttle display value with the date?
Or have a Dynamic Action on change to show the date in an item?
Dimitri
Hi Dimitri,
thanks for your answer and sorry for coming back so late. It took for me to get back to APEX. APEX is currently stil my personal hobby a long my daily work.
I was thinking about your suggestions and it told me that I have to had more specific. The issue is not only to show the additional informations of the "table-in-the-middle".
It is a bit more challenging (for me). I would like to make changes to these additional information by e.g. set a the date values manually.
I was thinking of creating a pop-up window to show the values and implement a dialog to offer the user the possibilty to make those changes.
But I would appreciate if you could offer me a more sophisticated idea.
Thanks in advance
Regards
_Thomas
Hi Dimitri,
How many values can handle on shuttle?
Thanks
Srinath
Working with index in place
BEGIN
apex_debug.message (p_message => 'Delete existing records first', p_level => 3);
DELETE dimi_customer_product
WHERE customer_id = :P2_ID
AND product_id NOT IN ( SELECT to_number(regexp_substr(:P2_PRODUCT_IDS, '[^:]+', 1, level)) as ID
FROM dual
CONNECT BY regexp_substr(:P2_PRODUCT_IDS, '[^:]+', 1, level) IS NOT NULL
);
apex_debug.message (p_message => 'Inserting new records', p_level => 3);
INSERT INTO dimi_customer_product ( customer_id, product_id)
SELECT :P2_ID, to_number(regexp_substr(:P2_PRODUCT_IDS, '[^:]+', 1, level)) as ID
FROM dual
CONNECT BY regexp_substr(:P2_PRODUCT_IDS, '[^:]+', 1, level) IS NOT NULL
MINUS
SELECT :P2_ID, product_id
from dimi_customer_product
where customer_id = :P2_ID;
END;
Thank You, very useful
Dear ,
For Delete , we can do it in Database ,while createing the product table , --> ON DELETE CASCADE , no need for a process .
CREATE TABLE dimi_customer_product
(
column1 customer_id null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (customer_id )
REFERENCES parent_table (customer_id)
ON DELETE CASCADE
);
Post a Comment