In CRMScript I'm trying to find all companies where my UserDefinedField (date) is in a certain timeframe. From what it seems, dates are stored in the UDXXXsmall table as Longs [1], so im thinking i somehow have to do something with the userdef_id (FK udcontactSmall) table of my contact table [2]. However, when trying a few of the "Long" values, i get just long numbers, and not the date values im after.
I've set the UserDefinedField as Indexed, and tried all long01-04, but there are just numbers which i dont understand what to do with.
I can't make sense of how to get the actual values, and would appreciate some help, please let me know if more information is needed 😊
[1] https://docs.superoffice.com/en/custom-objects/reference/index.html
[2] https://docs.superoffice.com/en/database/tables/contact.html
Alle Svar (2)
Hi Frederic,
I had some UDEF examples in my Person (database name; Contact UI name)
My example was written in the udpersonSmall table as a value in the long03 column.
The value in this field is stored as a Unix timestamp. Example -> the date 01.08.2024 becomes 1722470400.
If I want to search for a date range. Example, from 30.07.2024 to 10.08.2024 that would would be from 1722297600 to 1723248000. (I used ChatGPT to convert, but there are other alternatives as well.)
So my final code could look something like this:
I hope that helps you to move forward with your project.
We recommend you use the NetServer archive providers to perform search operations.
In CRMScript, they are the same as described in the archive provider documentation, however in CRMScript all NetServer types have the NS prefix.
Here is an example to search for all companies where a Date user-defined field value is greater than now.
#setLanguageLevel 4;
// specify the date vaue criteria
DateTime dt;
String dateTime = dt.toString(13,1,true);
String [] values;
values.pushBack(dateTime);
// setup the criteria
NSArchiveRestrictionInfo[] restrictions;
NSArchiveRestrictionInfo res1;
res1.SetName("contactUdef/SuperOffice:3"); // user defined field progid == SuperOffice:3
res1.SetOperator("afterTime");
res1.SetValues(values);
restrictions.pushBack(res1);
// populate if a sort order is desired
NSArchiveOrderByInfo[] order;
// defined the desired entity (provider specific)
String[] desiredEntities = String("contact").split(",");
// select desired columns. All available listed here: https://docs.superoffice.com/en/api/netserver/archive-providers/reference/simplecontact.html
String[] desiredColumns = String("contactId,name,contactUdef/SuperOffice:3").split(",");
// use paging for large datasets
Integer pageSize = 10;
// execute the query
NSArchiveAgent archiveAgent;
NSArchiveListItem[] rows = archiveAgent.GetArchiveListByColumns("SimpleContact", desiredColumns, order, restrictions, desiredEntities, 0, pageSize);
foreach (NSArchiveListItem row in rows) {
Map rowData = row.GetColumnData();
String contact_contactId = rowData.get("contactId");
String contact_name = rowData.get("name");
String contact_dateUdef = rowData.get("contactUdef/SuperOffice:3");
printLine(contact_contactId + ": " + contact_name + " => " + contact_dateUdef );
}Hope this helps!