Export/Import from system documentation tables

(0) Share

Report

Posted on by Rich

320

Feels like a long shot, but has anyone played with exporting data from (and/or importing to) system documentation tables (such as SecurityRoleAssignmentRule) please? Appreciate this particular table will mean decoding/encoding container field values, but the fact that I can't seem to access the table in X++ or as a data source for a data entity means the whole idea may be a non-starter.

I have the same question (0)

All responses (8)

Answers (2)

André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Export/Import from system documentation tables

HI Rich,

For this same table, I did start creating an entity in the past, but parked it. Your question triggers me to continue with it. You can use the table with x++ coding and you can include it in a data entity. If you get errors, can you share them?

If you want to start creating a data entity, you can first create the entity with names as you want to use, but use another table as the system table is not visible. Once created, you can delete the fields and data source. Then add this SecurityRoleAssignmentRule table and add fields.

I have to check my notes. I believe there was something with another standard entity, related to automatic role assignment which also needs some attention.

Was this reply helpful? Yes No
Rich 320 on at

Like (0)

Report

RE: Export/Import from system documentation tables

André,

I wasn't getting errors directly: when I said "I can't seem to access the table in X++", I was being (unintentionally) vague about Intellisense not completing the table name, and not providing the column names when I declared a buffer variable for the table and tried to put in a "while select". I've just tried again and have been able to iterate over the expected rows that I can see in SQL Management Studio on a Dev box, so I'm not sure what I was doing with Intellisense last week: thanks for prompting me to look again.

I've spent a little time trying to decoding the RuleQuery byte array container field to a meaningful text string (Sertan Yaman's blog post on base64 encoding gave me some hope, but based on the result I'm getting right now, I can only assume the byte array isn't base64 encoded). This makes the data entity approach all the more desirable, but so far, I can build a data entity by replacing data source as you suggest, before then getting Failed to parse data entity view queries for following data entities: POCSecurityRoleAssignmentRule. Other than the container field, I don't think there's anything particularly unusual about the data entity, and a little reading suggested that an absence of relationship cardinality may be at fault. If I remove the related data source, build & db sync both complete successfully, and I can see the view in SSMS, but it's not visible in the Data Management workspace, even after refreshing the entity list. Investigations continue...

[Edit: typo]

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (1)

Report

RE: Export/Import from system documentation tables

Hi Rich,

You can first add the RuleQuery field like any other field on the data entity and regenerate the staging table. When you then open the staging table, copy the name of the field and delete it. Create a new string field and paste the name of the RuleQuery field. Set the string size to 'memo'. Then you can build the model/solution and synchronize the database.

This procedure will export and import the queries; no additional coding required. You can review the details on standard entities where also a query is supported, like WHSWarehouseLocationDirectiveV2Entity.

Was this reply helpful? Yes No
Rich 320 on at

Like (0)

Report

RE: Export/Import from system documentation tables

Thanks André: I was hoping to progress my efforts and post further results myself, but as ever, your guidance is appreciated. Having changed the staging table as you suggest, it looks like I've still missed something. I have an export project containing (1) the custom data entity for the security rules and (2) the WHSWarehouseLocationDirectiveV2Entity. When I export the data package, the WHSWarehouseLocationDirectiveV2Entity spreadsheet shows a DirectiveQueryFileName column with a GUID pointing to a file that is vaguely readable in a text editor of choice: there are a bunch of control characters, and I certainly wouldn't fancy trying to create queries from scratch in this format.

Nonetheless, it's better than my custom entity, because that still shows the encoded value directly in the spreadsheet. I can't spot any obvious differences in the properties of either the field (both custom entity & Warehouse entity have it as a Container field, for example), the data entity (Entity category property, Subscriber access level are the same), or the staging table field (I've set the Asset Classification to Customer Content, though it isn't apparent to me why that would be relevant). Looking at the WHSWarehouseLocationDirectiveV2Entity, I can see there are a couple of methods that work with the query field, but they look like they're only relevant to inserts, so don't see anything there to disagree with your statement "no additional coding required" That said, it seems obvious that I have to change something in how the field's handled: any further suggestions would be welcome.

As an aside, querying the views & the staging tables directly in SQL Studio Management Studio, they both look pretty similar (i.e. the views both have the query columns starting 0x07FD, and the staging tables both omit the leading 0x). There's another thread discussing virtual fields for containers which is what I planned to investigate, but I'd have thought that would require the field handling differently for import & export, which doesn't feel feasible.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report
RE: Export/Import from system documentation tables

Hi Rich,

I forgot about the convert to file option. I did do a similar action several years ago when we didn't have the V2 version of the location directives entity. The contents of the query can be saved into the memo field or if you want to have it written to a resource file, you can add a field containing the filename and a method on the staging table. The code for the location directive is:

///
/// When exporting this DMF data entity, Directive Query will be packaged into a separate file. /// Queries are sometimes malformed due to truncation if translated directly into a string. ///
/// A container used to separate the directiveQuery field into a file. public static container getFieldsToBeConvertedToFile() { // Entity fieldname, Staging table field name, StagingFieldName FileSuffix return [[fieldstr(WHSWarehouseLocationDirectiveV2Entity, DirectiveQuery), fieldstr(WHSWarehouseLocationDirectiveV2Staging, DirectiveQuery), fieldstr(WHSWarehouseLocationDirectiveV2Staging, DirectiveQueryFileName)]]; }

Was this reply helpful? Yes No

Rich

320 on at

Like (0)

Report

RE: Export/Import from system documentation tables

Thanks for pointing to the staging table where I was focused on the data entity. I've looked at that method & a blog post discussing import/export of container fields, but I still haven't identified the way the query is packed in SecurityRoleAssignmentRule. I can populate a file using the getFieldsToBeConvertedToFile method on a SecurityRoleAssignmentRuleStaging table, but the value I'm seeing isn't human-readable like the query is through the UI: it looks like a more-or-less random sequence of characters. Methods I've tried to decode the packed string:

X++ method (in brief: ruleTable is local variable instance of the documentation table)	Result
BinData::dataToString(ruleTable.RuleQuery)	non-readable string
bindata.base64Encode(ruleTable.RuleQuery)	non-readable string
con2base64str(ruleTable.RuleQuery)	non-readable string
con2Str(ruleTable.RuleQuery)	Runtime conversion error
System.Convert::FromBase64String(ruleTable.RuleQuery)	Return type is a byte array, which I'm still working on converting to a string

The last still holds some potential, but I'm being asked to prioritise other work, so may be a while before I can provide an update.

Was this reply helpful? Yes No

Verified answer

André Arnaud de Cal... 299,071 Super User 2025 Season 2 on at

Like (0)

Report

RE: Export/Import from system documentation tables

Hi Rich,

The container will not be decoded to something readable. The purpose is to move the query from one environment to another.

If you need to have a human readable SQL statement, you can create a virtual field and use x++ coding to first activate a Query object from the query value in the field and then use the command getSQLStatement().

Was this reply helpful? Yes No
Rich 320 on at

Like (1)

Report

RE: Export/Import from system documentation tables

You can first add the RuleQuery field like any other field on the data entity and regenerate the staging table. When you then open the staging table, copy the name of the field and delete it. Create a new string field and paste the name of the RuleQuery field. Set the string size to 'memo'. Then you can build the model/solution and synchronize the database.

For the minimum viable approach to this requirement, this is what I've done for the moment: it's probably expecting too much to be able to modify the queries outside of F&O, but I'm hoping to return to this at some point & give users a "preview" in the exported file.

Was this reply helpful? Yes No