Learned this today - BAQs and sites

For the multi-site people out there.

I made a BAQ to show transfer orders. I was hopping mad when site A could not see site B stuff.

I learned that if you use the PartPlant, PartRev or Warehse tables in your BAQ (and inner join), the BAQ is restricted to only the sites that the user has access to.

This is documented in a table called zDataTable - note the RestrictedBySite column:

(I recall others hitting this with territories. Notice the column to the left of the site column.)

Credit to Aeron at Epicor Support for showing me this today.

But there is hope! If you left-join on those tables, you’re OK.

So this is what failed:
image

But this works:

For this, PartPlant is filtered to site A. Site B people can’t see the detail from site A, like the buyer (yeah I don’t know why they wanted that either), but that’s OK for me. It at least works in both sites. It’s just that one site doesn’t get all the detailed info. But they get all the rows, which is what matters.

7 Likes

That explains why when I SQL Trace I see the Territories stuff all the time in the trace.

1 Like

Drives me nuts!

Yeah I’m only two months into live multi-site and I’m frustrated.

Three random tables are restricted. Not PartQty or PartWhse or PartCost, thankfully, so I can work around cost and quantity. But why not just remove the restriction on all of them? We do BAQs and menu security! We know how to block info! But I can’t unblock what the system restricts.

You can. Give all users access to all plants. :man_shrugging:

You know how to block info. :wink:

@Mark_Wonsil

Walked right into that one, didn’t I?

It’s really about transactions.

I’m happy to block info. Really, 80-90% of what our users use to see info is menu items (dashboards) that I created anyway. I hardly block info so much as just repackage it.

But I don’t want to have to make endless BPMs to block this transaction, but not that one. Giving access to plants means BPMs.

Still not sure what the business case is, i.e. why you need to block one thing and not another and when but…

Referring to a post about Code Widgets this week, in a BPM you can write your own query in LINQ in a Code Widget and fill a dataset without any restrictions for territory, tenant, or plant security. That dataset can be use later in the BPM.

I don’t know where you are using this so this may or may not help. But if you’re willing to manage security manually and you’re not updating anything, it’s a possible solution.

2 Likes

Yeah, in the end you’re right, the best control is trusting people.

Our people are excellent. They are smart and try to do what’s best for the system. Problem is, the system that they came from, they had to switch sites all the time to see inventory in another site, for example. I’m trying to break them of this habit and let them stay in one site all day every day. I sure try to myself. It’s a good thing each site has wildly different conventions for bin names, for example - that’s saved me a few times. And POs can’t be received in the wrong site. But if you made the PO in the wrong site…

1 Like

That is pretty fascinating. Good to know.

@Mark_Wonsil. Could you please share how to create the widget and the query in LINQ, please?