Hello! I am very new to writing BPMs, so I apologize in advance if this is easily attainable. I’ve reviewed some of the educational material and I’m not able to discern a way to do this without help… hence why I’m turning to you!
I am trying to create a BPM that writes a new record to a UD table (UD08) whenever a field is changed within Order Entry (UnitPrice, SellingQuantity, or RequestDate). The fields it would add to UD08 would be some stock fields and some custom fields (OrderNum, OrderLine, ChangeType_c, ChangeReason_c, ChangeNotes_c, ChangedBy, and ChangeDate).
Bonus points if you’re able to tell me how to also get it to capture how the revenue changed in an additional calculated field! (old unit price × old quantity, versus updated unit price × updated quantity).
I’ve only created one other BPM to capture the date a query was last used, following these instructions. I tried to apply the same logic to this task, except placing it in Pre-Processing under SalesOrder.Update, and I couldn’t get anything to work.
I’ve tried searching the site for similar requests, but almost all of the solutions I’ve found have been to enable the ChangeLog in the business object. We want to write the changes to a UD table because we want to capture the changes across all sales orders, while the ChangeLog only focuses on one sales order at a time. Writing the changes to a UD table also means I’ll be able to create a nifty SSRS report to analyze and make the data presentable for those who are asking for it.
For your bonus question. In the pre processing. you could do a get by id and do the math. the getbyid will return what is still in the database vs what is being changed.
You can then also use widgets to create a new ud08 record. fill the table and then update the new ud08 record.
@knash Thank you for trying to help out, I really appreciate it!
I suppose I should have included what I’ve tried so far! I currently have a Pre-Processing BPM on SalesOrder.Update (all using the Workflow Designer, because I know nothing of C#):
The BPM has a variable type of Ice.Tablests.UpdExtUD08Tableset
Added a Fill Table by Query widget - the query just pulls all of the fields mentioned in my original post, and then maps those fields to the UD08 table
Added Invoke BO Method widget, invoking the UD08.UpdateExt
That’s all I have. For the other BPM I wrote (logging the last use date of BAQs and who ran it in a UD table), it works perfectly… but honestly, I have no idea whether the use case can even be applied to this situation. I thought it could as long as I switched it from Post-Processing on DynamicQuery.Execute to Pre-Processing on SalesOrder.Update, but that’s obviously not doing anything!
First thought is you are mapping the fields to the ExtUD08 tableset, but are you setting the RowMod = “A” to indicate the update needs to add new records?
@Chris_Conn@knash Thank you both for taking the time to troubleshoot with me!
I made sure the RowMod was set to “A”, it’s still not filling the table with anything though. Here are some screenshots, I’m not sure where I’m going wrong:
@knash A few questions - my apologies, just trying to learn from this for future reference!
Would the GET be placed before or after the FILL TABLE that I have (or replace it)?
Is there a reason why I would use UPDATE (like you have) versus UPDATEEXT that I currently have?
Is there a way I can insert a widget between two widgets in the Designer? I’m not seeing a way to do that, or to remove widgets once they’re added, which seems like rudimentary stuff that I must be blind to, ha!
Correct GetaNew would be first. Sorry I thought you could read my brainwaves from the picture I posted yesterday.
You should be able to add widgets to what you have. If you click on the connector lines you can delete them. Then you can redraw them in the order you want.
Widgets
GetaNewUD08 (The picture show what the widget is calling)
BO Ice.UD08
Method Name: GetaNewUD08
for the parameter you can the one you have or create a new one.
2)FIll Table
Update
I believe the UpdateExt is deprecated, smarter folks can chime in.
@knash Oh, I think you’ve got me so close to a solution! Now I believe I’ve run into a different issue - when testing the BPM by changing a sales order and clicking save, I’m getting an error that the record is already in the UD08 table. I’m assuming this is because the Key I assigned isn’t actually unique. I’ve got it set up to be a combination of the Order Num + Order Line + Price + Qty + Date, so I’m not sure why it wouldn’t be considered unique whenever a sales order is updated, since one of those values (price, qty, or date) would always be changed.
Is there a way to assign an auto-generated unique key to every single new save in a sales order?
@knash I may be far over my head with this one… I tried my best to review the PDF and the C# code included in it, but I am honestly at a complete loss on how to change up the BPM and code to apply to my situation (setting a field in a UD table) versus the example use case of applying it to an ERP field.
What’s got me confused is that I’m getting the Duplicate Row Error, but there’s only one row in the UD table and all of the fields are empty.
@knash@Chris_Conn These both seem like great solutions! I tried to test Chris’ solution first with using the SysRowID as Key1, but I’m running into a separate issue. To test it, I go to the Order Entry module and change the selling quantity, then click save (which should add the record to the field). Then I pull up my UD table in a BAQ, which shows that a record has been added, but all fields are completely blank. Then I go back to the same sales order and change something else on it, but this is where I get the Duplicate Row Error and am unable to save any additional changes to the sales order.
I think my issue may be happening due to binding… should the tt fields be empty? Or should I be using ds fields instead?
The sysrowid might not work as you may have multiple updates on the same record you are monitoring.
try the SysRevID.
What is your query for the fill table? You can hardcode a few to test. Then once you know the creation is working we can focus on getting the real data filling in.
Variable is now “NewUD08” and set to UD08Tableset.
Both GetaNew and Update reference the NewUD08 variable.
Fill widget is set with the query and mapping in my previous screenshots.
