OPEN COMPETITION - Reward £1,000 by Bromcom

[Damon]

Hi All,

 

As some of you are aware I work as a consultant in the Edtech market, and like a lot of folks on this forum, have spent most of my working life supporting and advocating for schools and Education. I work with Bromcom on a fairly regular basis and saw the challenge as a response to the concerns schools, MATs and LAs are facing at present. Because ultimately, I believe that schools should be able to exercise choice without excess barriers being placed in their way.

 

As a number of you have been commenting about this on here and socials, I reached out to Bromcom and Ali for some clarification. After a chat, I agreed to be a formal moderator for this competition alongside Nick from Finnemore Consulting. Hopefully lots of you know of Nick Finnemore from his SIMS days and now as a well-known industry bod.

 

To make things a bit clearer and fairer for anyone who wants to participate. Here are some key things for everyone to be aware of along with an update on the competition itself.

 

 

• Bromcom are not expecting you to build an application to do this. It's more that you just need to prove it's possible to meet the brief on an “all or nothing” basis. It is very important that schools can get “all their data” from an ESS hosted SIMS database. For example:
  
  • On the lookup side of things, can you confirm that all SIMS lookups can be exported via the API and give a handful of examples/screenshots evidencing the output?
    
  • With the Secondary elements - Can you confirm there's an automatable way to export all Cover, Options, Exams, Timetable and Curriculum data. Again, providing examples/screenshots of the output to prove it can be done.
    
  • Exam candidate, seating and associated room information, Exam award title, award codes and series codes, Exam components such as sitting date, duration and date marks due.
    
  • Timetables - Can you export timetable bands and blocks with their associated IDs? Timetable classes with periods and room information, Non-Teaching timetables and Period structures.
    

 

 

 

• This competition will close on Thursday – 11/07/24 at 4PM BST so please respond here or via [email protected] – Please note:
  
  • There will only be a winner if they can achieve all of the above as Bromcom need to be able to know they can help schools migrate all of their data, not a partial migration that leaves schools lots of work to do.
    
  • If there is no winner, the best contributor will be selected and £1000 will be paid to a charity of their choice.
    

 

I hope that clarifies some things and makes this a bit more viable to do as a fun project but also increases awareness of the difficulties schools are facing. I appreciate the ambiguity had some people concluding that weeks of development work was expected for £1k and that was not the original aim.

 

Please feel to ask any questions about the project.

[FN-GM]

Even still, doing that work for them for just £1000 is a bit of an insult.

[PotNoodleTech]

I think people are going a bit over the top here - it's just a bit of fun and I think it's cool to put the challenge out to us techs but sadly bromcom didnt realise how downright grumpy most of you are.

 

Someone who's pie hot at sql/spreadsheet/pdf data mining could grab an easy £1000 here and have a bit of fun in this thread whilst doing it - and all I see is entitled moaning?

[FN-GM]

and all I see is entitled moaning?

 

All I see is a company who is taking the mick. A cheap way to obtain a goal. In the real world of business IT this would cost them a lot more. There is a whole thread about devaluing IT professionals. This in just another example, albeit not from a school this time.

Edited July 9, 2024 by FN-GM

[PotNoodleTech]

Well it's up to each techie if they want to entertaining getting an extra grand or not?

 

Either way I am flabbergasted that the relevant expertise is not currently within bromcom.

[dmj]

Well it's up to each techie if they want to entertaining getting an extra grand or not?

 

Either way I am flabbergasted that the relevant expertise is not currently within bromcom.

 

You could read it as ESS documentation is not up to scratch, and if that's the case then I'm not going to spend x hours on bruteforcing the api endpoints for something that I might not find.

[Damon]

How long would you perceive an entry to take FN-GM? More than a day?

 

In my mind this is just over half a day if the data is accessible via the API. There's no other viable way of doing it really in terms of a migration path. The way I look at this, I can count around 13 queries if I were to offer up 3 examples of lookups and hit every single point on that list. Queries for the API are two or three lines of code that you can chop up and change about for each data area. You run them, grab a screenshot of the output and tick that one off the list.

 

I should imagine the people that know the API well would know from the outset whether all this information is able to be queried or not and then choose whether they think it's worthwhile taking a punt on it. If I knew the API well and thought it was possible, personally i'd happily spend a day on something for a grand.

 

I think part of the problem is that Bromcom probably don't believe this is achievable via the means that ESS have allowed schools to export data. So perhaps this is an exercise to say 'prove us wrong', if you can you get a grand, otherwise we'll give it to a charity. And likewise, despite the earlier confusion they aren't asking for all the code and documentation on how everything is done, it's just about proof whether this is possible or not. You aren't handing over your IP in this process, otherwise I could understand your important point about value/recompense in our industry.

 

