cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Menu data

Former Member
0 Kudos

Hi,

I notice that there are times when the menu IDs are different for different companies. For instance, a user-defined table added may have a different MenuID for Company A than for Company B depending the other tables the company had already been added.

Is there a way to find out the menu ID for editing a given user-defined table? Is this info kept in the datadase where one could get it using SQL?

Thanks,

Mike

Accepted Solutions (1)

Accepted Solutions (1)

rasmuswulff_jensen
Active Contributor
0 Kudos

Hi... Dealt with this problem in the past, and back then I didn't find any place to extract the data... What I did was to loop the menu-ids in the usertables menu and did the matching using the name of the menuitem.. a bit of a hack but it did work

Former Member
0 Kudos

Yes, since User define tables are dynamic data that are unique by company database, there MenuID can vary, I think that the best to do is like Rasmus said, some logic to loop thru all the menuids of the user define table.

Regards,

WB

Former Member
0 Kudos

William, Rasmus,

Thanks for your input. But how does SAP persist that data? Wouldn't database be necessary?

Mike

rasmuswulff_jensen
Active Contributor
0 Kudos

They are complete incremental assigned based on tablename starting from 51201

This is the UID if you have the following tables:

- TableA = 51201

- TableC = 51202

- TableD = 51203

Now if you add a table called TableB you will get

- TableA = 51201 <<Same

- TableB = 51202 <<New (was TableC)

- TableC = 51203 <<Changed (was TableD)

- TableD = 51204 <<New (next number)

So the data is not persistant... But you could actually calculate the UID by this SQL...

SELECT 51200+ROW_NUMBER() OVER (ORDER BY TableName) AS MenuUid, TableName FROM OUTB WHERE ObjectType = 0

This code returns all non-udo-tables in the database with the record-number + 51200 (the menuentry for the userdefined tables), and all you need to do would be to loop through the recordset and locate the correct Table-name

Former Member
0 Kudos

Rasmus,

Thanks for the SQL code. I'm having one problem with it though. I created a function that would use it in order to have a consistent way to get the menu ID for any given user-defined table.

My code is below. When rs.DoQuery(SQL) is run, I get an "Object reference not set to an instance of an object." error. When I directly run the query that is created in the function in SQL Server, it runs as expected. Could the query be one that is disallowed by SAP?

Private Function UDT_MenuID(ByVal TableName As String) As String

Dim rs As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

Dim SQL As String = "SELECT ROW_NUMBER() OVER (ORDER BY TableName) + 51200 AS MenuUid " & _

"FROM OUTB WHERE ObjectType = 0 AND TableName = '" & TableName & "'"

Dim MenuID As String

Try

rs.DoQuery(SQL)

If Not rs.EoF Then

rs.MoveFirst()

MenuID = rs.Fields.Item("MenuID").Value

rs = Nothing

End If

Return MenuID

Catch ex As Exception

SBO_Application.MessageBox(TranslateMessage("ErrorGettingUDT_MenuID") & TableName & "." & vbCrLf & ex.Message)

Return ""

End Try

End Function

rasmuswulff_jensen
Active Contributor
0 Kudos

Hmm.... I see the problem...

SAP have a bug in their record it appears... I did a SQL profiler on the SQL and for some reason SAP transform this SQL:

SELECT 51200+ROW_NUMBER() OVER (ORDER BY TableName) AS MenuUid, TableName FROM OUTB WHERE ObjectType = 0

int this

SELECT 51200+ROW_NUMBER() OVER (ORDER BY TableName) AS MenuUid, TableName , [OUTB].TableName AS ''kEY_TableName'' FROM OUTB WHERE ObjectType = 0 FOR BROWSE

It is quite normal that SAP change the sql before execution, but this time they have done an error....

Problem in this sql is the

 [OUTB].TableName AS ''kEY_TableName'' 

since ''kEY_TableName'' useds two single quotes ('') instead of a single quote (')

You should log a message at SAP so they can fix it...

Answers (0)