When you look at the items listed in the portal, how can you tell which items should be filtered out?
The best method I can think of is to add a field to your join table that looks this information up from your products table when an item is added. Now you've got a field in your join table that can be used as part of a filtered relationship.
I've added the lookup field to the join table but i'm still a little confused as to what I'm going to connect it to. I want to filter the join table but in the previous filter mechanism I created the global field was in what would now be the join portal...so why did I create the lookup field? Could you point me towards the right direction?
Thanks so much,
You'll need a filter field in the parent record. If this is a "hardwired" filter (you don't want to be able to change the filtering), you can create a calculation field in the parent table that returns a value that matches your new looked up value field. Since you want to exclude matching records, use the ≠ operator in the relationship.
Let's say your portal relationship currently looks like this:
Invoices::InvoiceID = LineItems::InvoiceID
Change it to:
Invoices::InvoiceID = LineItems::InvoiceID AND
Invoices::NewCalc ≠ LineItems::NewLookup
If I do that then everything disappears from the lineitems portal on the Orders layout. Instead of a calc field I used a global. What could I be doing wrong?
What sort of look up did you set up in your line items table? Any chance that it is looking up the same value for all your line items instead of looking up a value just for the items to be filtered out?
No, it copies over the category for every item added to the order. I deliberately selected items with different categories to test the filter. Would I be able to send you the file? I'd post it but don't see any option to do so.
You can upload a file to a file sharing site and then post the URL to it here.
Or send me a private message and I'll send you an email address.
I don't find the filter field nor the modified relationship between Orders and LineItems to do this.
If you want to exclude all related records where "bin" is the value stored in LineItems::Lookup Bin,
Define a calculation field in Orders, cBinFilter set to return "Bin" as text.
Modify the relationship between Orders and LineItems to be:
LineItems::OrderID = Orders::OrderID AND
LineItems::Lookup Bin ≠ Orders::cBinFilter
Thank you thank you thank you! It works!
A single issue which I can work around is the fact that the the inventory::item name disappears from the lineitems portal after I create this new relationship. I can use a lookup for that as well but why is that?
Thanks so much, your help is greatly appreciated...really.