Stop a Process Based on a BAQDataView

In customer shipment entry, is it possible to stop a user from putting a pack id into a closed status based on data in a BAQDataView?

For example, if there are any rows in the BAQDataView, when the user clicks on Actions and selects Close, it will throw an exception and the user cannot proceed. If there are 0 rows the user can close the pack id and process the shipment.

Is there a reason you’d want it to be based on the BAQ DataView? The reason I ask, is that the same BAQ that you’re showing in the BAQDV can essentially be written into a BPM that you could run when the ShipStatus changed to “CLOSED”. Then you could raise an exception with a message of you’re choosing. I would shy away from a customization if possible just for upgrades sake.

You should be able to create a row rule that would be able to enable/disable the action. But like @dr_dan said a BPM would stop this kind of action from happening at the BO level.

It doesn’t have to be on the dataview, I’m all about a better way if there is one. I just don’t know how to go about doing that via a BPM.

This is what I would try. I’d do an In-Transaction Data Directive on ShipHead table. Set a condition widget that checks for the field ShipStatus to change from “OPEN” to “CLOSED”. On the true side, either use another condition widget with a BAQ results condition or you can set an integer variable like “BAQRows” based on a LINQ query that would replicate what your BAQ is doing and then use a condition block to evaluate if teh variable > 0 to raise that exception.

If you really wanted to get fancy, you could still reference your existing BAQ in an execute custom code block so that way if you ever update your logic in the BAQ, it will update in both the BAQDV and the logic of the BPM.

I’m not sure where your comfort level is with writing BPMs, so I can give you more specific info if you need.

I’ll give your first suggestion a try. I don’t do a lot of BPMs, but I’m certainly willing to dig in and try!

I love getting fancy, but I know enough code to be dangerous so I steer clear of that route when I can.

Or if the BAQ is basic, create the query as part of the conditional statement.

1 Like

It’s not a basic BAQ, there’s a calculated field and an outer join

1 Like

That shouldn’t be a problem. You only care about if there is a record returned or not.

1 Like

I tried to use the condition ‘number of rows in the designed query’ but since I can’t change the join or use my calculated field i couldn’t go that route.

Mind sharing the BAQ query?

Nothing too fancy

select
[ShipDtl].[PackNum] as [ShipDtl_PackNum],
[ShipDtl].[PartNum] as [ShipDtl_PartNum],
[ShipDtl].[OurInventoryShipQty] as [ShipDtl_OurInventoryShipQty],
((case when (PartBin.OnhandQty) is null then 0 else PartBin.OnhandQty end)) as [Calculated_OnHand]
from Erp.ShipDtl as ShipDtl
left outer join Erp.PartBin as PartBin on
ShipDtl.Company = PartBin.Company
and ShipDtl.PartNum = PartBin.PartNum
and ShipDtl.WarehouseCode = PartBin.WarehouseCode
and ShipDtl.BinNum = PartBin.BinNum
where (ShipDtl.OurInventoryShipQty > (case when (PartBin.OnhandQty) is null then 0 else PartBin.OnhandQty end))

Great.

You have a packid. What is the rule to make it STOP from being set to closed?

Something to do with OnHandQty. Just want to make sure.

If the there are any rows in the data view it needs to stop

But with a left join there will always be rows.

Not talking query talk. What is the condition you are looking for?

if the calculated on hand field is less than the inventory ship qty stop the user from closing the pack id.

I have the BAQ query set to only show rows on the shipment that we do not have enough inventory to ship. If there is enough inventory for all the lines on the shipment, the dataview doesn’t have any rows.

So something like this could work.
This is just pseudo code

No need to use the ShipDtl table as that is the tt.

Select
(PartBin.OnhandQty) as OnHand
from Erp.PartBin as PartBin on
ttShipDtl.Company = PartBin.Company
and ttShipDtl.PartNum = PartBin.PartNum
and ttShipDtl.WarehouseCode = PartBin.WarehouseCode
and ttShipDtl.BinNum = PartBin.BinNum

where (ttShipDtl.OurInventoryShipQty < PartBin.OnhandQty

So results could be:
If ttShipDtl.OurInventoryShipQty is 10 and partbin is 20 a record will return
If ttShipDtl.OurInventoryShipQty is 10 and partbin is 5 then nothing will return
If ttShipDtl.OurInventoryShipQty is 10 and partbin is null then nothing will return

If query results is greater than zero, stop user from continuing.

1 Like

thank you!! I’ll give this a try!!