jarrodprice
asked on
Filtered list using calculations from both sides of a table relationship
In my wine database each wine record is connected to its related varietal, country, pricepoint, classification, and supplier tables. Each varietal, supplier etc, has a unique code. Each wine has a code that concatenates the codes from each of its five related table.
Each contact in my database can have a selection of prefered wines, or types of wines. The preferences are tracked as line items related to that contactid.
Each line item can have 1 to five elements (varietal, country etc) concatenated from its related tables like the wine records.
The preferences are related to the wines through each respective concatenated code.
The problem is this, If 1 preference is italian red wines under 20, the portal should show only wines that match all 3 criteria, even though the preference code will only partially match the wine code, which also includes a supplier and varietal component.
The second issue is that the the related wine list should show italian reds under 20, NOT all italian wines, all red wines, and all wines under 20.
This list can have multiple preferences, which means, that a second preference could include all Shiraz wines over $100. I know I would need to come up with some calculation on 1 or both sides of the relationship to filter the wine list properly, but I am not sure what it should be.
Each contact in my database can have a selection of prefered wines, or types of wines. The preferences are tracked as line items related to that contactid.
Each line item can have 1 to five elements (varietal, country etc) concatenated from its related tables like the wine records.
The preferences are related to the wines through each respective concatenated code.
The problem is this, If 1 preference is italian red wines under 20, the portal should show only wines that match all 3 criteria, even though the preference code will only partially match the wine code, which also includes a supplier and varietal component.
The second issue is that the the related wine list should show italian reds under 20, NOT all italian wines, all red wines, and all wines under 20.
This list can have multiple preferences, which means, that a second preference could include all Shiraz wines over $100. I know I would need to come up with some calculation on 1 or both sides of the relationship to filter the wine list properly, but I am not sure what it should be.
Member_2_908359
looks too complex to handle this with links. I would use the preference strings to generate a serie of "or" searches criteria in a script executed in the wines table...
ASKER
That would be a problem, since the list wines that is generated from the code match between the the preferences table and wine table needs to be populated without having to run a search. For example, if I were in a record for a specific wine, I would see a list of every contact that has this particular wine related through the preference code.
The essence of the issue is this:
A record in the preference table with this code:
CTRY04
PRPT02
should match with these records in the wine table:
Wine #1
CTRY04
VRTL01
CLAS05
PRPT02
SUPP01
Wine #2
CTRY04
VRTL10
CLAS20
PRPT02
SUPP80
but not this wine:
Wine #3
CTRY04
VRTL03
CLAS05
PRPT20
SUPP80
All three wines match CTRY04, but wine 3 does not match PRPT02, so only wine 1 and 2 show in the related table.
The essence of the issue is this:
A record in the preference table with this code:
CTRY04
PRPT02
should match with these records in the wine table:
Wine #1
CTRY04
VRTL01
CLAS05
PRPT02
SUPP01
Wine #2
CTRY04
VRTL10
CLAS20
PRPT02
SUPP80
but not this wine:
Wine #3
CTRY04
VRTL03
CLAS05
PRPT20
SUPP80
All three wines match CTRY04, but wine 3 does not match PRPT02, so only wine 1 and 2 show in the related table.
use separate fields for each code, and a relationship based on 5 fields maybe?
ASKER
I will try this. If I create a calculation for each code that sets it to "-" if blank or the regular code if not, and then on the wine side, I have a calculation for each code that is
code
-
then the blank fields will match the "-" part of the code on the wine side, leaving the non-blank fields on the preference side to either match or not match the code on the wine side.
You are right. This should work.
I will get back as soon as I have a chance to complete the code and test this.
code
-
then the blank fields will match the "-" part of the code on the wine side, leaving the non-blank fields on the preference side to either match or not match the code on the wine side.
You are right. This should work.
I will get back as soon as I have a chance to complete the code and test this.
why use "-", empty should be enough?
ASKER
By default, if a code is blank in the preference table, it should be treated as a match with its associated wine code field, so that the relation is solely based on the non-blank fields.
For example, if the country code field is empty in the preference table, it will not match the populated country code field in the wine table, and thus eliminate that relationship even if every other related code matches. I want the match to only be made on nonblank fields.
For example, if the country code field is empty in the preference table, it will not match the populated country code field in the wine table, and thus eliminate that relationship even if every other related code matches. I want the match to only be made on nonblank fields.
Perhaps lesouef can come up with a more elegant way to do this but here is one thought. On the contact side, create 5 fields. Have them autoenter a value ( I will use numbers 1-5, 1 for field one, 2 for field two, etc) and use these fields to enter the criteria that the client prefers, one code per field. If a client only has two criteria, then only populate 2 fields and leave the numbers in the other 3.
On the wine side, in addition to the codes that are already creating the multi-key value, include the numbers 1 to 5 each on its own line. You will end up with something like this:
Wine Side:
CTRY04
VRTL01
CLAS05
PRPT02
SUPP01
1
2
3
4
5
Then create a TOG that relates the Wine table multikey to the Contact table on all 5 of the preference fields with a mutipredicate relationship. ie, WineKey to Pref1 AND WineKey to Pref2 AND WineKey to Pref3 etc.
Then create the value list based on the values in this relationship. If you have a matching wine code it will match. If you leave the number in the field it will also match but if you have a different value in the field than what is in the WineKey it will not match.
On the wine side, in addition to the codes that are already creating the multi-key value, include the numbers 1 to 5 each on its own line. You will end up with something like this:
Wine Side:
CTRY04
VRTL01
CLAS05
PRPT02
SUPP01
1
2
3
4
5
Then create a TOG that relates the Wine table multikey to the Contact table on all 5 of the preference fields with a mutipredicate relationship. ie, WineKey to Pref1 AND WineKey to Pref2 AND WineKey to Pref3 etc.
Then create the value list based on the values in this relationship. If you have a matching wine code it will match. If you leave the number in the field it will also match but if you have a different value in the field than what is in the WineKey it will not match.
I thought I had understood the pb, but now I am not so sure... Don't understand at 1st glance...
I'll give it a try later on maybe if jprice does not succeed in using jj's way. I personnaly need to write the solution to understand it, my brain is too old to only imagine the way it will work!
I'll give it a try later on maybe if jprice does not succeed in using jj's way. I personnaly need to write the solution to understand it, my brain is too old to only imagine the way it will work!
ASKER
If I had to guess, jj's idea is similar to mine. If the ctrycode field in the preference table is empty, it returns a 1. This matches the 1 in the winecode. In this case, if I were to create a preference for a contact that is empty in all five fields, they would return 1, 2, 3, 4, and 5 respectively, thus matching every wine record.
This is what I am hoping to achieve. The rest of jj's solution I don't understand, but the effect as I described it will definitely achieve what I am after.
It may be a couple days before I build in this solution to my database and test it.
This is what I am hoping to achieve. The rest of jj's solution I don't understand, but the effect as I described it will definitely achieve what I am after.
It may be a couple days before I build in this solution to my database and test it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wound up figuring out my own solution, but it was based on your model, using keys and scripts to do it.