Complex requirement from users: Show customers interested in buying a product based on selected features? Two tables are not linked

jblomqvist — Tue, 02 Oct 2018 21:34:08 GMT

Hi,

I have a slightly complex requirement from the users.

We have some data about products that should be sold by a certain date.

We then have another table where we have customers interested in buying a product with certain characteristics.

The two tables are not linked in any way.

For example the Product table could have:

ProductName

ProductType

ProductGroup

Material 1

Material 2

SellingPrice

Country

etc

We then have Customers Interested table which has:

CustomerName

ProductInterestedIn

ProductGroupInterestedIn

Material1Interest

Material2Interest

MinimumBuyingPrice

MaximumBuyPrice

Country

etc

What users want is to be able identify customers interested buying a certain product based on the selections from the Products table.

Examples of use cases:

1) If a user selects one or more ProductType from Product table, it should show customers interested in buying those products based on matching values in the ProductInterestedIn table.

2) If a user selects a SellingPrice, it should show customers interested in buying products within the selected value using info from MinimumBuyingPrice and MaximumBuyingPrice.

3) If a user selects a Material value from Products table, it should show customers interested in those materials using values from Material1Interest or Material2Interest.

4) If a user selects a Country from Products table, show customers with matching values in the Country field from the Customers Interested table.

5) Users might select from more than one field in the Products table, so customers from Customers Interested table should be shown accordingly.

Note: I am pretty sure their requirements will change and they will start asking for selections to work the other way around as well, e.g. make some selections on field values from Customer Interested table and see relating values from appropriate fields in the Product table.

How is this even possible?

Please help.

Re: Complex requirement from users: Show customers interested in buying a product based on selected features? Two tables are not linked

andrey_krylov — Wed, 03 Oct 2018 06:55:27 GMT

Hi John, if mentioned fields have the same values

Material 1 - Material1Interest

Material 2 - Material2Interest

........................................

you can concatenate these tables

Load

table1:

ProductName,

ProductType,

ProductGroup,

Material 1,

Material 2,

SellingPrice,

SellingPrice as MinPrice,

SellingPrice as MaxPrice,

Country

From ...

Concatenate

Load

CustomerName,

ProductInterestedIn as [ProductType],

ProductGroupInterestedIn as [ProductGroup],

Material1Interest as [Material 1],

Material2Interest as [Material 2],

MinimumBuyingPrice as MinPrice,

MaximumBuyPrice as MaxPrice,

Country

From ...

and as for prices, using an IntervalMatch() create a separate table like this

MinPrice,

MaxPrice,

Price

topic Re: Complex requirement from users: Show customers interested in buying a product based on selected features? Two tables are not linked in App Development

Complex requirement from users: Show customers interested in buying a product based on selected features? Two tables are not linked

Re: Complex requirement from users: Show customers interested in buying a product based on selected features? Two tables are not linked