Select distinct record using x++

(0) Share

Report

Posted on by skd

390

Hey team,

I'm trying to select some files from the DocuRef table but there seems to been multiple copies of the file in the DocuRef table and I want to select the distinct records. How can I do that. Can you please help me modify my select query to select only one file out of the duplicates

My select statement:

  while select * from docuref where docuref.RefTableId == 17136  join docuvalue
                where docuvalue.recid == docuref.ValueRecId
        {
        //my code
        }

Example of multiple files in my table:

I have the same question (0)

All responses (21)

Answers (2)

Blue Wang on at

Like (0)

Report

Hi Skd,

You can add Group by in the select statement which field you want the distinct records.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (1)

Report
Hi skd,

If you have several copies and you don't need them, you need to delete them from document management.

Also, I recommend using DocuRefSearch api instead of direct queries

DocuRefSearch docuRefSearch; DocuRef docuRef; docuRefSearch = DocuRefSearch::newCommon(record_where_documents_are_attached); if (docuRefSearch.next()) { docuRef = docuRefSearch.docuRef(); }

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

As mentioned above, if someone added the same file three times, you should remove the duplicates. The system can't tell that they are "distinct", we can see that each of them points to different DocumentId.

The only way how you could tell that they are distinct is to fetch the file contents from the storage and compare the actual files in x++. But I would not recommend that, instead make sure that you don't upload same files many times.

Was this reply helpful? Yes No
skd 390 on at

Like (0)

Report
Hey Blue Wang,

There is some issue with my code. It gives me an error saying "Stack Trace: Company does not exist" .Please help me with the issue.

while select Name, maxof(ModifiedDateTime) from docuref group by docuref.Name, ModifiedDateTime where docuref.RefTableId == 17136 join docuvalue where docuvalue.RecId == docuref.ValueRecId { XmlDocument xmlDoc = XmlDocument::newFromStream(DocumentManagement::getAttachmentStream(docuRef)); xmlNode = xmlDoc.getNamedElement('FormRunConfigurationSerializer').getNamedElement('OwnerValue'); _owner = xmlNode.text(); info(strFmt("Owner Name: %1", _owner)); ttsbegin; formRunConfiguration.Owner = _owner; formRunConfiguration.Scope = FormRunConfigurationScope::User; formRunConfiguration.User = "Nigel.Farage"; formRunConfiguration.IsDefault = NoYes::Yes ; formRunConfiguration.Company = curExt(); formRunConfiguration.Name = "Default"; formRunConfiguration.Version = 1; formRunConfiguration.FormViewOptionType = FormRunConfigurationFormViewOptionType::Default; formRunConfiguration.insert(); formRunConfigurationOptions.clear(); formRunConfigurationOptions.User = "Nigel.Farage"; formRunConfigurationOptions.Owner = _owner; formRunConfigurationOptions.Enabled = NoYes::Yes; formRunConfigurationOptions.EnabledExplicitPersonalization = UnknownNoYes::Yes; formRunConfigurationOptions.insert(); ttscommit; }

Also when I change my select query to this it works okay. Just the issue is it stops on the duplicate files. (Duplicate files cannot be deleted by me due to some version changes as specified by my company)

while select * from docuref where docuref.RefTableId == 17136 join docuvalue where docuvalue.recid == docuref.ValueRecId { XmlDocument xmlDoc = XmlDocument::newFromStream(DocumentManagement::getAttachmentStream(docuRef)); xmlNode = xmlDoc.getNamedElement('FormRunConfigurationSerializer').getNamedElement('OwnerValue'); _owner = xmlNode.text(); info(strFmt("Owner Name: %1", _owner)); ttsbegin; formRunConfiguration.Owner = _owner; formRunConfiguration.Scope = FormRunConfigurationScope::User; formRunConfiguration.User = "Nigel.Farage"; formRunConfiguration.IsDefault = NoYes::Yes ; formRunConfiguration.Company = curExt(); formRunConfiguration.Name = "Default"; formRunConfiguration.Version = 1; formRunConfiguration.FormViewOptionType = FormRunConfigurationFormViewOptionType::Default; formRunConfiguration.insert(); formRunConfigurationOptions.clear(); formRunConfigurationOptions.User = "Nigel.Farage"; formRunConfigurationOptions.Owner = _owner; formRunConfigurationOptions.Enabled = NoYes::Yes; formRunConfigurationOptions.EnabledExplicitPersonalization = UnknownNoYes::Yes; formRunConfigurationOptions.insert(); ttscommit; }

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

Hi sdk,

when you get exceptions, the first thing to do is to debug it to understand which line of code throws the exception. Could you do that and let us know? Thanks!

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

By the way, you should never hard code table ids in your code. Table ids can be different across different systems.

Use tableNum(MyTable) instead.

Was this reply helpful? Yes No
skd 390 on at

Like (0)

Report
Hey Nikolaos Mäenpää,

I did debug and I get an exception at this line first:

XmlDocument xmlDoc = XmlDocument::newFromStream(DocumentManagement::getAttachmentStream(docuRef));

Also, I added a watch on the select query which gave me this error:

Was this reply helpful? Yes No
skd 390 on at

Like (0)

Report

Yes I will, just till I get my output I'm using hard coded values.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (1)

Report
Hi skd,

The issue is that you are using grouping in the initial query and it doesn't populate all fields for DocuRef that are needed in getAttachmentStream method. You should use firstonly sort query by ModifiedDateTime in reverse order to get the latest record

select firstonly docuref order by ModifiedDateTime desc where docuref.RefTableId == 17136 join docuvalue where docuvalue.RecId == docuref.ValueRecId; if (docuRef.RecId != 0) { ... }

Was this reply helpful? Yes No
skd 390 on at

Like (0)

Report

Hey Sergei Minozhenko,

But this will only select one file right? There are multiple files I want to select, so how can I do that?

Was this reply helpful? Yes No