Filtering on morepage field (Checkbox) in SearchEngine

Hi All,

I am trying to make a script that runs throug documents of certain type, and where a checkbox is not checked.

I have made a script and can list the documents but when I try to add the checkbox in the filter is doesn't work.

I have looked here at the forum and found the way I am using now, but I cant get it to work,
I hope some of you guys can help a little.

I have tried with both 0 and dalse but none of them work

thanks :)

RE: Filtering on morepage field (Checkbox) in SearchEngine

Hi Allan,

 

UDEF checkboxes will have either the value of 0 or 1.

However, if no UDEF value has been filled on the document, there wont be any corresponding line for the record in the uddocsmall table.

This means, that if your default value of the checkbox is set to unticked - a new record won't be created, and therefor you won't be able to search for the unticked value.

But if your default value of the checkbox is set to ticked, a new record is created - and will update accordingly when you untick the checkbox.

This can be a bit tricky. Especially if you only use UDEF's  of type "checkbox", with the default value "unticked".

 

Have you verified that the checkbox actually is placed in the long05 field?

I tried setting this up in my demo, and got it to work by using the following criteria:

SearchEngine se;
se.addFields("appointment", "document_id,appointment_id,status,type,updated");
se.addField("document.header");
se.addField("document.userdef_id.long06");
se.addCriteria("document.userdef_id.long06", "Equals", "1");
se.addCriteria("appointment.type", "OperatorEquals", "4");

se.execute();
print("Header: "+se.getField("document.header") + " Checkbox clicked: " + se.getField("document.userdef_id.long06"));
By: Petter Näslund 31 Mar 2021

RE: Filtering on morepage field (Checkbox) in SearchEngine

Hi,

As Petter mentions, the behaviour with missing udef-records is a common "gotcha" that most CRMScript-developers get exposed to sooner or later. :)

The simple workaround is to secure that the entity connected to the related udef-table has at least one udef-field that contains a default value. That way a corresponding related record is always created in the udef-table when there is a new record in the entity table.

Do have in mind that you don't have to set a default value for the field that you actually is targeting. You could use any entity-related udef-field for that. If needed, you could also create a dummy-udef-field whose only purpose is to set a default value, which leads to an implicit creation of the udef-record. You can set this field to have no header name and visual settings that makes it not to show in the GUI.

If I'm not remembering it totally wrong, SuperOffice has a built in optimization feature that excludes fields from search results when the udef field width is set to "0". Also kind of a "gotcha" if you don't know about it and believes that a visual setting shouldn't affect API functions could be believed to return this field. This is just to inform that a "dummy-field" doesn't necessarily needs to affect the "architecture", even if it could be seen as a bit ugly architectural-wise. :)

Do have in mind that only entity-records created AFTER you have enabled an udef-field to always have a default value, is ensured to have a corresponding udef-record, I think at least. Most likely you will have to run some kind of update-script for all former existing entity-records to make sure that they all have a corresponding udef-record. Most likely by setting the same udef-field to the same default value for all entity-records that don't have a corresponding udef-record.

But, how do you find a record based on a field that doesn't exist? I suppose you could create a diff-list based on all existing entity-ID's minus the list of entity-ID's with the default udef-field set (which could be found as it then has a corresponding record in the udef-table).

Best Practices

Avoid using direct relations to udef-database-fields

I need to point out that it is an error prone bad practice to use search engines that uses direct relations to specific udef-fields. At least if you don't use a self built framework that dynamically returns the correct udef-field database name based on the ProgId.

Why? Because the udef-field could switch place to another udef-database-field. Activities that could trigger such a relocation of udef-fields is for example when enabling indexing on a specific udef-field (especially the one you are targeting). As only four udef-fields per entity-udef-table can be indexed (if I remember correctly), I think the four first fields for each field type in the udef-table are allocated for for hosting indexed fields.

As later changes in the udef-field configuration could affect your scripts, maybe without you knowing about it, you can't really have control over this risk. So it's better to not built code that could fail in this way.

The problem is even worse if you are writing to such udef-fields, as you then could start writing to the wrong field.

ProgID's

ProgId's do exist for this exact purpose, to be able to have something static that can return the data from a database field that is dynamically placed. A best practice that I use myself, is to always use a more descriptive ProgID-name than the name given by default, which is likely "SuperOffce:n" where n is a number.

I often use the structure "<EnglishEntityName><DescriptiveFieldName>:1".

The ":n"-part shouldn't really be needed I think. But there was a bug in one of the SO 8.x-versions, where the ProgId field name validator in the udef-config-editor, failed names not having the part ":n". This created some problems when upgrading to this SO version, as I had excluded this part in the ProgId-names in an implementation made in a former version of SuperOffice. So, even if I think that bug got fixed in a later 8.x release, I still add ":1" at the end of the ProgID-name, just as a safety measure.

Personally, I find it a bad practice to use the default ProgID-names for customizations in scripts, macros or integrations. This as descriptive names give such more self explanatory solutions. But, keep in mind that if you are to change the ProgID's of existing udef fields, ensure that these names aren't used in any other already existing customizations or integrations.

Alternatives

So, what are the alternatives?

ProgId-Lookup-Framework

One is to use a framework that returns the correct current udef-database-field based on the udef-fields "ProgId". One pro might be that you then still are able to use a SearchEngine for the query. The con is that you need to use an extra query that is based on a "complex" business rule for how to return the correct udef-table-field-ID based on the used ProgId.