Lastly, I am biased here as I regularly work with them, but a little known fact about Bromcom is that they reinvest 100% of their profit back into their business and have done since day 1 (check companies house). That's decades of money being invested into staff and the products. So whilst you may have thoughts about the company for a range of reasons, I will say that they don't hold back on investing in people because there isn't a queue of shareholders waiting to collect their dividends.

[Damon]

You could read it as ESS documentation is not up to scratch, and if that's the case then I'm not going to spend x hours on bruteforcing the api endpoints for something that I might not find.

 

I agree. That's why I'm not participating in this either. But, there are people out there who know this a lot better than me and have been starting to reach out. If they know from experience it's all possible then it makes the task a hell of a lot easier.

[FN-GM]

How long would you perceive an entry to take FN-GM? More than a day?

 

I don't actually know, I am not an SME in this area. But considering that Bromcom are struggling with this, I would consider it to be tricky (or as you said possible at all). Because of this would guess it would be more than half a day. I would envisage that Bromcom have already spent much longer trying to solve the issue.

 

I think it is actually a good idea what they are doing. I just have an issue with what they are paying. It is far from what you would pay to get an SME in to solve the issue, especially considering this is a niche area (compared to say networking consulting). If someone on here holds the key to their big problem, I believe they should be well compensated.

Edited July 9, 2024 by FN-GM

[psydii]

Looking at the Damon's clarification, all that is necessary is screenshot evidence that you could use the stated tools to extract the data - you don't need to supply the code or an application. I don't think they are trying to get the community to solve the problem per-se.

 

I strongly suspect they are expecting the problem as defined to be unsolvable via the API/Reporting tools they are allowed to use, and this is an exercise in demonstrating that.

 

If it turns out that someone here (or the other place) is able to demonstrate its possible, then that person collects the money and Bromcom (and others) move on.

[dmj]

I strongly suspect they are expecting the problem as defined to be unsolvable via the API/Reporting tools they are allowed to use, and this is an exercise in demonstrating that.

I agree, which makes it all the more disappointing that they offer so little reward, essentially to find a bug in an undocumented api so they can win a case against a competitor.

[Damon]

Another way of looking at this is that someone could do a small amount of work, qualify as the best contribution and get £1000 towards their charity of choice. 😄

 

Personally, I'd rather that no one needed to spend any time trying to work out whether it was possible to get all of a school's data out of their existing system. I would also prefer it if schools didn't have to change their plans based on the fear of losing lots of data through switching systems. But that's where folks are at right now and I think the end result hurts the consumer just as much as the businesses involved.

[vikpaw]

So now one of our hardworking colleagues proves it can be done, and then doesn’t get paid to actually do it?

Someone else will continue the work?

 

It’s not a bug bounty (this autocorrected to “big” bounty and it’s not that either!). It’s been called out before, so let’s be honest; it’s a publicity stunt, and in my opinion devalues and disrespects our members.

 

It’s an available API and isn’t that easy to use when I last had a go because the guidance wasn’t great, nonetheless it’s available, has some documentation and is designed to do certain tasks. Depending on the user it can be free / paid for and you can ask for help albeit paid.

 

You don’t need a competition for this, a school could just request support, even if it was paid for and ask specific questions about the data required and the availability. To earn my £1000 if I was a SIMS school, I’d log a support ticket, though fat chance of getting a reply in the required timeframe.

 

Why stir the pot and try to get engagement/publicity for something many person days have failed to achieve other than try to prove a point at our expense. If you really want people to waste time on this I agree the reward should have been way higher to just give someone a chance. Even the API support team would have given it a go!

 

I say it’s shameful. As penance donate some money to charity anyway, and sponsor an apprenticeship or two for IT techs in schools and give back some respect to one of the most under appreciated roles in education.

[Damon]

I'd argue that the position schools and alternative MIS suppliers are put in is shameful but it's clear that there's mixed opinion on this thread and I doubt there's much that can be done to change that. Also, it seems like the money will be going to charity as it stands so rest assured that will happen and an Edugeek member will get to decide.

 

Out of curiosity, I wonder how much someone on the API support desk for ESS earns? My first F/T job was on the CAPITA SIMS helpdesk and I got £14k. That was less than I would earn in the supermarket at the time. I'd hazard a guess that the API support desk might get £30-£35k. The higher amount is equivalent to about £110 a day after tax. That person would have the skill to answer this fairly quickly and take home just under two weeks of their normal earnings.

 

