I am creating a BPM where one of my conditions call for a Query. My query is relating from ttPORel to TranGLC. Criteria is RelatedToFile = “PORel”
The validation fails my table relations:
But when I build the same exact query in BAQ designer. It works fine. Is this a bug or what am i doing wrong?
I feel your pain. The BPM BAQ designer isn’t quite the same as the normal baq designer, and here’s one of the small differences you’re experiencing. You’ll have to cast/convert one of the fields to that type (either int or string) in that relationship to get it to work I think.
Try making a subquery for TranGLC, so that the Key fields can be referenced in the data type you’re expecting.
How do you make a subquery in the BPM BAQ designer?
You can’t unfortunately. You would need custom code to do something similar.
I do not think it is possible as you cannot cast the fields like you can in a regular BAQ
Something like this should work:
Add the var tempTable as shown above
Fill By Query widget
Just the ttTable you want to relate to TranGLC, with all fields, no criteria.
On the configured mapping, use the Bind Automatically, and then change three fields to expressions:
In the condition’s query, relate like:
Calvin,
While having trouble with my other thread Method Directive to prevent save if conditions are met - #20 by ckrusen, I stumbled onto this thread.
I tried to fill table by Query, but it seems that the ttPORel has no data (when state changes from unconfirmed to confirmed; the save has already occurred when the approved checkbox went to true and I assume this clears the temporary table). I realized this when I set messages that would show me the values for the OTSAddress1, 2, and 3 and they were all null. Then I tried three Set Variable widgets (for PONum, POLine, PORelNum) and got this:
I think I am really close to solving my issue. I am trying to fire a message when anyone tries to confirm the PO, when any release on that PO is Buy to Other (PUR-UKN) and the TranGLC.SegValue1 for that release is “999999” or “120000” or “120100” or “120200”.
Yeah, that was really about how to link tables with different types in a BPM query.
Since the POHead is the only table that’s changing, ttPOHead is the only one that will have data. If you only care if any release has a bad GL acct, then just link ttPOHead to TranGLC on PONum. No need to specify POLine or PORelNum (Key2 or Key3).
Make a var of type string, and set it by casting PONum. Then use that as a table criteria in the BPM condition query.
Calvin,
The trouble is the segments that start with 12 are valid but not if the release is Buy to Other (and the Buy For field is set on the Line and Release level, but not on the Header level; the only level that has tt data populated). So if a PO has multiple lines with some Buy to Other and some as Buy to Inventory, then I would need to find the releases that are Buy to Other and see if only those releases have the incorrect GL account. It feels like I am getting tangled in a spider’s web.
Use the technique of adding a dataset variable and the Fill By Query to fill the dataset with data from the PORel DB table instead of the ttPORel. Just have a single table criteria in the Fill By widget’s query of
PORel.PONum = ttPOHead.PONum
I think the following will do exactly what you want
Condition: “Conf: F->T” checks for Confirmed flag changed
widget: “Set intPONum” sets the variable intPO (so that we don’t link a ttTable later). The expression is:
ttPOHeaderRow.PONum
Fill by Query: “Get Relevant Rels” populates variable tempPORel with the PORel records for this PO.
“Fill tempPO Qry”:
(All Display Fields selected)
Configured mapping:
Condition: “Check Releases”
TranGLC qry (tables and relationships):
TranGLC qry (table criteria)::
Calvin,
Wow, thank you for your solution. I am getting the following compilation error:
Mine is set as Ignore
After you changed it to ignore, saved and then closed the BPM Workflow window … did you remember to then save the BPM? (Changes to a BPM workflow won’t take effect until the BPM itself is saved).
If it still doesn’t work (using ignore), try setting it with an expression to "U", like:
.
But it really should work with Ignore. If it still gives you trouble, use the Clear bindings button to clear them all and only set the Company and the three OTS fields - as that’s all that is used in the rest of the BPM.
It might be because you are SAAS. Other fields in the tempPO dataset might be problematic too. That’s why I suggested just updating the minimal fields to be used.
Calvin,
You are right again. I think it had something to do with my saves (I also closed method directive maintenance and then restarted it). Now it compiles and seems to work. Instead of Raise exception, I chose to set the Confirmed field to False, then show a message (because then the UI refreshes to show the current state; unconfirmed).
Brian
For extra credit, you could add another PO dataset variable and populate it with only the offending records. Then you could display that to the user, so they could jump right to the line/release that needs updating.
I know you said you didn’t want to prevent the approval on bad GL acct, but you could add a row rule to a customization to highlight bad accounts. It wouldn’t keep the user from proceeding, but if they know the right account now, they could save them selves having to go back later to fix it.
We have begun testing on Kinetic2021.1 (as our PILOT database was upgraded over the weekend). My method directive failed to compile and was listed as Outdated. It seems the PORel table no longer has a column IsContiguous. I had to recreate the widget “Fill Table by Query” (to completely remove the reference to IsContiguous) and now the “Workflow validation succeeded”. However, when I try to Enable the BPM, I get the following error:
Can anyone provide me with helpful tips on how to resolve this error?
