Unable to access a formatted search

[former_member589482]

Hi Experts

As per advice from 'Mastering SQL for SAP Business One by Gordon Du' (great book BTW) I have by default set all authorisations to query wizard, generator and manager to 'no authorisation'. But my users cannot now access some cells in SOP that contain a formatted search.

How do I give them access to the formatted search but NOT the query wizard, generator and manager? I note in the book it advises that you set authorisation groups against query categories - Is this the BEST way to control access?

[former_member589482 · Accepted solution]

Dear Advisors

I have had an answer back from SAP via my VAR and found out the missing ingredient to allow users access to the formatted search queries embedded in forms. I'd already set the FMS query category to group 15 and set the user read only authorisation level 15 too, without success.

The missing link was that besides allowing the above authorisations you also have to allow the user full authorisation to - 'Execute non-select SQL statement' which wasn't the case and hence the problem. See screen shot below: I hope you find this feed back useful and thank you for your help.

[kothandaraman_nagarajan · Accepted solution]

Hi Martin Lewis,

Thank  you so much for your valuable feedback.

Regards,

Nagarajan

[former_member589482]

I have now reverted to where I started and given full authorisation to query wizard/generator & manager and now --- the error does not happen anymore.

Its not ideal, I know but at least we know its not the FMS and/or syntax but a User and/or authorisation issue.

Does anyone know where I go from here or do I log it with SAP?

[kothandaraman_nagarajan]

Hi Martin Lewis,

Yes. Its better to check with SAP support.

All the best.

Thanks & Regards,

Nagarajan

[former_member589482]

Thanks to everyone for their help. I will definitely update this thread when I find the cause so we all know if it ever happens to another user.

I feel I should award points for the help so will try to do this now.

[Former Member]

The cause is clear. You have to allow user to be accessible to the query so that the FMS can run. No need to bother SAP support. It might be chargeable.

[Former Member]

Hi,

Unfortunately, it may not be possible to disable all users' access to query manager and FMS also work.

Thanks,

Gordon

[former_member589482]

Thank you everyone for your interest and help. I shall post the information you require below to see if we can find a solution.