These features are all built in into the SDK/API, so it might be error prone to build this kind of queries yourself, especially if the business rules are to be changed in the future, which is likely for the udef-fields.

Archive Queries

A much more robust and safe way of executing queries that contains both joined tables as well as udef-fields, is to use a Archive-based query instead. These queries also support using ProgID's instead of direct udef-table-field-names.

Historically these queries have had a quite cumbersome syntax, but lately additions has been made to make it possible to use more or less the same SQL-like syntax as used for writing queries in our REST-API. So now it seems much easier, which is nice!

I will not take the time to give any examples in this post, as there already are several posts, articles and document-pages that describes this. There might be less examples of the later syntax, but with some trial and error, you will probably figure it out. :)

Do note, that there are several ArchiveMethods that could be used for different use cases and one specific that I have in mind that can be used for more generic queries. There are several versions of that ArchiveMethod and I think you need to use the last versioned one to be able to use the newer syntax mentioned above.

Also keep in mind that these methods doesn't use the actual database field names, they are using the corresponding names in the API, which is more or less the same, but not always. Sometimes case can differ, etc. So you have to look up the field names to use for the corresponding entities in the SDK.

Below is a list of forum posts, blog articles and SDK-pages that might be useful to understand how to use these ArchiveMethods. They are not listed in a pedagogically order, just in the order added to my research bookmark list. :) But the later links might refer more to the newer syntax. But it is recommended to read the blog article first, to get a better understanding of the basics.

NetServer Archive Providers
https://community.superoffice.com/en/content/content/netserver-sdk/netserver-archive-providers/

Reference
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-Reference.htm

Archive Providers Restriction Types
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-ArchiveProvidersRestrictionTypes.htm

GET Archive/{archiveProviderName}
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/v1Archive_GetArchive_GET.htm

What is a Carrier
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Introduction-WhatisaCarrier-WhatisaCarrier.htm

REST FindAgent - Restriction on Udef can cause IIS to go into melt down
https://community.superoffice.com/en/developer/forum/rooms/topic/superoffice-product-api-group/crm-web-application/rest-findagent-restriction-on-udef-can-cause-iis-to-go-into-melt-down/?userToNotify=10289

How to use ContactEntity.ExtraFields?
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/core-components/how-to-use-contactentityextrafields/

Using archives through ejScript
https://community.superoffice.com/en/blog/using-archives-through-ejscript/

CRMScript - How to get a collection of companies based on a certain "Our Contact" and a certain list item in a UDEF-field?
https://community.superoffice.com/en/developer/forum/rooms/topic/superoffice-product-api-group/customer-service/crmscript-how-to-get-a-collection-of-companies-based-on-a-certain-our-contact-and-a-certain-list-item-in-a-udef-field/

ArchiveRestrictionInfo Class
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/T_SuperOffice_CRM_ArchiveLists_ArchiveRestrictionInfo.htm

Update visible for - not possible for appointments that my user does not own
https://community.superoffice.com/en/developer/forum/rooms/topic/superoffice-product-api-group/customer-service/update-visible-for-not-possible-for-appointments-that-my-user-does-not-own/

ArchiveRestrictions not working as expected
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/web-services/archiverestrictions-not-working-as-expected/?userToNotify=10289

Archive Providers Restriction Types
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-ArchiveProvidersRestrictionTypes.htm

Search
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-WebAPI-REST-Search.htm

GET Archive/{archiveProviderName}
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/v1Archive_GetArchive_GET.htm

Archive agent
https://community.superoffice.com/documentation/SDK/SO.Customer.Service.Support/html/crmscript_archiveagent.htm

NSArchiveListItem[] GetArchiveListByColumns2(String providerName, String columns, String sortOrder, String restrictions, String entities, Integer page, Integer pageSize)
https://community.superoffice.com/documentation/SDK/SO.Customer.Service.Support/html/CRMScript-apireference-Classes-NSArchiveAgent-GetArchiveListByColumns2.htm

How to use the SuperOffice Find API
https://community.superoffice.com/en/content/content/netserver-sdk/netserver-9.x/superoffice-find/

Which archiveprovider is used by dynamic contact selections in 9.2?
https://community.superoffice.com/en/developer/forum/rooms/topic/superoffice-product-api-group/crm-web-application/which-archiveprovider-is-used-by-dynamic-contact-selections-in-92/?userToNotify=10289

How to use the SuperOffice Find API
https://community.superoffice.com/en/content/content/netserver-sdk/netserver-9.x/superoffice-find/

Search for a specific contact by using 'Number2' as search criteria
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/web-services/search-for-a-specific-contact-by-using-number2-as-search-criteria/

Best archive to get Assoiates with email address
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/web-services/best-archive-to-get-assoiates-with-email-address/

Performance in REST API -
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/web-services/performance-in-rest-api-/

Overriding dynamic providers in G9
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/core-components/overriding-dynamic-providers-in-g9/?userToNotify=10289

Add Columns to Archive Providers
https://community.superoffice.com/en/content/content/netserver-sdk/netserver-8.x/add-columns-to-archive-providers/

Extending an archive to include extrafield?
https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/core-components/extending-an-archive-to-include-extrafield/

ProjectDynamicSelectionV2 Archive Provider
https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-ArchiveProviders-ProjectDynamicSelectionV2ArchiveProvider.htm

 

Happy reading! :)

By: Marcus Svenningsson 1 Apr 2021