TSQL to identify user's favourite reports

Hi Nick,

Thanks for reaching out. User favourite details are indeed stored in the PersonFavourite table.

Here's a query you can use to determine favourite assignments by user. These are results that users explicitly toggled as a favorite in the UI:

SELECT * FROM PersonFavourite WHERE IpPerson = '5' AND FavouriteTypeCode = 'FAVOURITE';

You'll just replace IpPerson with the desired user value (as per the Person or IpClass table).

If you want to see all user favorite assignments, i.e., one's you'd see here:

You'd run the same query without the 'AND FavouriteTypeCode = 'FAVORITE';' portion.

I suspect most users would be looking for the actual favorites they explicitly chose to add to favorites though, so for that you can run the first query to obtain those results, then put them into an insert statement in the migrated environment. Of course you'd also have to make sure the ContentId's are the same. In my example, I'd have to check whether ReportId '60687' exists in migrated environment. If it does not, in the original environment you'd search '60687' in ReportHeader table to find report name, search that in the migrated environment, then use that ReportId instead.

Hopefully this explanation makes sense. Please let me know if you have any questions on this.

Regards,

Mike

Hi Mike,

Thanks for that.

We were able to fix the issue at hand by changing the role from "Public Content Writer & Collaborator" to "System Admin", and back to "Public Content Writer & Collaborator".

However, although we can now access and see the favourites, the timeline box display the favourites as 0.

Any idea where this number is taken from so that we can check the DB?

Thanks,

Nick

Hi Mike,

Here's a screenshot.

Thanks,

Nick

Hi Mike,

I think that would be a known issue.

Can you let me know in what build this is fixed:

Thanks,

Nick

Hi Nick,

Nice find! I was not aware of this defect - this is almost certainly related. This defect was fixed in 8.0.5, 7.4.13 and 9.0.4. I don't see the build you're on specified in this ticket, but it's before any of these builds, please upgrade and you should be good to go.

Please let me know how goes and whether you have any additional questions.

Regards,

Mike

Hi Nick,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Hi Mike,

I am still seeing the issue in 8.06 build.

Let me confirm on this though.

Thanks,

Nick

Hi Nick,

Thanks for the update. Please do.

Regards,

Mike

Hi Mike,

We have opened the following case to have the defect fixed:

21036-02-25

Thanks,

Nick

Hi Nick,

Thanks for letting me know! Considering this, I'll go ahead and close this ticket out then, but please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

Hi Mike,

It seems the issue where the user cannot see their favorites happens for multiple users.

We do not know the true extent, and we currently need another way of fixing the issue rather than toggling the permission to admin and back to the original role for each user that is having the problem.

It would be great if we could a response on this as soon as possible as the issue is a bit time sensitive.

Let me know if you need anything else.

Thanks,

Nick

Hi Nick,

This issue was replicated and logged as a Defect in ticket ID 21036. As such, it is currently in the Dev Team's hands. I see in the internal task the importance of this is noted, the priority is set to High and it's already assigned to someone on the Dev Team, so I suspect there will be further updates on this soon.

That said, since this defect is already being tracked in that ticket, I'm going to close this ticket out as it's ostensibly a duplicate at this point. If you have any further questions or concerns surrounding this, please respond in 21036.

Thanks,

Mike

Hi Mike,

The display count showing as 0 is a defect - correct.

The issue we have is users cannot see their favourite reports. After toggling the permissions, then users were able to see their favourite reports, while the count was still displaying as 0.

What we need is a way to find all these users that cannot see their favourite reports, and fix the issue without having to toggle the permission for each one of them as this would be a tedious manual task for potentially hundreds of users.

Let me know if it is still unclear.

Thanks,

Nick

Hi Nick,

I think I understand now: in this case you're adding Favourite Reports, favourites remains '0' (the defect logged in 21036), and they're not showing up in the timeline unless you toggle the permission. Is my understanding now correct?

If so, my follow-up question is: when you say the favourites show up upon toggling the permission, what you're referring to is changing the user Role to Admin, then back to its original Role, correct?

Unfortunately, I can't replicate this, even in 8.0.6, but if my understanding above is correct, it should be possible to toggle this via the following SQL queries in SQL Server:

UPDATE StaffMemberRole SET EndDate = GETDATE() WHERE IpEmployee = '13151' AND RoleCode = 'YFREPORTCONSUMER';
INSERT StaffMemberRole VALUES (13151, 1, 'YFADMIN', GETDATE(), '9999-12-31', 1);
UPDATE StaffMemberRole SET EndDate = GETDATE() WHERE IpEmployee = '13151' AND StartDate = GETDATE() AND RoleCode = 'YFADMIN';
INSERT StaffMemberRole VALUES (13151, 1, 'YFREPORTCONSUMER', GETDATE(), '9999-12-31', 1);

These queries are updating the current role to be no longer in effect, inserting the new role, updating the new role to no longer be in effect, then inserting the old role back and making it current.

You'll of course just need to swap out the IpEmployee ID's (by identifying users in the IpClass and/or Person table), and the corresponding RoleCode's.

That said, given the nature of how Role permissions are assigned (via UPDATE then INSERT queries), it's still a little bit of manual work to do this, but it's likely still quicker than going through the UI.

Anyways, please let me know if this appears to be what you're looking for.

Regards,

Mike

Hi Mike,

I think I understand now: in this case you're adding Favourite Reports, favourites remains '0' (the defect logged in 21036), and they're not showing up in the timeline unless you toggle the permission. Is my understanding now correct?

Nick: No, I'm specifically referring to reports that had already been added as favourites in the past. When we tested with a different user, adding new reports as favourites worked fine ie we could see the report in the favourites section but the count still displayed 0

If so, my follow-up question is: when you say the favourites show up upon toggling the permission, what you're referring to is changing the user Role to Admin, then back to its original Role, correct?

Nick: That's correct.

Unfortunately, I can't replicate this, even in 8.0.6, but if my understanding above is correct, it should be possible to toggle this via the following SQL queries in SQL Server:

Nick: Thanks Mike. I think we could give this a try. When I had a quick look in the DB, it seems the records are there, but unsure how toggling the role played a part in this

Thanks,

Nick

I have uploaded an extract of the DB tables under REQ000001114340.zip on the FTP site if you want to have a look.

• ipperson 74612 - Fixed by toggling the permission
• ipperson 117651 - Not fixed yet (Will be testing the query against this user)

Is it possible to find out whether it could be the defect that is causing the behavior where users cannot see their existing reports added as favourites?

Thanks,

Nick

Hi Mike,

Based on the latest information provided, are you also able to help us with a query to try identify other users that might have this issue since we would also need to run the query for multiple users if the issue is widespread.

Thanks,

Nick

Hi Nick,

Thanks for your response. Good find! I can certainly provide a query to identify users who had pre-existing favourites they had manually defined. The only piece I'm not sure of is what date would be considered pre-existing, so you'll have to plug in that date yourself, but here's the query:

SELECT * FROM PersonFavourite WHERE CreateByCode = 'MANUAL' AND StartTimestamp < '2020-09-23';

Please let me know how goes and whether you require anything else here.

Regards,

Mike

Hi Nick,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Hi Nick,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

Hi Mike,

That is fine.

Thanks for your support.

Regards,

Nick

Hi Nick,

Thanks for confirming!

Regards,

Mike