We have customers with a special price list which will sit at sequence 1 on the price list hierarchy and then the RRP price list which will sit underneath. In a BAQ i want to be able to export their exact price for each part number. So if the part number is on both price lists, only the one higher up in the hierarchy will show.
How can this be achieved with a BAQ ? Or is there any other way I can download an exact price list for the customer.
e.g. Price list X at sequence 1 Partno = £1.99
Price List RRP at sequence 2 Partno = £4.99
Find out which price list the price should be pulled for the given part/customer combination
Use the sequence number from step 1 above to pull the actual price
I’d setup a subquery that joins CustomerPriceLst with PriceLstParts inner joined on company and list code. In the display fields, display (and group by) Company, CustNum, and PartNum. Also add a calculated field to show the minimum CustomerPriceLst.SeqNum.
The output of the above query will tell you which customer price list to use for each permutation of Customer/Part.
Set the above query as an inner subquery and use it as the link between CustomerPriceLst and PriceLstParts to display the price from the appropriate price list for each Customer/Part combination.
I get fairly good performance out of this method, as long as I am filtering by a specific customer. Our database has around 20,000 parts and 8,000 customers, so pulling all 160,000,000 rows would almost certainly timeout. If I filter down to a single customer (which is what we need in our use case anyways), I get my 20,000 row result set back in under a half a second.
Hi Jay, I have been struggling with this same issue. My results are not matching what I would expect yet. Would you be willing to share your BAQ for reference?
Sample BAQ attached, definitely do some validation against your data to make sure this works, as it is just a quick example and hasn’t been fully validated. It should get you started, though.
My use case included a couple of UD fields that you’ll have to remove in your environment, but none of them are critical to the logic, they are just for display purposes. I also have included some logic to show the EAN-13 or UPC-12 codes, if one or the other is present. The PartPC tables can be removed, if you don’t need those codes. The “Volume Pricing” field simply shows the minimum quantity break on parts with quantity-based pricing, to show the customer what volume they’d have to get up to in order to achieve a discount. All of this is very business-process dependent, so it might have to be tweaked in your environment.