Read with exact filter - works on columns, can it also work on rows?

[Harn]

The Offers screen has the filtering

• the iscurrent variable draws on the data in column x
• the offerMadeToViewer draws from one of the columns H-O depending on which of these agencies is the current Viewer.
• I'm focussing on an intersection between the above 2, to go into list currentOfferToViewer.

Do get back to me if you need more info than this.

[ABG]

Another Screen1 bug, before I go to the Offers screen:

In Screen1 you issue a read range of sheet Offers, for range H1:O1
I assume the rangeData comes back as a list.
Your loop through the raangeData ends up with TinyDB tag offersUserList containing just the last item.
Each pass over the list replaces that tag/value with just the current item.

Maybe you wanted to just set that tag's value to rangeData to keep the entire list?

[ABG]

@Harn

The Offers screen has the filtering

Offer1, or listOfOffers?

[Harn]

Sorry I meant the spreadsheet page Offers.
The AI2 screen I've been working on is list of Offers.

I'll read through your previous reply now and then get back to you.

[Harn]

OK so for the screen1 comment and removing the unnecessary loop the correction would look as below?

[ABG]

@Harn

and removing the unnecessary loop the correction would look as below?

Yes, assuming you want the value of that tag to hold the entire returned list.

[ABG]

I'm bumping up against supper time here.
Sheet Offers has the names lined up across the sheet.
That's a problem if you want to scale this up or add and drop names.

I had mentioned earlier the idea of turning columns into rows using the equivalent of a JOIN table.

Is that idea still in play?

P.S. Before I break, here is an old, obsolete food trade logger based on a no longer available component.
I mention it for its table structure.

Pizza Trader

[Harn]

Ok I'll have a look at the Pizza Table approach and get back to you. Does
'supper' mean you're not back till after a night time break? I'm in NZ and It's lunchtime.
Yes I realise the column per foodbank provides scalaibility problems, but it's the only thing I've found that will work. The Receiver and Users Spreadsheet tabs shows a separate approaches to addressing this.

[ABG]

@Harn

I'm in NZ and It's lunchtime.

I'm near NY (GMT-5) so don't count on any responses for the next 12 hours.
(I have a life.)

[Harn]

Fair enough, thanks for all your help, catch you later.

[ABG]

I had a chance to look over your app and sheets.

I think the offer visibility criteria (expired, viewer in list) can be consolidated into just one column of the offers table, instead of all those individual viewer eligibility columns.

The trick is to use the read with partial filter block instead of read with exact filter. The partial filter would check if the name of the viewer of the offer were in a comma separated list of eligible viewers in that offer's eligible viewers cell. (I assume cells can stretch to include such long csv lists.) To throw in expiration, wrap the viewer list with a =() formula that checks the current date against the expiration date of the offer, and returns blank if expired, the viewer list otherwise.

I haven't had a chance to test this yet.
If it works, it would open up the app to unlimited agencies while reducing the code considerably.

Sample code for multi-select:

Multi-Select in AI2

[Harn]

Continuing the discussion from Read with exact filter - works on columns, can it also work on rows?:

ABG, putting this in here rather than main thread as keep getting an error when trying to send.

Ok, that certainly makes sense, particularly in relation to allowing it to work with unlimited agencies. I'll try this in later iterations of the program, probably with a move to sticking the data in JSON.

In the meantime I've tried and got to work a completely separate approach (see below). I've

1. selected the entire offers sheet so it comes as list of rows of lists.
2. I've then put each of the separate row lists into TinyDB and so I have them as offerList1, offerList2 etc.
3. This allows me to run a loop through each of these offerLists, to check whether a) the index for the current viewer returns 'Y', and if b) the index item for whether the offer is current returns 'current'.

Anyway even if a bit 'clunky', it works!

Thanks so much for your help here though, it's been a long exchange. I'm assuming you work for MIT AI, or are you just an enthusiastic amateur. If the former, and if appropriate, send me contact details for your boss, and I'd be happy to give him positive feedback on the work you've done here. I'll throw a few $s at the donation site.
This really has saved my (posteria word beginning with 'A' MIT won't let me use)! God Bless!

[ABG]

@Harn

just an enthusiastic amateur.

@Harn

I'll throw a few $s at the donation site.

They could probably use it.

Last I heard, they are supported in part by the Hong Kong Jockey Club.