Hi @SerPounce
I thought it might be a good idea to do what I am trying to achieve in Excel/SQL as it might help to explain the logic. I did it in SQL (despite not knowing much about SQL but being a very experienced Excel monkey!). I found this website:
which lets you write and test basic code. There were three types of SQL and as I don’t know what the difference between them is I just selected the first one: Oracle 11g. On the left side of the screen I put the following code:
CREATE TABLE defaultview
(
dvID VARCHAR2(4000 BYTE),
priority VARCHAR2(200 BYTE),
vendor VARCHAR2(200 BYTE)
);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘1’, ‘1’, ‘ebay’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘2’, ‘2’, ‘footlocker’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘3’, ‘3’, ‘nike’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘4’, ‘4’, ‘footasylum’);
CREATE TABLE users
(
ID VARCHAR2(4000 BYTE),
email VARCHAR2(200 BYTE),
favouritestore VARCHAR2(200 BYTE)
);
INSERT INTO users (ID, email, favouritestore) VALUES (‘1’, ‘1j@a.com’, ‘ebay’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘2’, ‘2j@a.com’, ‘footlocker’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘3’, ‘3j@a.com’, ‘nike’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘4’, ‘4j@a.com’, ‘footasylum’);
CREATE TABLE allshoes
(
ID VARCHAR2(4000 BYTE),
description VARCHAR2(200 BYTE),
price VARCHAR2(200 BYTE) ,
product VARCHAR2(200 BYTE) ,
vendorid VARCHAR2(200 BYTE) ,
uniqueid VARCHAR2(200 BYTE) ,
vendor VARCHAR2(200 BYTE) ,
sole VARCHAR2(200 BYTE)
);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘1’, ‘this is the nike description air force 1’, ‘90’, ‘air force 1’, ‘JkTGzADv’, ‘aaa111’, ‘nike’, ‘no’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘2’, ‘this is the footlocker description ultrabost’, ‘169.99’, ‘ultraboost’, ‘FX1334’, ‘bbb222’, ‘footlocker’, ‘yes’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘3’, ‘this is the footlocker description air force 1’, ‘90’, ‘air force 1’, ‘23498067’, ‘aaa111’, ‘footlocker’, ‘no’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘4’, ‘this is the footasylum description airforce 1’, ‘90’, ‘air force 1’, ‘008939’, ‘aaa111’, ‘footasylum’, ‘no’);
This pretty much replicates the data I have in my bubble app that I am trying to work with. It took a few hours of messing around and googling before I got it to work (the error messages aren’t very informative!) but eventually I arrived at the code below which seems to do absolutely everything that I am trying to achieve in Bubble (please note that the reference to the arbitrary value vendor=eBay is entered as a test. In Bubble it would be CurrentUser’sFavouriteStore’sValue)
with temptable as
(
select allshoes.product, allshoes.uniqueid, allshoes.vendor, allshoes.vendorid, Row_Number() Over (Partition By uniqueid Order By priority Desc) RN
from allshoes
left join defaultview
on allshoes.vendor=defaultview.vendor
where allshoes.uniqueid not in
(Select allshoes.uniqueid from allshoes where allshoes.sole=‘yes’ or allshoes.vendor=‘ebay’)
)
select product, uniqueid, vendor, vendorid
from temptable
where RN=1
union (Select product, uniqueid, vendor, vendorid from allshoes where allshoes.sole=‘yes’ or allshoes.vendor=‘ebay’)
What worries me is that bubble is supposed to be a simplistic way of doing things, but I’ve spent way longer (weeks) trying (and failing) to achieve what I’ve been able to do in a coding language I’m not familiar with in the space of a few of hours. I’m hoping the above code provides the logic and a clue for anyone who might be able to help replicate in Bubble. TIA
