Thats right, if you can do it on user email (email whitelist) that would be great as I will only permit access to current clients.
I am not sure if this would work. I would need to investigate it further.
Well, I’m using a typical database normalization when you have several features associated to the same item but I know what you mean (I hope): you want to save rows, right?
The amount of data, its structure and how you will handle it in the future are things you have to consider on this stage.
@gvalero, yes given unlimited rows I completely support your method above, but if that means 500 products times 20 vendors that is a lot of extra rows.
@Brad_Legassick if rows are of no concern I would echo @gvalero’s suggestion, but if you are trying to keep the row count low, my next comment may give a solution (not a clean one per say, but hopefully a functional one).
So what I did, is not… the cleanest solution, so it’ll take some data prep to get ready, but should be functional, and again this may not be the most elegant solution, but it may be a good choice if you don’t want to use so many rows.
So on the user sheet which should have row owners turned on add a column which will hold their vendor/supplier of choice/assignment. Each one of these price “groups” will prefereably have a letter only unique code, which will be put here for each user (see below for why)
So as above Product sheet has:
- first column is the item list
- a column for each price “group” (supplier vendor combo or whatever)
- And this is where things get… odd
- so in each of these columns you will need a string which contains both a “price group” code (letters ONLY) and the price in numbers (no currency symbol). So for instance all of column Sup1 V1 will have the price for the item in that row and the code SAVA for Supplier A vendor A
- the next column to add is the “big” if/else column which will decide which price the user should see. So this column will basically look for the user chosen/assigned price group in all the columns (which is why you need the code AND the price in each cell (i told you this was not elegant ).
- this will look like
- IF Supplier1V1 Contains UserSelectedSupplierCode then Supplier1V1
- IF Supplier1V2 Contains UserSelectedSupplierCode then Supplier1V2
- on and on for every price group column
- the output will be for instance 2.34 SAVC (2.34 price from Supplier A Vendor C combo)
- this will look like
- the last column is a math column which is configured to simply show the value in the previous if/else column and because its a math column it will strip the previous column values of all text and return the final price list for the user.
Please let me know if that makes any sense and if it will scale. I have built and tested it so it is functional, it’s just a matter of how much setup you want to do I suppose.