From what I understand, the intention of this competition was simply meant to be a bit of fun to see if there's any SIMS experts out there that will call out Bromcom and say 'of course you can do that!' Sure, in hindsight, perhaps more money would have yielded a better discussion and more interest but this isn't meant to be a formal declaration of what something is worth to Bromcom or the industry at large. They put that on their job adverts.

 

There are people out there, myself included that would enjoy the challenge of demonstrating that something is possible if I had the expertise. If no one else was doing it, I'd spend the morning on it and collect the cash. After that, I'd give them a call and try to negotiate a nice project fee for putting a permanent solution in place.

[Damon]

I don't actually know, I am not an SME in this area. But considering that Bromcom are struggling with this, I would consider it to be tricky (or as you said possible at all). Because of this would guess it would be more than half a day. I would envisage that Bromcom have already spent much longer trying to solve the issue.

I think it is actually a good idea what they are doing. I just have an issue with what they are paying. It is far from what you would pay to get an SME in to solve the issue, especially considering this is a niche area (compared to say networking consulting). If someone on here holds the key to their big problem, I believe they should be well compensated.

 

I agree that more money is always going to get a better response but I do believe if someone holds the key to this, they would have a lot of negotiating power moving forward. As I say, they aren't handing over their IP in the competition so they retain the ability to sell their services afterwards and make a nice bit of cash.

 

Also, I'm not so sure that there are SME's out there that are offering this. Bear in mind the folks at Wonde/Groupcall and so on historically use command reporter but have probably ramped up to the API to get more since the ESS announcement. Understandably, they would probably only sell the access to the data through their systems rather than the means to extract. This just means it increases the cost of migration to the consumer as there's another overhead to consider.

[vikpaw]

The cost to use Wonde as a self-service school user is trivial.

 

Edit: but probably 3 days of an ESS engineers salary.

Edited July 10, 2024 by vikpaw

[Steve101]

"Prove our experts wrong and win some money, It's half your monthly salary!"

[Damon]

"Prove our experts wrong and win some money, It's half your monthly salary!"

😂 - if only that had been the original post

[Ditto]

The cost to use Wonde as a self-service school user is trivial.

 

Edit: but probably 3 days of an ESS engineers salary.

 

I'm a little confused by the several post in this thread about Wonde. Can it really access all the data in SIMS to enable a migration to proceed with a migration to another MIS? If yes, do they yes a dedicated set of API's or have some other means of accessing the data. Another point, I though Arbor deal with the same need by continuing to use a backup database whilst agreeing to cover any legal concerns down the line. Any reason why Bromcom won't do they same?

[psydii]

I imaging it is because Bromcom have gone head to head legally with SIMS at least once and the DfE stepped in before things got too expensive with the lawyers. Perhaps this time they have too much at stake to take that risk. So this time we have Arbor taking the risk, and Bromcom attempting to prove that the legal, api-based method is not possible, together they bring the case forward that DfE/Competition Commission need to step in.

[vikpaw]

"Prove our experts wrong and win some money, It's half your monthly salary!"

...but will take you 2 months to understand the docs

 

Has anyone actually entered? If not, i'll put in 10secs of work just to nominate : https://www.christie.nhs.uk/

[Damon]

Bromcom also indemnify schools against any legal costs that arise through the use of backups. Just double checked and it's on their site too.

 

I'm not sure if Wonde can gather all information as of today, I know in the past that they didn't, it was just data from the commandreporter. I believe Wonde have an API for the data they store which is how the third parties then access information for their apps.

[Damon]

...but will take you 2 months to understand the docs

 

Has anyone actually entered? If not, i'll put in 10secs of work just to nominate : https://www.christie.nhs.uk/

The closest we have to an entrant is a contribution by Steve21 on this thread! There has been plenty of discussion on LinkedIn about using AI to convert PDFs, screen scraping suggestions and so on but Steve has actually come forward with a suggestion based on the original context. Ironically it was already known that behaviour itself was one of the easier ones to access but that data area was used as an analogy for the less common and harder to define areas that were being collected. But I think it stands regardless.

 

Special mention for Marci's idea on PDF conversion though, I'm new to power automate and loving the ability to do stuff like that in bulk.

So in short - It's all to play for Vik and an excellent cause to nominate.

Edited July 11, 2024 by Damon

[vikpaw]

I don't expect to win, but throwing this out there cos i do want to support my charity. Sorry @Steve21

 

Caveat: I haven't been able to test this, but the example stipulates lookups that cover illness categories such as Heart Condition, Heat Stroke, so covers medical. I would expect this method to be comprehensive for all lookups and lists the code, not just the description as required.

 

Lookups may be exported in 2 parts:

 

• Get a list of all of the Lookup tables.
  
• Get a list of all the Lookup Codes for each table.
  

A lookup table is of the form

