Search

Hello and welcome to the Developer Ecosystem Fall News! This autumn we've had a lot of online and offline activities in the InterSystems Developer Ecosystem. In case you missed something, we've prepared for you a selection of the hottest news and topics to catch up on! News 🔥 Remarkable Milestones: 16K...18K...8M... on InterSystems Developer Community! 🔥 💡 InterSystems Ideas News #8 and #9 📝 Global Masters: new badges for Ideas on InterSystems Ideas Portal 📝 Global Masters Credly Badges - new way to share your accomplishment! 📝 Earn a Learning Badge! 📝 InterSystems Supported Platforms Update Q4-2023 ♨ Alert: Failed login handling and OAuth2 client errors ♨ Red Hat Insights alerts now available for InterSystems IRIS ♨ InterSystems IRIS Minimum Supported CPU Models 🛑 Deprecation of InterSystems IRIS NLP, formerly known as iKnow 🛑 Deprecation of the InterSystems Cloud Manager (ICM) 🛑 End of support for CentOS 🛑 Deprecation of support for VxFS 🛑 Deprecation of InterSystems System Alerting and Monitoring (SAM) 🛑 InterSystems Studio deprecation news Contests & Events InterSystems Python Programming Contest 2023 Contest Announcement Kick-off Webinar Technology Bonuses Time to Vote Technical Bonuses Results Winners Announcement Meetup with Winners InterSystems Java Programming Contest 2023 Contest Announcement Kick-off Webinar Technology Bonuses Time to Vote Technical Bonuses Results Winners Announcement Meetup with Winners Water Cooler Talk Announcement Suggest Topics [Water Cooler Talk] FHIR, the holy grail of interoperability and EHR, or just another brick in the wall? ⏯️ [Webinar in Hebrew] Leveraging Embedded Python ⏯️ [Webinar in Hebrew] Advanced JSON & DocDB ⏯️ [Webinar] Developing an AI Powered IRIS Application, in Python ⏯️ [Webinar] GitOps using the InterSystems Kubernetes Operator ⏯️ [Webinar] Analytics Capabilities using InterSystems IRIS 🧑‍🤝‍🧑 [Roundtable] InterSystems Developer Community Roundtable - September 28 2023 🧑‍🤝‍🧑 [Roundtable] InterSystems Developer Community Roundtable - November 30 2023 ☕️ [Meetup] Second Developers Meeting in Barcelona ☕️ [Meetup] InterSystems Japan Developer Community Meetup in Osaka 👨‍💻 [Hackathon] InterSystems at HackMIT 2023 👨‍💻 [Hackathon] Join InterSystems at the European Healthcare Hackathon in Prague 👨‍💻 [Hackathon] Get Inspired: Winners of InterSystems IntegratedML challenge at Cal Hacks hackathon 2023 🏅 [Contest] 2nd Tech Article Contest on Chinese Developer Community 🏅 [Contest] The 1st InterSystems Japan Technical Writing Contest! Latest Releases ⬇️ Developer Community Release, September 2023 ⬇️ Developer Community Release, November 2023 ⬇️ InterSystems IRIS, IRIS for Health, & HealthShare Health Connect 2023.3 developer previews Preview 1 Preview 2 Preview 3 Preview 4 ⬇️ Maintenance Releases 2022.1.4 and 2023.1.2 of InterSystems IRIS, IRIS for Health, & HealthShare Health Connect are now available ⬇️ InterSystems announces General Availability of InterSystems IRIS, InterSystems IRIS for Health, & InterSystems Studio 2023.3 Best Practices & Key Questions 🔥 Best Practices of Autumn 2023 Continuous Delivery of your InterSystems solution using GitLab - Part VII: CD using containers IRIS Data to Google Big Query - InterSystems Cloud SQL via Dataflow How to develop in Tableau and PowerBI LangChain on InterSystems PDF documentation InterSystems Embedded Python with Pandas - Part 1 OAuth2 and Basic Authentication, Authorization AND Auditing by code from Web Application 5 useful SQL functions to take your SQL skills to the next level Using JSON in IRIS LangChain fixed the SQL for me ChatGPT to create message transformations for you? Improvements to how IRIS classes are generated and called How to shrink the IRISTemp database Initializing InterSystems IRIS Docker Image with Python ❓ Key Questions of Spring 2023: September, October, November People and Companies to Know About 🌟 Global Masters of Fall 2023: September, October, November Job Opportunities 💼 InterSystems Application Developer 💼 Are you looking for a role as a InterSystems Ensemble/IRIS Developer And more ❗ How to create a job ad on Developer Community ❗ Groups and tags on DC explained ❗ More about Groups and Tags on DC So... Here is our take on the most interesting and important things! What were your highlights from this past season? Share them in the comments section and let's remember the fun we've had! Well done @Anastasia.Dyubaylo - I love how the content is grouped for easy exploration. Keep up the great work everyone :) Wow, lots of events - online and offline this season! As always, thanks for your continued support, Ben! :)

