search

UserOfficeProject / issue-tracker

Shared place for features and bugs from all collaborators.
0 stars 0 forks source link

Update CLF Laserlab report #895

Closed simonfernandes closed 11 months ago

simonfernandes commented 1 year ago

Update the report to include both new and old proposal data.

https://office.facilities.rl.ac.uk/reporting/SQLView.aspx?category=clf-investigators&query=laserlab-proposals

simonfernandes commented 1 year ago

@Cat-Lucifer I just noticed that these two updated reports contain Laserlab info:

Do we still want to do this? Or maybe the priority can be downgraded?

Cat-Lucifer commented 1 year ago

@simonfernandes I didn't know this! But yes, please still continue with the issue as is. Too many people use it, other than me, and it is the only query that gives just Laserlab info (without having to download lots more data than is necessary).

mutambaraf commented 1 year ago

Will need historical data at the time of submission.

simonfernandes commented 1 year ago

Hi @Cat-Lucifer, just wanted to double check I haven't missed something - for new proposals there is no Laserlab info other than whether they've answered yes to applying through Laserlab, is that correct?

I can only see that the yet-to-be-released main HPL questionnaire asks this question too. Although actually it looks like maybe it was used in LSF 22_2, 23_1 and 23_2 and later removed? The answer is missing from those proposals on the site (visibly but not behind the scenes).

Cat-Lucifer commented 1 year ago

@simonfernandes So, the only way of knowing, from the proposal questionnaire if the proposal is a Laserlab proposal is that Yes/No question. I know that LSF didn't offer Laserlab funding for FAP call 24-1, so assume that's why the question was removed (???). The above query, I guess, will need to check against this question and pull across the relevant info. Although, I also need to enter the corresponding Laserlab proposal number info here - https://www.facilities.rl.ac.uk/clf/programme/Lists/LaserlabEuLink/AllItems.aspx?View=%7b5dd07ba0-4841-41a5-8623-cb4c195b2e64%7d&SortField=LinkTitle&SortDir=Asc&InitialTabId=Ribbon%2EListItem&VisibilityContext=WSSTabPersistence - which then also pulls through.

Yes, HPL also has this question. I assume it was the same one as in the LSF template pulled in and not a new one! Might be worth you making Sarah aware that bringing back in the same question and associated embellishment is important!

simonfernandes commented 1 year ago

@Cat-Lucifer My understanding of the way this report currently works is: it gets all rows in the above SharePoint Laserlab list and joins legacy proposal data to it where possible. The problem with this is that a) the proposals will only show in the report once you've manually added them to the list and b) it's not configured to join onto new proposal data yet.

What needs to happen is (at a high level) a two-in-one query, where we instead start by getting all of the proposals where the user wants it from each respective system, and then join the extra data from the sharepoint list (where present) to it.

The new system is fine, but in legacy we have a 'euAccess' question that has lots of different values and I'm not sure which means whether a proposal is laserlab or not. I can't figure out how to align it to what's in the Laserlab SharePoint list:

image

Please could you advise? Sorry if information overload, I just wanted to be sure I'm doing this right!

Cat-Lucifer commented 1 year ago

@simonfernandes Hmmm. Let me look into this a bit more

Cat-Lucifer commented 1 year ago

@simonfernandes I have just had a look in legacy and both Artemis and HPL have the following question for EU access with a "Yes" "No" drop down selection. I assume LSF had the same (?). Could we take the legacy data from that?

More generally, I'm happy populating a database with info, but it would be neater to have some automation.

image

simonfernandes commented 1 year ago

@Cat-Lucifer So that question is what eventually ends up in the EU_ACCESS column in that screenshotted table above (also below), which is where all the relevant data is held. It contains "Yes", "No" and various other options.

Through a bit more digging of old code, I think back in the day we used to just ask yes or no and record that in the column. The latest proposal with a yes is 1192000. Then it seems we started to give a list of all EU countries (so there's also specific countries in that column), and then in 2021 we removed specific countries and added "EU country", "EU associated state", "Outside EU" options.

So am I right in thinking we just want to display all results that don't say "No" basically? Easier than I thought possibly, but it still doesn't align with what's in SharePoint? The screenshot above (repeated below) shows the most recent app numbers (top to bottom) where the value is not "No" for legacy only.

image

Cat-Lucifer commented 1 year ago

@simonfernandes Okay, that's more complicated than I first thought then...! Yes, let's go with pulling across data that doesn't select "No", as you suggest, and any edge cases can be captured manually if they arise.

simonfernandes commented 1 year ago

@Cat-Lucifer Just another small thing to be aware of is that, for newer proposals, we can't display the local contact user details other than the name that the user provides us.

Cat-Lucifer commented 1 year ago

@Cat-Lucifer Just another small thing to be aware of is that, for newer proposals, we can't display the local contact user details other than the name that the user provides us.

@simonfernandes I'm cool with that.