"View or Edit Data" - Enter Null Foreign Key
Question
Application Type
Service
Service Studio Version
11.14.12 (Build 59645)

Hi all, I have an "Organization" entity that I'd like to represent as a hierarchy to allow for sub-organizations.  So my entity looks like this:

Id [Type=Long Integer, AutoNumber=Yes]

Name [Type=Text, IsMandatory=Yes]

ParentOrganizationId [Type=Organization Identifier, IsMandatory=No]


ParentOrganizationId should contain NULL for a "root" organization, and sub-organizations would contain a "parent's" organization Id to build out the hierarchy.

I'm trying to create some development data using "View or Edit Data".  My "root" record needs to have NULL for the ParentOrganizationId.  But even though ParentOrganizationId is set as not mandatory, the ParentOrganizationId field is showing with a red border asking for a valid Id value.  It doesn't appear to allow me to enter null or anything other than an existing OrganizationId.

Any thoughts on how to do this?

Thank you!

John

Solution

I think you are not doing the right/proper allocation of the type of record. What you should do is, have in the Organization entity a type of organization that will say it is an organization or sub-organization.

Any record on the organization will have an id, so I don't think you should put null or not null in that entity attribute record.

EDIT:

I see that you are trying to put ParentOrganizationId  as the type

But I just have 2 questions, why don't you create an entity for sub-organizations and another for organizations? And Can a sub organization of a organization be in multiple organizations? - If so, from my perspective you need to change a little your entity for this.

If a sub-organization is just in an organization your entity needs to be like this.

And you can always put it like this.


Because 0 is the same as NullIdentifier() when you are comparing

Let me know what you think.

Hi John,

Please Set Delete Rule property as Ignore for ParentOrganizationId Attribute. Then Insert default value for Long Integer as 0.

Hi John,

your design of that entity is perfectly ok.  No need to create different entities for root and sub organisations for the sole reason of a non-mandatory foreign key, and this should be completely unrelated to what type of delete rule you have.

This is a bug in the "View or Edit Data" functionality of Service Studio.  I would suggest you report it to Outsystems as a bug.

this part of the error log is where I think their wrong code is situated :

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()

So, to create test data, you could, following Vinod's suggestion, switch the delete rule to ignore, add your root organisation, and switch the delete rule back to protect, or you could create scaffolding screens to edit your data without tampering with the delete rule.

Dorine

Yes thank you Dorine, this is what I suspected.

I'm going to file a bug report with OS, and scaffold my screens for managing organizations - I would rather leave the delete rule as protect so as not to weaken referential integrity.

Thanks,

John

Solution

I think you are not doing the right/proper allocation of the type of record. What you should do is, have in the Organization entity a type of organization that will say it is an organization or sub-organization.

Any record on the organization will have an id, so I don't think you should put null or not null in that entity attribute record.

EDIT:

I see that you are trying to put ParentOrganizationId  as the type

But I just have 2 questions, why don't you create an entity for sub-organizations and another for organizations? And Can a sub organization of a organization be in multiple organizations? - If so, from my perspective you need to change a little your entity for this.

If a sub-organization is just in an organization your entity needs to be like this.

And you can always put it like this.


Because 0 is the same as NullIdentifier() when you are comparing

Let me know what you think.

Hi John,

Please Set Delete Rule property as Ignore for ParentOrganizationId Attribute. Then Insert default value for Long Integer as 0.

Hi John,

your design of that entity is perfectly ok.  No need to create different entities for root and sub organisations for the sole reason of a non-mandatory foreign key, and this should be completely unrelated to what type of delete rule you have.

This is a bug in the "View or Edit Data" functionality of Service Studio.  I would suggest you report it to Outsystems as a bug.

this part of the error log is where I think their wrong code is situated :

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()

So, to create test data, you could, following Vinod's suggestion, switch the delete rule to ignore, add your root organisation, and switch the delete rule back to protect, or you could create scaffolding screens to edit your data without tampering with the delete rule.

Dorine

Yes thank you Dorine, this is what I suspected.

I'm going to file a bug report with OS, and scaffold my screens for managing organizations - I would rather leave the delete rule as protect so as not to weaken referential integrity.

Thanks,

John

Thank you Marcio and Vinod.  I appreciate both of your suggestions.

To clarify, a sub-organization can only belong to one parent organization - a simple tree:

  • Acme Inc.  [no parent]
    • Sales Division   [parent Id points to Acme]
    • Manufacturing Division   [parent Id points to Acme]
      • Design Team [parent Id points to Manufacturing Division]
  • XYZ Corp.  [no parent]
    • Marketing Division [parent Id points to XYZ]
      • Web Team [parent Id points to Marketing Division]
    • Accounting Division [parent Id points to XYZ]

Other entities (to be developed) would have an OrganizationId attribute and should be able to point to any organization in the tree, e.g. Acme or any one of its divisions/subdivisions.

A variation of Marcio's structure could work.  I would use an Organization entity and an OrganizationOrganization entity connecting two Organizations, one as parent and one as sub.  The issue with this is I'll need to put in several  protections to guard against a user creating an illogical tree (an org pointing to itself, an org pointing to a sub of itself, or an org having more than one parent). 

The structure Vinod shows is what I've got.  Just not sure why the "View or Edit Data" function is requiring a value for ParentOrganizationId even though it is set as not mandatory.  I set Delete Rule to Ignore as Vinod suggests and that gets me past this blocker, but of course this will allow referential integrity to be broken unless I protect from this manually in my update & delete actions (which is fine).

Seems to be a bug in the "View or Edit Data" function that Delete Rule = Protect is overriding IsMandatory = No.  Delete Rule ought to only specify what should happen when attempting to delete an entity with foreign key references.  SQL databases do allow a foreign key to be specified as null, while also allowing the delete rule to be "don't allow", "allow", "set to null", "cascade deletes".  Not sure why "View or Edit Data" function is overriding this.

Thanks,

John

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.