Hi Community, We have some exciting news! It's time for the next InterSystems technical article writing competition! ✍️ Technical Article Contest: InterSystems IRIS Tutorials ✍️ Write an article that can be considered a tutorial for InterSystems IRIS programmers of any level: beginner / middle / senior from February 19th to March 17th. We've extended the submission deadline till the 24th of March! 🎁 Prizes for everyone: A special prize for each author participating in the competition! Prizes 1. Everyone is a winner in the Tech Article Contest! Any member who writes an article during the competition period will receive a special prize: 🎁 Terra Thread Fairtrade Waist Pack 2. Expert Awards – articles will be judged by InterSystems experts: 🥇 1st place: iPad10th generation 🥈 2nd place: Beats Fit Pro True Wireless Earbuds 🥉 3rd place: Amazon Kindle Paperwhite Signature Edition (32 GB) Or as an alternative, any winner can choose a prize from a lower tier than his own. 3. Developer Community Award – article with the most likes: 🎁 Amazon Kindle Paperwhite Signature Edition (32 GB) Note: The author can only be awarded once per category (in total, the author will win two prizes: one for Expert and one for the Community) In the event of a tie, the number of votes of the experts for the tied articles will be considered a tie-breaking criterion. Who can participate? Any Developer Community member, except for InterSystems employees. Create an account! Contest period 📝 February 19th to March 24th: Publication of articles. 🗳️ March 25th to March 31st: Voting time. 🏅 April 1st: Winners announcement. Publish an article(s) throughout this period. DC members can vote for published articles with Likes – votes in the Community award. Note: The sooner you publish the article(s), the more time you will have to collect both Expert & Community votes. What are the requirements? ❗️ Any article written during the contest period and satisfying the requirements below will automatically* enter the competition: The article must be a tutorial** on the InterSystems IRIS topic. It can be either for beginners, middle or senior developers. The article must be in English (incl. inserting code, screenshots, etc.). The article must be 100% new (it can be a continuation of an existing article not in the contest). The article cannot be a translation of an article already published in other communities. The article should contain only correct and reliable information about InterSystems technology. The article has to contain the Tutorial tag. Article size: 400 words minimum (links and code are not counted towards the word limit). Articles on the same topic but with dissimilar examples from different authors are allowed. * Our experts will moderate articles. Only valid content will be eligible to enter the contest. ** Tutorials provide step-by-step instructions for a developer to complete a specific task or set of tasks. 🎯 EXTRA BONUSES This time, we've decided to add additional bonuses that will help you to win the prize! Please welcome: Bonus Nominal Details Topic bonus 5 If your article is on the topic from the list of proposed topics (listed below), you will receive a bonus of 5 Expert votes. Video bonus 3 Besides publishing the article, make an explanatory video. Discussion bonus 1 Article with the most useful discussion, as decided by InterSystems experts. Only 1 article will get this bonus. Translation bonus 2 Publish a translation of your article on any of the regional Communities. Learn more. Note: Only once per article. New participant bonus 3 If you haven't participated in the previous contests, your article(s) will get 3 Expert votes. Proposed topics Here's a list of proposed topics that will give your article extra bonuses: ✔️ Using AI/ML/GenAI✔️ Using Cloud SQL✔️ Using VSCode✔️ Using Kubernetes✔️ Using FHIR SQL Builder Note: Articles on the same topic from different authors are allowed. ➡️ Join InterSystems Discord to chat about the rules, topics & bonuses. It's time to show off your writing skills! Good luck ✨ Important note: Delivery of prizes varies by country and may not be possible for some of them. A list of countries with restrictions can be requested from @Liubka.Zelenskaia Hello Devs. Today is the first day of the Tech Article Contest! Warm up your keyboards, write an article, and join the contest! We are looking forward to your ideas and tutorials! Hey Developers! Five days of the contest is already have passed. Don't forget to check the bonuses for the article, so you can earn more points and win! Write an article, join the contest, and win the prizes! Hi Devs. What's great news! 🤩 The first new article has appeared on the Contest page: The bewitched line terminator by @Robert.Cemper1003 Waiting for other interesting articles 😎 Hey Devs! One more new article has been added to the contest! Insights from unstructured data using SQL Text Search by @Veerarajan.Karunanithi9493 Hi Developers, A new article has appeared on the Contest page: How to send messages to Microsoft Teams by @Francisco.López1549 And it won't be the last, I threaten with more articles, hehehe Hey Community! Another articles have been added to the Contest page! Сheck them out😉 InterSystems IRIS® CloudSQL Metrics to Google Cloud Monitoring by @sween Connecting to DynamoDB Using Embedded Python: A Tutorial for Using Boto3 and ObjectScript to Write to DynamoDB by Andre Ribera How to create a killer presentation using Open Exchange by @Yuri.Gomes Hey Devs! This is the last week of publication, but you still have time to publish articles, so hurry up! By the way, another article has been added to the contest: InterSystems VSCode ObjectScript Extension Pack in glance by @Muhammad.Waseem Hi Kurro,We're waiting for more cool articles😎 Coming soon, I was doing the video for the last article.. Enjoy it You asked – we did it! ❗️ The deadline for the contest has been pushed back for a week to March 24th! One more week to write an article and join our competition – don't miss your chance to win 😉 Niiiice, more time to come up with another article 😁 Hi Community! Five more articles have been added to the Contest page: 1. Generating meaningful test data using Gemin by @Iryna Mykhailova2. Connecting to InterSystems IRIS in VS Code using SQLTool by @Laurel.James 3. Orchestrating Secure Management Access in InterSystems IRIS with AWS EKS and ALB by @Roy.Leonov 4. Tutorial: Adding OpenAI to Interoperability Production by @Maria.Nesterenko 5. How to start Using VsCode by @Heloisa.Paiva Developers, don't forget to check Extra Bonuses in the Tech Article Contest 2024. Hi participants! We're excited to announce that we've extended the competition for an additional week, now the submission deadline is March 24th! Plus, we've added another week after that for the jury voting process. Stay tuned as the results will be revealed on April 1st. Keep those articles coming, and let's make this competition even more exciting! Hey everyone, There are only three days left until the end of the publication period! And here are 4 new articles that have been added to the Contest page: Creating Unit Tests in ObjectScript for HL7 pipelines using %UnitTest class by @Chi.Nguyen-RettigGetting data from InterSystems IRIS CloudSQL using xDBC by @Iryna Mykhailova Query external SQL database to use within a Production by @Ewan.WhyteHow to create Data Lookup Tables using the SQL Data Import Wizard by @Victoria.Castillo2990 How do I enter the competition? This is what I have done: 1. https://community.intersystems.com/contests/5 2. "Join the contest"I get to this url: https://community.intersystems.com/node/add/post?edit%5Bfield_post_type%5D=791 With the button "Article" highlighted Is this the correct entry way?What do I put into the "Group"? Hey Devs! Today is the last day of publication, complete your article and publish it! One more article has been added to the contest, check it out: Tasks & Tricks: The Dev's Fish & Chips by @Daniel.Aguilar Hey Community! 🎉 Thank you to all the incredible members of our community who participated in the Technical Article Contest: InterSystems IRIS Tutorials! We received a total of 21 amazing articles, each showcasing your expertise and creativity. Now that the submission period is over, it's time for our esteemed judges to have their say. They will have one week to carefully evaluate each submission and select the winners. During this week, all the contestants can gather bonus points and likes for your articles. So, keep engaging with the community, sharing your insights, and encouraging others to check out your work. 🏆 Best of luck to all the participants, and stay tuned for the announcement of the winners on April 1st!

