Weighing options for a pivot table of two groups with an updateable BAQ. Can this be done?

Epicor ERP 10
1

Here’s a hypothetical that will explain my current project.

My client has ‘candies’… they can be of different ‘shapes’ and ‘flavors’. (ok, enuf with the 's)

I’d like to create a lookup table for each. Groups seems like the best option. So I’ll create a Group Shapes and a Group Flavors. Users can add shapes and flavors as they please.

Next up, I’d like them to get an updateable BAQ dashboard, that will run the Shapes vertically and the Flavors horizontally, with checkboxes as there vertices. If a user checks one, it would update to a UD0# table. Then as users add new shapes, they can go into the dashboard and select what flavors that shape is available in. Also, if a new flavor was added, then the current shapes could be chosen for that flavor.

From there, dropdowns would be populated with the flavors, based on what shape was selected.

I’m advanced in SQL, thinking I could probably do this with a pivot view on the two groups exposed as an external BAQ.

Am I crazy thinking this would work? Is there an easier way to accommodate this? In the Epicor world, this could be PartNum-UOM or something similar.

2

I’m not sure what you are asking, but you cannot do Dynamic SQL for the Pivot Columns if that’s what you are asking :joy:

If they are tracking lots then you can store a lot of the part attribute information in the PartLot table and query on that.

1 Like
3

Here’s a mockup I made in Excel to show what I’m trying to do.

Hoping to make an updateable dashboard that looks something like this:

image

Then when the user select PartNum 1, populate a dropdown with Red, Green, and Blue only.

Adding a new PartNum would be simple in this example, but trying to figure out how to add a new Color that would then be an option for all part nums.

Thinking I could do this with UD100A or something similar, but I’d still need to have to code in each new color. Such as Checkbox05 = Black.

I’m currently setting the dropdown values in code and need to make changes everytime they add a new partNum or color, or if they decide to offer a certain partnum in a previously unoffered color. I’d like to build them something that they can manage this on their own.

Thoughts?