It is most definitely an authorisation issue as the error message ONLY started after I denied the users access to ALL query reports (admin/authorisations/etc.) . I then realised the errors of my way (by actually reading Gordon's book ) and have reinstated access to just the formatted search queries ONLY (by using query/category/group 15)

It is only since I made these authorisation changes that normal users get the error message. Our superusers whom didn't get their authorisations changed DO NOT get the error message.

However here is the syntax for ONE of the formatted searches and Johan, you are correct - it errors out when I follow your instructions (see screen shot error note). This however doesn't explain why superusers don't error out?:

SELECT T0.ItemCode, T0.Dscription

FROM INV1 T0

INNER JOIN OINV T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T2.CardCode = $[ORDR.CardCode]

GROUP BY T0.ItemCode, T0.Dscription

Once again guys your help and interest is most appreciated.

[Former Member]

This query should not run on its own. Assign to FMS to try.

[former_member589482]

Hi Gordon

I'm not sure I understand your reply? By FMS I assume you mean Formatted Search?

If so I have authorised our normal users to access only the formatted search category but still get the error message as previously indicated.

If I've misunderstood your answer would you mind explaining in more detail.

[Former Member]

FMS means Formatted Search.

In your screenshot, you have shown the query running under query manager. That is not supported.

As for the error message, that is not avoidable if you don't give user enough authorization.

[kothandaraman_nagarajan]

Hi Martin Lewis,

1. The error appear due to card code selection. You need to select card code from OINV table not from ORDR table.

Try this:

SELECT T0.ItemCode, T0.Dscription

FROM INV1 T0

INNER JOIN OINV T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T2.CardCode = $[OINV.CardCode]

GROUP BY T0.ItemCode, T0.Dscription

Note: Tested in  8.81 version. No error message.

2. Before run, just copy and paste it in query generator and execute. Sure the system throws an error message. Just ignore and save it under query manager  with appropriate name.

3. Open invoice document, run your saved query from --->Tools---->Queries--->User queries.

Thanks & Regards,

Nagarajan

[Johan_H]

Gordon Du wrote:
...
In your screenshot, you have shown the query running under query manager. That is not supported...

That is not entirely correct. It is supported, but Martin did not follow the entire instruction.

The trick is to first open the form that contains the formatted search, browse to any record, and then run the query under the query manager.

[former_member589482]

Following your exact method Johan, I get the below error message (2nd screen). When I do the same operation as a super user I get no error message (1st screen). Could it be I have just assigned the normal user with just 'group 15' authorisation ONLY and FMS query category is also set as 'all groups'?

To create the error what I changed is as follows:

1. I set query/category to 'all groups' in the query manager

2. I set user/authorisations to 'no authorisation' for any query groups

3. I then altered the user/authorisation to = group 15 only

I think its in changing the authorisations is where I have gone wrong as this caused the error and superusers (who didn't get any authorisations changes) show no error.

[kothandaraman_nagarajan]

Hi Martin,

If read only database user runs above query, the above error message will appear. To check, run below query.

• SELECT T0.[ROUser], T0.[ROPass] FROM DBADM T0

Thanks & Regards,

Nagarajan

[former_member589482]

We have no 'read only' users, just superusers and users whose authorisations have been limited (see above)

Most of our users are superusers as due to a SAP bug in our patch level our normal users cannot open some calendar fields - or so I'm told. we are on 8.82 PL05

[Johan_H]

Hi Martin,

You may well be right.

However looking at the result from the query, and at the error message, I wonder about two things:

1. Usually the query of a formatted search may only return a single result (row).
2. The error message says the user is read-only.

The first thing may be a fluke, but the error could be explained by the user having read-only rights only on the form that the formatted search is in.

Technically speaking, a formatted search will add or update a value in the form, in other words, it edits the form. As the user has read-only rights to this form, he is not allowed to edit it, et voila...

You may test this by giving the user full rights to sales orders.

Regards,

Johan

[former_member589482]

My VAR states there is nothing wrong with the FMS or the syntax therein (as it works perfectly well with Superusers) and they propose I alter the normal users BACK to full authorisation on query wizard/generator & manager.

Yes......that's just where I started from and goes against any security advice given previously.

Your point about form read-only rights is valid and I have passed this along the food chain and await their comments.

I will have to give FULL access to ALL queries, just to get my users working again, but I'm not comfortable with it and will request my VAR log it as a formal issue (however that's is done?)

[former_member589482]

Gordon. Could you expand on this answer as I think you are in the right area?

[Johan_H]

Hi Martin,

Martin Lewis wrote:
...
Your point about form read-only rights is valid and I have passed this along the food chain and await their comments...

Did you test it though ? I mean, that was an educated guess, but we can't be sure that that is it.

Martin Lewis wrote:
...and will request my VAR log it as a formal issue (however that's is done?)

What happens now, is that your VAR will (should) try to reproduce the issue on their own test system, and try to find a solution using normal system settings. If that doesn't work, they will log a support message with SAP (second tier) Support for SAP Business One, on the SAP Service Marketplace.

SAP Support will investigate, and they will then provide some sort of solution.

Regards,

Johan

[former_member589482]

Users have authorisation for forms so its not that. My VAR has escalated it to SAP, at least that's what they've told me,  so lets see what happens.

[former_member589482]

Thanks for your conformation Nagarajan. I've set up the Query/category authorisation groups to 'ALL (1-15) and set the users authorisation level to 15 so they now have access to formatted searches (I think) But they now get an error message concerning a table called OOAT. I'm not sure what this table or error is. Can you help?

Thanks again

[kothandaraman_nagarajan]

Hi,

Sure. Please post screen shot of above error message.

Thanks & Regards,

Nagarajan

[former_member589482]

I have the screen shot as an attachment in an email from the user. How do I attach it to a reply?

[former_member589482]

Hi Naga. Ive managed to save the attachment as a picture so here it is.

[Johan_H]

Hi Martin,

This is a random error message that B1 will throw when there is an error in a query. In this case the formatted search probably uses a query, and the error is in the syntax of that query.

Queries for formatted searches can easily be tested by first opening the form that contains the formatted search, and then from the Query menu (Tools > Queries > User Queries) opening the query in question.

The query window should then show the desired result.

Regards,

Johan

[former_member589482]

Thanks for your help but one quick question before I look at syntax error. All my users who are 'superusers' and use the same form do NOT get an error message? Only the non-superusers where I've changed their authorisations get the error message!

Logic dictates therefore that the syntax or error message is not the issue. I must admit SAP doesn't always follow logic though. Any suggestions?

[kothandaraman_nagarajan]

Hi,

Please post your query, then only we can find root cause of error message.

Thanks & Regards,

Nagarajan

[Johan_H]

As Nagarajan said, it would help to see the query to determine if it is the culprit here.

It is possible that we are dealing with an authorization issue.

However, the test I suggested earlier is simple enough to perform and perfectly safe, so you might want to try that to being with.

Regards,

Johan

[kothandaraman_nagarajan]

Hi Martin Lewis,

Yes. Set authorizations under "Group" in query manager.

Thanks & Regards,

Nagarajan