Portal Woes and other dilemas

[Limore]

So i have an inventory database and a customer wants table where you can specify what they are looking for. There is a list of 4 criteria (item, diameter, manufacturer and model) where the item always has a value while the other fields may be left blank. On the customer wants side, i have the portal filtering to match the criteria chosen and a refresh portal script trigger on the 4 fields. It seems to work fine. Here it is for reference:

Case ( Customer Wants::Diameter = ""  and Customer Wants::Manufacturer = ""  and Customer Wants::Model = ""  and Customer Wants::SC ≠ ""; Customer Wants::SC = Inventory::SC; Customer Wants::Diameter = ""  and Customer Wants::Manufacturer  ≠  ""  and Customer Wants::Model  ≠  ""  and Customer Wants::SC ≠ ""; Customer Wants::Model = Inventory::Part Number  and Customer Wants::Manufacturer = Inventory::Manufacturer  and Customer Wants::SC = Inventory::SC; Customer Wants::Model = ""  and Customer Wants::Diameter  ≠  ""  and Customer Wants::Manufacturer  ≠  ""  and Customer Wants::SC ≠ ""; Customer Wants::Diameter = Inventory::Diameter  and Customer Wants::Manufacturer = Inventory::Manufacturer  and Customer Wants::SC = Inventory::SC; Customer Wants::Manufacturer = ""  and Customer Wants::Diameter ≠ ""  and Customer Wants::Model ≠ ""  and Customer Wants::SC ≠ ""; Customer Wants::Diameter = Inventory::Diameter  and Customer Wants::Model = Inventory::Part Number  and Customer Wants::SC = Inventory::SC; Customer Wants::Diameter = ""  and Customer Wants::Model = ""  and Customer Wants::Manufacturer ≠ ""  and Customer Wants::SC ≠ ""; Customer Wants::Manufacturer = Inventory::Manufacturer  and Customer Wants::SC = Inventory::SC; Customer Wants::Diameter = ""  and   Customer Wants::Manufacturer = ""  and Customer Wants::Model ≠ ""  and Customer Wants::SC ≠ ""; Customer Wants::Model = Inventory::Part Number  and Customer Wants::SC = Inventory::SC; Customer Wants::Model = ""  and Customer Wants::Manufacturer = ""  and Customer Wants::Diameter ≠ ""  and Customer Wants::SC ≠ ""; Customer Wants::Diameter = Inventory::Diameter  and Customer Wants::SC = Inventory::SC; Customer Wants::Model  ≠  ""  and Customer Wants::Manufacturer  ≠  ""  and Customer Wants::Diameter ≠ ""  and Customer Wants::SC ≠ ""; Customer Wants::Model = Inventory::Part Number  and Customer Wants::Manufacturer = Inventory::Manufacturer  and Customer Wants::Diameter = Inventory::Diameter  and Customer Wants::SC = Inventory::SC; ""  )

 

seems a little convoluted.

 

The issue becomes when i want to put a portal on the inventory item and list customers interested in that item. Basically, if everything but the item is blank it should put the customer name in the portal of every instance of that item in the inventory layout and if other criteria are selected, it should only list the customer if they match all the criteria selected in the wants table. The filtering produces some unexpected results, namely that doing a search for customer inside the portal is inaccurate.

 

I feel like the solution is staring me in the face and sticking it's tongue out at me and blowing a raspberry, but for the life of me I am stymied.

 

Thanks for playing. and whatever you do, don't try and parse that ugly filter portal script. blech.

LImore

[Steve Martino]

Without even dissecting the portal filter I know it's not the way to go, but I just don't really understand what you are trying to do.

Is Customer Wants a join table between Customers & Products (Inventory)?

What TO is the layout based?

What TO is the portal based?

How is the customer specifying and selecting an item?  When they do, what is suppose to happen?

I would think a portal on the customer TO of Customer Wants (a join table between Customers & Inventory, would list items selected by that customer only-assuming the proper primary key/foreign key relationship, or in this case 2 foreign keys on the join table.

Then a sub-summary report based on Customer Wants could show a list of products broken down and counted by customer.

However, if you search in a Customer Wants portal for a customer, you will get a customer TO based record for every customer that has that one specific item, among other items in their related portal, which is probably where you are getting your 'unexpected results'.  The search is not inaccurate, it's doing exactly what it's supposed to do.

[Limore]

so there is an inventory TO and a customer wants TO. On the customer wants TO, a person can choose a customer and then pick from 4 drop down lists to select the item, diameter, manufacturer and model and a portal filters the inventory that is available for that criteria. I am using the crazy portal filter there with script triggers on the drop downs to refresh the portal.

I am including two screen shots.

 

the portal i am interested (well both) in is the one on the inventory page that will show all customers that are interested in a particular item. This would be pertinent to do a mass mailing to all who are interested.

with the current portal doing the wacky filtering, i am afraid it would not produce the right results for a mass email out.

 

what i would truly like to do is have a relationship where an empty field (ie diameter) on customer wants can match to ANY (diameter) in the inventory TO. Item or SC or sort codes will always be selected but the other three fields could remain blank.

 

Limore

[bcooney]

Rather than a portal filter, you could use global fields and then do a Find on global field modify (or a Search button). If you find records, put their ids in a global and the portal is the relationship from that global multi key to the items table.