Hey Community, You've probably already heard about Discord and many of you are already chatting there. And now we invite you to become closer to the world of InterSystems technology and join the social club of our developers! Use a super-fast way to communicate with each other: 💥 InterSystems Developers Discord Channel 💥 On DC Discord Server, you'll find many InterSystems-related channels and discussions, as well as channels for everyday life conversations. Just join our club to get closer to the world of InterSystems developers! See you on the InterSystems Developers Discord ✌️ If you have any suggestions for improving our Discord Server or want to create a new channel on a specific topic – please feel free to share your thoughts in the comments. Hey everyone, To stay in touch, let's continue our tech talks on InterSystems Developers Discord Server! See you there ✌️

Hi Developers, Learn about the InterSystems IRIS FHIR Server, the fully managed FHIR data solution that empowers FHIR application developers to focus on building life-changing healthcare applications: ⏯ What is the InterSystems IRIS FHIR Accelerator Service? Subscribe to InterSystems Developers YouTube and stay tuned!

Hi contestants! We've introduced a set of bonuses for the projects for the Interoperability Contest 2021! Here are projects that scored it: Project Basic Auth Bearer/JWT OAuth Authorization Auditing Encryption Docker ZPM Online Demo Code Quality Article on DC Video on YouTube Total Bonus Nominal 2 3 5 2 2 2 2 2 3 1 2 3 29 appmsw-forbid-old-passwd 2 2 2 1 2 9 isc-apptools-lockdown 2 - - 1 2 5 passwords-tool 2 2 1 2 7 API Security Mediator 2 2 2 2 2 3 1 6 3 23 Audit Mediator 2 2 2 1 4 3 14 iris-disguise 2 2 1 4 3 12 iris-saml-example 5 2 2 2 3 1 2 17 Server Manager 3.0 Preview 2 4 6 appmsw-dbdeploy 2 2 1 2 7 Data_APP_Security 2 5 2 2 2 2 3 1 4 3 26 IRIS Middlewares 2 1 3 TimeTracking-workers 2 2 1 5 zap-api-scan-sample 2 1 4 3 10 https-rest-api 2 2 Please apply with your comments here in the posts or in Discord. Bonuses are subject to change upon the improvements or your requests if we missed something! Good luck in the contest! Hi Mr. Evgeny Shvarov Thanks for sharing Please note that Data_APP_Security app already have article on DC Thanks I claim 2 points to audit mediator app, because the article https://pt.community.intersystems.com/post/aproveitando-o-banco-de-dados-de-auditoria I claim 2 points to security mediator, because the article https://pt.community.intersystems.com/post/o-poder-do-xdata-aplicado-%C3%A0-seguran%C3%A7a-da-api I request bonus to online app (SecurityMediator) http://ymservices.tech:52773/crud/ I request bonus to use Audit into the app SecurityMediator. Evidence: https://github.com/yurimarx/iris-api-security-mediator/blob/a3fa1863cd8318ace38d554ef3956694e6b28ef4/src/dc/SecurityMediator/SecurityMediator.cls#L66 I request bonus to the use of basic auth, my app is REST API that's requires basic auth. If you access my app online, you have to inform user and password. (http://ymservices.tech:52773/crud/). Evidence: https://github.com/yurimarx/iris-api-security-mediator/blob/a3fa1863cd8318ace38d554ef3956694e6b28ef4/module.xml#L21 I just posted another article about my Server Manager 3 entry: https://community.intersystems.com/post/server-manager-now-showcasing-vs-codes-new-support-pre-release-extensions I didn't know that translated articles give points, so if it is allowed there is my article in portuguese https://pt.community.intersystems.com/post/anonimiza%C3%A7%C3%A3o-de-dados-apresentando-iris-disguise Hi, I am requesting points for translated article in Spanish for Data_APP_Security app https://es.community.intersystems.com/post/c%C3%B3mo-crear-usuarios-conceder-privilegios-habilitardeshabilitar-y-autentificarinvalidar Thanks Hi,Add video on YouTube https://www.youtube.com/watch?v=Ofyf0IdakeYThanks I claim more 2 points (security mediator) for my new article https://community.intersystems.com/post/objectscript-rest-api-cookbook Hey Mr. Evgeny Shvarov Please add points for the `quality code` for: # appmsw-forbid-old-passwd +1 # isc-apptools-lockdown +1 I also ask you to add +2 for `appmsw-forbid-old-passwd` The program that checks the correctness of the password entered by the user is already definitely an authorization component. Please add points for the quality of the code for `appmsw-dbdeploy`all errors are due to the fact that the code quality program cannot work with% ZPM.https://community.objectscriptquality.com/dashboard?id=intersystems_iris_community%2Fappmsw-dbdeploy Hi Mr. Evgeny Shvarov Please note that I have added OAuth2 support along with Article for Data_APP_Security appThanks Hi @Evgeny.Shvarov We are here to request bonus points to Gryffindor ZAP API Scan Sample zap api scan sample 1 article in English: https://community.intersystems.com/post/why-how-whats-zap-api-scan-sample and 1 article in Portuguese: https://pt.community.intersystems.com/post/por-que-como-o-que-%C3%A9-zap-api-scan-sample  :)

Hi Developers! Here're the technology bonuses for the InterSystems Python Contest 2022 that will give you extra points in the voting: Embedded Python - 4 Python Native API - 3 Python Pex - 3 NoObjectScriptLine - 5 Questionnaire - 2 Docker container usage - 2 ZPM Package deployment - 2 Online Demo - 2 First Article on Developer Community - 2 Second Article On DC - 1 Video on YouTube - 3 See the details below. Embedded Python - 4 points Use Embedded Python in your application and collect 4 extra points. You'll need at least InterSystems IRIS 2021.2 for it. Python Native API - 3 points InterSystems IRIS introduces a Python Native API library that helps to interact with IRIS from python. Use it and collect 3 extra points for your application. Python Pex - 3 points InterSystems IRIS has Python Pex module that provides the option to develop InterSystems Interoperability productions from Python. Use it and collect 3 extra points for your application. It's OK also to use alternative python.pex wheel introduced by Guillaume Ronguier. NoObjectScriptLine - 5 points We are introducing several python APIs in this contest! And the bonus is yet another challenge: build your python solution with InterSystems IRIS and try to avoid using even a line of ObjectScript! IRIS Classes with only Embedded Python methods are OK though. Do it and collect 5 bonus points more! Questionnaire - 2 Share your feedback in this questionnaire and collect 2 extra points! Docker container usage - 2 points The application gets a 'Docker container' bonus if it uses InterSystems IRIS running in a docker container. Here is the simplest template to start from. ZPM Package deployment - 2 points You can collect the bonus if you build and publish the ZPM(InterSystems Package Manager) package for your Full-Stack application so it could be deployed with: zpm "install your-multi-model-solution" command on IRIS with ZPM client installed. ZPM client. Documentation. Online Demo of your project - 2 pointsCollect 2 more bonus points if you provision your project to the cloud as an online demo. You can do it on your own or you can use this template - here is an Example. Here is the video on how to use it. Article on Developer Community - 2 points Post an article on Developer Community that describes the features of your project and collect 2 points for the article. The Second article on Developer Community - 1 point You can collect one more bonus point for the second article or the translation regarding the application. The 3rd and more will not bring more points but the attention will all be yours. Video on YouTube - 3 points Make the Youtube video that demonstrates your product in action and collect 3 bonus points per each. The list of bonuses is subject to change. Stay tuned! Good luck in the competition!

Hi Developers, We are excited to announce that InterSystems will be a Platinum sponsor of Hacking Health Camp - big professional HealthTech conference and hackathon, which will take place in-person in Strasbourg, France on March 25-27, 2022. On March 8, 2022 @Guillaume.Rongier7183 will carry out a webinar "How FHIR can help you develop your health applications" (Webinar language: French). Register here. At the hackathon, InterSystems will have a challenge for hackers on using InterSystems FHIR cloud services. Learn more on official website and join us in-person in France!

Hey Developers, New video is already on InterSystems Developers YouTube channel: ⏯ What is the InterSystems IRIS FHIR Server Learn about the InterSystems IRIS® FHIR Server, the fully managed FHIR data solution that empowers FHIR application developers to focus on building life-changing healthcare applications. Stay tuned!

Hi Community, we continue to gather feedback about Data Loading & Packaging. In particular, we're interested in hearing your impressions on some of the new capabilities added with InterSystems IRIS 2021.2 We'll be using this feedback to improve InterSystems IRIS, so please feel free to share any details and feedback: >> Link to the survey (14 questions, 5 min) << Note: this is the same survey we already published during the contest, on Global Masters and Discord. If you already participated, you don't have to do it again. Thank you for the feedback!

Hey Coommunity, Learn about InterSystems globals, the data model native to InterSystems IRIS® data platform that makes data storing efficient and provides the flexibility your data needs: ⏯ Flexible Data Modeling with InterSystems Globals Don't miss the latest videos for InterSystems developers on DC YouTube!

Hey Community, In this video, you will learn how to use the Data Transformation Wizard in the Management Portal of InterSystems IRIS® data platform to convert messages to other formats: ⏯ Transforming Healthcare Messages in InterSystems Integrations Subscribe to the InterSystems Developers YouTube channel and stay tuned!

Hey Developers, See how you can develop a .NET application and connect to InterSystems IRIS® data platform using one or more APIs: ⏯ Using NET to Connect to InterSystems IRIS Subscribe to the InterSystems Developers YouTube channel and stay tuned!

Hey Developers, Develop a Java application that connects to InterSystems IRIS® data platform relationally, natively, or with objects: ⏯ Using Java to Connect to InterSystems IRIS Enjoy watching on the InterSystems Developers YouTube channel and stay tuned!

An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community. As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be: Class DC.Demo.Node Extends %Persistent { Property Parent As DC.Demo.Node; Property Name As %String [ Required ]; Index ParentAndName On (Parent, Name) [ Unique ]; Storage Default { <Data name="NodeDefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> <Value name="2"> <Value>Parent</Value> </Value> <Value name="3"> <Value>Name</Value> </Value> </Data> <DataLocation>^DC.Demo.NodeD</DataLocation> <DefaultData>NodeDefaultData</DefaultData> <IdLocation>^DC.Demo.NodeD</IdLocation> <IndexLocation>^DC.Demo.NodeI</IndexLocation> <StreamLocation>^DC.Demo.NodeS</StreamLocation> <Type>%Storage.Persistent</Type> } } And there we go! But there's a catch: as it stands, this implementation allows multiple root nodes to have the same name. Why? Because Parent is not (and shouldn't be) a required property, and IRIS does not treat null as a distinct value in unique indices. Some databases (e.g., SQL Server) do, but the SQL standard says they're wrong [citation needed; I saw this on StackOverflow somewhere but that doesn't really count - see also @Daniel.Pasco 's comment below on this and the distinction between indices and constraints]. The way to get around this is to define a calculated property that's set to a non-null value if the referenced property is null, then put the unique index on that property. For example: Property Parent As DC.Demo.Node; Property Name As %String [ Required ]; Property ParentOrNUL As %String [ Calculated, Required, SqlComputeCode = {Set {*} = $Case({Parent},"":$c(0),:{Parent})}, SqlComputed ]; Index ParentAndName On (ParentOrNUL, Name) [ Unique ]; This also allows you to pass $c(0) to ParentAndNameOpen/Delete/Exists to identify a root node uniquely by parent (there isn't one) and name. As a motivating example where this behavior is very helpful, see https://github.com/intersystems/isc-rest/blob/main/cls/_pkg/isc/rest/resourceMap.cls. Many rows can have the same set of values for two fields (DispatchOrResourceClass and ResourceName), but we want at most one of them to treated as the "default", and a unique index works perfectly to enforce this if we say the "default" flag can be set to either 1 or null then put a unique index on it and the two other fields. Maybe I'm missing something, but, beyond how nulls are treated, if you want parent to be unique within this definition you must define a unique index on parent (alone). The index you have defined only guarantees that the combination (parent, name) will be unique. Even if you declare the property as required it wouldn't still solve the uniqueness requirement. parameter INDEXNULLMARKER; Override this parameter value to specify what value should be used as a null marker when a property of the type is used in a subscript of an index map. The default null marker used is -1E14, if none is specfied for the datatype. However %Library.PosixTime and %Library.BigInt datatypes could have values that collate before -1E14, and this means null values would not sort before all non-NULL values. For beauty, I would also use the value of this parameter, for example:Class dc.test Extends %Persistent { Property idp As dc.test; Property idpC As %Integer(INDEXNULLMARKER = "$c(0)") [ Calculated, Private, Required, SqlComputeCode = {s {*}=$s({idp}="":$c(0),1:{idp})}, SqlComputed ]; Property Name As %String [ Required ]; Index iUnq On (idpC, Name) [ Unique ]; ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test(Name,idp)values('n1',1)) w SQLCODE,! ;0 &sql(insert into dc.test(Name,idp)values('n2',1)) w SQLCODE,! ;0 &sql(insert into dc.test(Name,idp)values('n2',1)) w SQLCODE,!! ;-119 &sql(insert into dc.test(Name,idp)values('n1',null)) w SQLCODE,! ;0 &sql(insert into dc.test(Name,idp)values('n2',null)) w SQLCODE,! ;0 &sql(insert into dc.test(Name,idp)values('n2',null)) w SQLCODE,!! ;-119 zw ^dc.testD,^dc.testI } }Output:USER>d ##class(dc.test).Test() 0 0 -119 0 0 -119 ^dc.testD=4 ^dc.testD(1)=$lb("",1,"n1") ^dc.testD(2)=$lb("",1,"n2") ^dc.testD(3)=$lb("","","n1") ^dc.testD(4)=$lb("","","n2") ^dc.testI("iUnq",1," N1",1)="" ^dc.testI("iUnq",1," N2",2)="" ^dc.testI("iUnq",$c(0)," N1",3)="" ^dc.testI("iUnq",$c(0)," N2",4)="" OrProperty idpC As %Integer [ Calculated, Private, Required, SqlComputeCode = {s {*}=$s({idp}="":$$$NULLSubscriptMarker,1:{idp})}, SqlComputed ]; Output:USER>d ##class(dc.test).Test() 0 0 -119 0 0 -119 ^dc.testD=4 ^dc.testD(1)=$lb("",1,"n1") ^dc.testD(2)=$lb("",1,"n2") ^dc.testD(3)=$lb("","","n1") ^dc.testD(4)=$lb("","","n2") ^dc.testI("iUnq",-100000000000000," N1",3)="" ^dc.testI("iUnq",-100000000000000," N2",4)="" ^dc.testI("iUnq",1," N1",1)="" ^dc.testI("iUnq",1," N2",2)="" What about defining a super parent, which is the only node with a NULL parent? In that case every other root node has this node as a parent and filtering is easy (WHERE parent IS NULL filters only the super parent). And there's no need for an additional calculated property in that case. Thank you for pointing this out! I saw this in docs but believe it wouldn't work for object-valued properties. There would still need to be some enforcement of the super parent being the only node with a NULL parent (and the point here is that the unique index wouldn't do that). Also finding all of the top-level nodes (assuming we could have multiple independent trees) would be a slightly more complicated. I have to throw in my opinions and possibly a few facts regarding nulls and unique constraints. IRIS Unique index - this is primarily a syntactical shortcut as it defines not only an index but also a unique constraint on the index key. Most pure SQL implementations don't merge the two concepts and the SQL standard doesn't define indexes. The SQL Standard does define unique constraints. Keep in mind that both IDKEY and PRIMARYKEY are modifiers of a unique constraint (and, in our world, the index defined as IDKEY is also special). There can be at most one index flagged as IDKEY and one that is flagged as PRIMARYKEY. An index can be both PRIMARYKEY and IDKEY. There was once an SQL implementation that defined syntax for both "unique index" and "unique constraint" with different rules. The difference between them was simple - if an index is not fully populated (not all rows in the table appear in the index - we call this a conditional index) then the unique index only checked for uniqueness in the rows represented in the index. A unique constraint applies to all rows. Also keep in mind that an index exists for a singular purpose - to improve the performance of a subset of queries. Any SQL constraint can be expressed as a query. The SQL Standard is a bit inconsistent when it comes to null behavior. In the Framework document there is this definition: A unique constraint specifies one or more columns of the table as unique columns. A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In the Foundation document, there exists two optional features, F291 and F292. These features define a unique predicate (291) and unique null treatment (292). These features appear to provide syntax where the user can define the "distinct-ness" of nulls. Both are optional features, both are relatively recent (2003? 2008?). The rule when these features are not supported is left to the implementor. IRIS is consistent with the Framework document statement - all constraints are enforced on non-null keys only. A "null" key is defined as a key in which any key column value is null. Thank you Dan! The index/constraint distinction and SQL standard context are particularly helpful facts for this discussion. :) the best part of this article is "IRIS does not treat null as a distinct value in unique indices"