public class dLookupTable
{
   public string Name { get; set; }
   public string Area { get; set; }    
   public string Mode { get; set; }
   public List TableEntries = new List();
}

 

A lookup entry is of the form

public class dLookupEntry
{
   public int? ID { get; set; }
   public string Code { get; set; }
   public string Description { get; set; }
   public bool Active { get; set; }
   public string Category { get; set; }
   public string CatDes { get; set; }  
}

 

We can use the Maintain Lookups browse to get the set of tables. Note that we have 2 types of lookup - True Lookup and Group Type lookups which need to be processed separately.

List lU_Tables = new List();
//GetStaffLookups(ref lU_Tables);
         
SIMS.Processes.LookupTypeBrowser lutb = new SIMS.Processes.LookupTypeBrowser();
SIMS.Entities.LookupArea lua = new SIMS.Entities.LookupArea();

foreach (var v in lutb.Browse("%", lutb.LookupAreaAny))
{
   if (v is SIMS.Entities.TrueLookupTypeSummary)
   {
   //..
   }
   else if (v is SIMS.Entities.GroupLookupTypeSummary)
   {
   //..
   }

True Lookups

TrueLookupTypeSummary t = (TrueLookupTypeSummary)v;
dLookupTable table = new dLookupTable();
table.Name = t.Description;
table.Area = null;
if (t.LookupArea != null)
{
   table.Area = t.LookupArea.Description;
}
table.Mode = null;
if (t.LookupMode != null)
{
   table.Mode  = t.LookupMode.Description; 
}

and then need to load the lookup entries for the table.

SIMS.Processes.LookupTypeDetail lutd = new SIMS.Processes.LookupTypeDetail();

lutd.Load(t);
SIMS.Entities.TrueLookupType trueLookupType = new TrueLookupType();
trueLookupType = (TrueLookupType)lutd.LookupType;

foreach (SIMS.Entities.TrueLookupValue lookupValue in trueLookupType.LookupValues.Value)
{
   dLookupEntry e = new dLookupEntry();
   e.Code = lookupValue.Code;
   e.ID = lookupValue.LookupValueID;
   if (lookupValue.Category != null)
   {

       e.Category = lookupValue.Category.Code;
       e.CatDes = lookupValue.Category.Description;

   }
   e.Description = lookupValue.Description;
   e.Active = lookupValue.Active;
   table.TableEntries.Add(e);
   }
   lU_Tables.Add(table);

 

And for group type

GroupLookupTypeSummary t = (GroupLookupTypeSummary)v;
dLookupTable table = new dLookupTable();
table.Name = t.Description;
table.Area = null;
if (t.LookupArea != null)
{
   table.Area = t.LookupArea.Description;
}
table.Mode = null;
if (t.LookupMode != null)
{
   table.Mode  = t.LookupMode.Description; 
}
SIMS.Processes.LookupTypeDetail lutd = new SIMS.Processes.LookupTypeDetail();
lutd.Load(t);
SIMS.Entities.GroupLookupType trueLookupType = new GroupLookupType();
trueLookupType = (GroupLookupType)lutd.LookupType;

and then the entries

foreach (SIMS.Entities.GroupLookupValue lookupValue in trueLookupType.LookupValues.Value)
{
   dLookupEntry e = new dLookupEntry();
   e.Code = lookupValue.Code;
   e.Description = lookupValue.Description;
   if (lookupValue.Category != null)
   {
       e.Category = lookupValue.Category.Code;
       e.CatDes = lookupValue.Category.Description;

   }
   e.Active = lookupValue.Active;
   table.TableEntries.Add(e);
}

 

then add them to the list of lookups that you require.

This would serialise to:

{
   "TableEntries": [
     {
       "ID": 1,
       "Code": "Y",
       "Description": "Yes",
       "Active": true,
       "Category": null,
       "CatDes": null
     },
     {
       "ID": 2,
       "Code": "N",
       "Description": "No",
       "Active": true,
       "Category": null,
       "CatDes": null
     },
     {
       "ID": 3,
       "Code": "U",
       "Description": "Unknown",
       "Active": true,
       "Category": null,
       "CatDes": null
     },
     {
       "ID": 4,
       "Code": "R",
       "Description": "Refused",
       "Active": true,
       "Category": null,
       "CatDes": null
     }
   ],
   "Name": "Service Children in Education",
   "Area": "Students",
   "Mode": "Statutory (Fixed)"
 }

Edited July 11, 2024 by vikpaw

[Damon]

Thanks for this Vik!

 

It made me question my own searching abilities for a moment and then I saw that it had only just been published by ESS. Funny that eh! This is genuinely super helpful for a lot of reasons, so thanks for posting it.

Edited July 11, 2024 by Damon