Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Filtered VL problem


Recommended Posts

I have a simple Customers--Invoice--InvoiceLineItems--Products structure that works fine.

I have also a Customers--Returns--ReturnLineItems to account for returns by customers.

So the structure is linear:

(Products linked to InvoiceLI linked to Invoices linked to Customers linked to Returns linked to ReturnLI)


The problem I am running into is the value list to show products to be returned to populate ProdID in ReturnLineItems table. When the VL is based on Products table, it shows all products in the table. I tried to filter it to show only products purchased by customer. So I changed the VL to include only related values from Customers to get it to show only the products purchased by the customer, it is totally blank. How do I filter the VL to show only products purchased by customer?

Please help


Link to comment
Share on other sites

You have a separate table for Returns as opposed to Invoices?


I think I would have them all in the same table with a field, call it "Type", that would have a value of either "Invoice" or "Return".


Be that as it may...


For a given Return, on any given line (i.e., related ReturnLineItems record), would you want the value list for Products to draw from all the products that this customer has everpurchased, or is a given return linked to a specific invoice and you'd want only the products from that invoice to pop up as possible products on the return? I'm assuming the first of the two options as I think about it, since when a customer returns stuff it isn't always stuff purchased at the same time / same invoice, yes?


OK, Invoices are invoices of a customer, so they should have customer ID already. Invoice Line Items are only indirectly of a customer, but you can have a lookup field, Customer ID, so that Invoice Line Items are directly of a Customer as well. The Product ID field on that Invoice Line Item is now distinguishable from the zillions of other Product ID records insofar as it is now affiliated with the customer.


So a Return is also of a customer and has a Customer ID, yes? Make new relationship from Customer ID in Returns to Customer ID in Invoice Line Items. (Say goodbye to your nice linear rel graph and welcome to the world of fully functional but wildly tangled relationships. It's OK, they may look like a snarly mess but they work fine. You should see mine). You'll be prompted to name a new Table Occurrence, let's call it CustomerILI.


Now a Value List defined as Product ID, related values only, in CustomerILI from starting point of Returns.


That should work.

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use