Jump to content

Question

I have an problem that I cannot figure out how to solve.  It's a bit complicated, but I think it may be unsolvable within Caspio.   Anyone who can solve this gets a gold star because I have tried everything.

 

I have a table of customers  example (C1, C2, C3, ... Cn ) and a table of vendors (V1, V2, V3...  Vn).

 

Every customer can have deals from multiple vendors e.g.(   Customer C1 can have deals from vendors V1, V3, V4, C2 can have deals with V2, V4, V5)  etc.

 

Vendors can have different deals with one or more customers, but only a single deal with a given customer.

 

This yields a "deal table" view consisting of combos of vendors and customers IDs forming records that have all the instances of vendors for each customer e.g. (V1/C2,  V1/C3, V2/C2)

 

 

OK-   Here's the problem.     I want to create a table that shows "All customers I don't have a deal with" from the perspective of a vendor.      I do a login which passes vendor ID, and I can only view records that are not equal to that vendor ID, but that will still show all other vendors that have deals with that customer.

 

I want to basically eliminate a customer entirely from the view if that customer ID shows up in the vendor's deal table.    I need to pass all the customer IDs in the vendor deal table somehow into the combined view table and do a big "not equal" to any of those customer IDs.    This doesn't seem to be possible, or maybe requires javascript.  The rules don't allow you to eliminate individual records based on the content of another table.

 

Thanks in advance.   Like I said,  gold star if you figure this out.

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

You need to have a third table which keeps VID and CID and there has to be another field (CombinedVCID) to keep the combination of these two. Since there can be only one matching this field should be unique.

 

Then in your report include CombinedVCID and filter your report with this field to find Does not Contain and receive VID from authentication.

Share this post


Link to post
Share on other sites
  • 0

I have created screenshots prototype.

 

Please let me know if it shows your requirements.

 

Vendor1 record is  shown where there is deal with Customer.(V1=>C3)

Vendor1 records are shown where there is no deal with Customers.(V1=>C1 and V1=>C2)

Vendor1 has deal with Customer3.

 

3 tables are required for the above requirement.

 

Thanks,

Neal

NealPatil @ gmx.com

 

CustomersVendors.png

Vendor1Deal.png

Vendor1NoDeal.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...