topic Link Customer to Pricelist in App Development

Link Customer to Pricelist

johngouws — Sat, 26 Apr 2025 03:22:53 GMT

Good morning.
I'm sitting with a scenario where I need to show the Price of Items per Customer, based on their Pricelist number. There are 5 pricelists available, numbers 1 to 5. On the Item side there are 5 pricelists fields, PL1 to PL5. There is no common field between the tables for association. The attached snippet shows it better.

The fields in the two tables are.

Item:
SQL SELECT
ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM Items;

Customer:
SQL SELECT
CustomerCode,
"Price List"
FROM Customer;

I really appreciate any thoughts on how this can be done and if it actually even possible. I include a simplefied version of the model and spreadsheet. In reality there are about 14000 Customers and 9000 Items

Thank you in advance.

Re: Link Customer to Pricelist

rwunderlich — Sat, 26 Apr 2025 17:18:38 GMT

Thanks for providing such complete example files, it makes it easy to answer.

You just need to CrossTable Load your Item table.

Items:
CrossTable([Price List], Price, 1)
;
SQL SELECT
ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM Items;

Then you will need to make the 'PL1' code align with the "1" code in Customer."Price List". In the SQL something like:

Concat('PL',"Price List") as "Price List"

Here's my solution in your QVW script:

Items:
CrossTable([Price List], Price, 1)
LOAD ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM
[CustomerPricelist.xlsx]
(ooxml, embedded labels, table is Item);

Customers:
LOAD CustomerCode,
'PL' & [Price List] as [Price List]
FROM
[CustomerPricelist.xlsx]
(ooxml, embedded labels, table is Customer);

-Rob

Re: Link Customer to Pricelist

johngouws — Sun, 27 Apr 2025 05:44:53 GMT

Thank you very much Rob. I've been struggling but cross table did not come to mind..
Your solution works perfectly.
In the live environment I went for doing the concat under the LOAD rather than the Pervasive Sql. The performance is better.

Thanks, John