Check EdenRed for redeemed codes and cross compare

[Ogdos]

Sorry if this is the wrong forum for this, please move if there is a better one.

 

What I'm trying to achive, is to check which free school meal codes have been redeemed by parents using the EdenRed service.

I have a spreadsheet (excel) that contains the list of the parents, and the serial numbers of the codes that were given to them.

I'd like to then compare from EdenRed, which ones have been redeemed and turn that row green (for easy identifcation).

Unfortunatley, there doesn't seem to be an easy way to do this. As EdenRed only shows the infomation on screen, with no easy way to download and compare.

 

Just wondered if anyone else has looked at this and come up with something that automates this task?

 

Thank you.

[resin1]

Haven't done the self-distribution route as yet, but I assume if you go via eCode Management=>Self distribute eCodes, and click search with no parameters to get all, or specify an order date range to narrow it down if you have multiple orders, to then get all for that order, the only option I can see is to use the mouse to select the rows in the table, Ctrl-A and Paste Special as Text into Excel.

 

The downside is that this whole web-site is so poorly designed, you'd have to step through each and every page from that search, with its small number of display rows, and repeated that, just to get a full list into Excel to then cross check.

 

Considering the site performance, the repeated server errors, and orders that might have 30-40 pages like that in the output, that's not a job I'd wish on anyone. But a job I'm shortly about to try to do for ours, since I want to do exactly the same cross check on a regular basis.

 

So if anyone comes up with a simpler method, I'm all ears!!

[Bev]

I only have 33 parents, so I have been copying the information off the screen and pasting it into Excel, but it's still 4 screens worth just for 33 parents, so if you are a large school it will be a mammoth task.

[Seb1780]

We have over 100 parents on each order, and we're now on our third order! I created a spreadsheet and once per week I get up at 0500 to scroll through the 33 screens of tedium to see how the redemptions are going and manually add the tick to the spreadsheet.

 

There is no simpler way.

[Ogdos]

So after far to many hours on this. I think we can all conclude that the EdenRed site is in need of further work. Although if they could just get the codes to activate on time would be great.

 

My new workflow is as follows.

 

I access the site at a quiet time, and to do a search for activation date; only doing one week at a time. (Still 16 pages). I then copy the redeemed serial numbers to a blank excel spreadsheet.

 

Once in excel I then select all (ctrl+a), select unmerge cells (from the home tab), remove any columns with non-relevent information, and finally then remove duplicates (from the data tab).

 

I then copy that to my excel spreadsheet with the week codes on. I will include sanatised versions here (with anyluck) and you are welcome to use or change in anyway that suits you.

 

The first dated one, was my beta sheet, where I merged two separate sheets (which I had used for mail merge and email merge) so is not the best, but might still be useful if you only have a few codes to check.

 

The later dated one, is my more refined sheet. I use this as the basis for both mail & email merge and it worked very well this week (We had all codes emailed and printed before 8:30am Monday morning). I won't include details of how to use it to run a mail/email merge, but feel free to DM if you want pointers.

 

In both cases, you copy the codes from the blank sheet (as outlined above) and it checks the serial number against the codes you have and highlights the matches. You then change the status from live to redeemed and it changes that entry to green, giving our DSL's and Family workers a quick overview for who they will offer further help.

 

Today's check only took 20 minutes from start to finish. (As above, please feel free to use, change, offer improvments, etc).

 

FSM Voucher codes 20-04-2020.xlsx

FSM Voucher codes 27-04-2020.xlsx

Edited April 29, 2020 by Ogdos
Line spacing

[resin1]

So today seems to prove once and for all what a bunch of jokers this company is. I read something the other day that they would be doing something to at least flag up where communications have actually failed due to invalid e-mail address i.e. their e-mails bounced.

 

So what do I see today - their method of flagging this appears to be that they change the "Last name" on the record in question to "BOUNCE". Hey - so you are trying to use their eCode management search facilities for a particular record and search by the surname you know, and... what??? no such record??? But it was there this morning??? What??? Oh... now I see why there's no match. And hey - why is my cross checking where I have been copying the list of eCode redemptions, and I use a join of surname and forename to lookup against clean pupil lists, now showing mismatches?? Oh... now I see why.

 

Meet The BOUNCES meets Meet the Fxxx'ers

 

Thanks for the flagging guys but how goddam crude is that solution?

[SallyMc]

Hi

 

Are the serial numbers the e-codes?

[Ogdos]

The serial numbers are not the eCodes, I believe that they are the ones that track each code. The eCodes are under the codes heading.