As in the title, I can get Read With Exact Filter to work with the built in colID field on a column in a spreadsheet, but is there a way for it to do it on a row?
The immediate solution I've thought of is to transpose the row into a column in the spreadsheet and then use the existing function. Seems a bit clunky though. Any other suggestions?
Can you give an example of what you want to do?
I used a Read With Exact Filer in my Marathon sample at
to test if a Marathon runner was already registered, asking for rows with their ID in column 1.
Are you extending rows with multiple items?
Usual practice is to add extra rows for more items.
Describe your data model.
Hi ABG thanks for your response
I've got 2 worksheets in my spreadsheet.
- is Offers which has different offers as rows and then a cell relating to each offer stating whether this is current or not. In the following block of code the Call spreadsheet with exact filter, (in the initalize block) and the related When spreadsheet got filter result, gets me the 4 of the row IDs (which correspond to row numbers) which are current. All good.
- worksheet is Receivers which contains a list of the Offer numbers (including the current ones accessed above) listed in a column. Against each number in columns to the right are the names of Receivers who are allowed to access a current offer. I'm wanting to identify whether the viewer (i.e. the current user of the app) whose details are accessed in the initialze block, matches the name of one the receivers in the columns associated with one of the current offers.
To give some context, this is an app for Foodbanks to share excess food with other foodbanks. Each foodbank can make an Offer which has a) an expiry date, hence the need to check offer is still current b) a list of other foodbanks they want to share the food with. These are the Receivers. As the current user of the app (the Viewer) may not be one of the foodbanks set by the Offerer as a potential Receiver, this also needs to be checked for.
Block 3 of the code below is an attempt to access the Offer ID of the Offers where the Viewer's name is included in the list of the Receivers. I'm doing this with a loop inside a loop structure but am currently getting a null response.
I've also tried making a dictionary with an Offer ID, list of receivers pair but keep getting 'not a well formed list of pairs' error. Could this me a useful approach though?
It's occured to me though that instead of having my Receivers worksheet with offer numbers going down the rows, and data out along the columns, I could have the Offer numbers going out along the columns and the data going down the rows. This would allow me I think to use the ReadWithExactFilter method?
I appreciate this is complex, thanks for your patience. I'll keep reading through your marathon example and see if that helps as well.
Maybe you need a third sheet to act as a JOIN table for Offers and Receivers?
Each row would include one Offer and one Receiver and whatever quantity is involved, if any.
The third sheet would be a replacement for extending columns on the first two sheets.
Extra rows in the third sheet would correspond to extra combinations of Offer and Receiver.
Hi
I tried the transposing appproach I mentioned but then the blocks wouldn't allow me to use the Read With Exact Filter method twice on the same page.
So I'm trying your suggestion of getting a 3rd spreadsheet page to join the 2 other sheets.
I'll come back when I've got further with that.
If it helps, you can add extra copies of spreadsheet components for different purposes, to get the benefit of extra completion events.
Just name them appropriately, to not lose track of their purposes.
I believe you may have problems returning and filtering data after transposing your data.
If the spreadsheet component works anything like gviz for the filtering of data with that block, then gviz requires all the items in a column to be of the same type: e.g. numbers or strings. gviz will decide what value type exists in the column, then omits any values that do not meet that value type.
However, MIT may be using a different filtering algorithm, and it may work ?
Hi thanks for this, but I don't understand.
If I call ReadwitExactFilter for Spreadsheet X , Sheet Bears, Col2, I then need a callback function GotFilterResult for that.
If on the same MITAI page i want to call a separate ReadwithExactFiler for the same spreadsheet and Sheet but say col3, it'll need a separate GotFilterResult for that.
At which point it seems to get confused because it can't work out which callback function goes with which ReadWithExactFilter.
Any suggestions?
Above is response to ABG
Re TIMAI2 Yes I do think I've got problems with it not recognising types of data. In the following
I think I should get a list of repeated 'Y's displayed in the measure Text I'm using as a type of console log, but all I'm getting is {}. From previous experience it seems not to be picking up the itemD = Y in the If/then blocks.
Again any suggestions.
Set a variable when you call a specific filter, then test for this variable in the return event. Something like this:
Ok, got it now. I did find another way round this, but I've got another problem now (thanks for your ongoing patience).
From the above I've now got 2 lists as below, iscurrent and offerMadeToViewer. I'm trying to get a 3rd list which shows the overlap between the 2. iscurrent list is [8], offerMadeToViewer is [3,6,8]. So my overlap should be [8].
I've tried both of the following though and am just getting [].
Again any suggestions, and thanks in advance.
Hi ABG thanks there was no way I was going to work that out!
Unfortunately It's still not working. Difference between what you've got and what I've got as below is I'm working from some established precalculated Lists, not set of strings, changed into list with list from csv row text.
Don't think it should make a difference but I'm still getting [] as my final result. Can you have a look at the following and see if I've stuffed up anything?
and
I advise applying Do It to both inputs to the value procedure, then to the value procedure call.
Show us the results?
Also, feeding output back into an input fouls up debugging.
Ok to clarify by 'Do It' you mean the purple procedure block - to procedure do ?
If so I'll work on this with the 'iscurrent' and 'offerMadeToViewer' variables.
Also could you clarify 'feeding output back into an input fouls up debugging'?
thanks
Extra global variables can be used to hold intermediate calculation results unsullied for later examination.
Did you notice the bubble on the screen shot of my sample call?
That's an MIT AI2 debug facility, in the introductory section off the debugging FAQ.
Make your blocks look like a pincushion with Do It bubbles.
OK, I'll read up on Do it bubbles and get back to you, might be a couple of days.
Again thanks for your help 
Hi again, sorry when I right click on the various blocks 'Do It' is either not there, or greyed out.
Any suggestions?