Updating custom field on with value from another custom field
We have a persistent custom field on the customer called EORI_REF and a persistent custom field on the customer order header called EORI_REF.
When the Order is created we would like the EORI_REF value held on the customer to be pulled through to the customer order.
The reason for this is most customers will have the EORI_REF but a few of our customers are catch-alls so we would like to be able to amend the EORI_REF on the customer order as required.
What is the best way to do this?
In the past I have used custom events or custom procedure that runs in the background to update values.
Thank you in advance for your advice.
Page 1 / 1
Hi @johnw66 ,
We have something similar where on the customer record on the address tab we have a custom field called SHIPPING_INSTRUCTIONS which we include on the customer order header screen.
The custom field on the customer record on the address tab is a persistent field it’s configured as a single line character field to hold 100 characters. One the customer order header we’ve added a custom field named SHIPPING_INSTRUCTIONS as well but we made this a read only field with an implementation type of SELECT. We then perform a select to display the information from the customer record on the address tab.
Select Statement: select CF$_SHIPPINGINSTRUCTIONS from CUSTOMER_INFO_ADDRESS_CFV WHERE customer_id=:customer_no and address_id=:ship_addr_no
Regards,
William Klotz
William
Thank you for your answer, unfortunately it cannot be a reference field as this is only read only. It needs to be a persistent field on both the Customer and the Customer Order.
Regards
John
Hi @johnw66 ,
So after making the custom field on the customer order header and customer header making sure they have the same character storage size I’d recommend creating an event on the customer order that fires when you insert a new customer order.
We have a persistent custom field on our customer order header named FINAL_CUSTOMER_PO_NO which we copy information from a field called LABEL_NOTE. While LABEL_NOTE isn’t a customer field the process is the same. Read data from LABEL_NOTE and insert into FINAL_CUSTOMER_PO_NO.
We have a custom event on the CustomerOrder logical unit setup to trigger on insert and update when LABEL_NOTE changes. The event trigger calls a custom stored procedure we created called OMNI_CO_EXT_TO_FINAL_PO. You’ll notice in the event actual PL/SQL code a line called Transaction_SYS.Deferred_Call which is key because it must run in the background to avoid getting the mutating trigger error message from Oracle.
I’ve also attached the stored procedure we are calling from the event action. The only thing I would change in the stored procedure if I were to redo it is I would narrow the Cursor statement to only return the fields I need instead of the entire customer order object. I created this stored procedure early while I was still learning PL/SQL and IFS back in 2015 but it’s working and isn’t causing performance issues so I’ve left it alone for the time being.
Regards,
William Klotz
Hi,
As an alternative (to custom event) you can have two separate Custom Fields on Customer Order.
First is read-only and just selects by sql value from Customer.
Second is persistent, null by default. Filled manually when there is special circumstance.
Then you can use them (e.g. on printouts or quick reports) like ‘if second is % then use it, if second is null then use first’.
BR, Lukasz
Hi @johnw66 ,
So after making the custom field on the customer order header and customer header making sure they have the same character storage size I’d recommend creating an event on the customer order that fires when you insert a new customer order.
We have a persistent custom field on our customer order header named FINAL_CUSTOMER_PO_NO which we copy information from a field called LABEL_NOTE. While LABEL_NOTE isn’t a customer field the process is the same. Read data from LABEL_NOTE and insert into FINAL_CUSTOMER_PO_NO.
We have a custom event on the CustomerOrder logical unit setup to trigger on insert and update when LABEL_NOTE changes. The event trigger calls a custom stored procedure we created called OMNI_CO_EXT_TO_FINAL_PO. You’ll notice in the event actual PL/SQL code a line called Transaction_SYS.Deferred_Call which is key because it must run in the background to avoid getting the mutating trigger error message from Oracle.
I’ve also attached the stored procedure we are calling from the event action. The only thing I would change in the stored procedure if I were to redo it is I would narrow the Cursor statement to only return the fields I need instead of the entire customer order object. I created this stored procedure early while I was still learning PL/SQL and IFS back in 2015 but it’s working and isn’t causing performance issues so I’ve left it alone for the time being.
Regards,
William Klotz
I 2nd that solution.
Cheers
Tommy
HI @johnw66
I asked a similar question and I was able to get an achievable input from this community. Pls refer below thread. This is based on different window (Sales Part Basic Data) but the end requirement is same.
Thank you everyone for your assistance. The detailed advice from William provided my solution. Now I am left with 1 issue, that is how to get the updated information to be displayed in the field without having to press ‘refresh’. I know that if this was a Custom Menu there is an checkbox where you can specify Refresh window after execution:
But unfortunately, you cannot do this from a PL/SQL block/custom event etc.,
