Sysero

Count specific rows in a repeater

Manual: Tips and Tricks

Detail:

If you have rows in a repeater with specifc values, enter the following in the OnSave Formula:

cast(XmlField1 as xml).value('count(!Xpath!)', 'varchar(50)')

and the following in the Add a repeater xpath field:

//RepeaterData/Repeat/*[@DatabaseValue="TextField2" and @Value="Rate2"]

Thiis assumes your repeater looks like this:
<Repeat UserID="1014">
<Data LabelText="Record Day or Hour" DatabaseValue="TextField2" Name="Record Day or Hour" Value="Rate2" />
</Repeat>

Relevance Score: 0.1891018

Calculated field used in Maintenance App

Manual: Tips and Tricks

Detail: (
SELECT max(CONVERT(DATETIME,Repeater.Data.value('Data[@DatabaseValue="DateField3"][1]/@Value','nvarchar(max)'),103))
FROM (SELECT CAST(XMLField1 as xml) as xmlres) as conxml
CROSS APPLY conxml.xmlres.nodes('//RepeaterData/Repeat/Data[@BooleanValue="False"]/parent::Repeat')
Repeater(Data))

Relevance Score: 0.1891018

Summing values in repeaters

Manual: Tips and Tricks

Detail: If you have a number field in a repeater and want the total to aappear in the main form, simpley use this format in the OnSave Formula:

cast(xmlField1 as xml).value('fn:sum(//RepeaterData/Repeat/Data[@DatabaseValue="NumberField9" ]/@Value)', 'int')

Relevance Score: 0.1891018

Manipulating Dates with Live Formula

Manual: Tips and Tricks

Manual: Administration

Manual Level Two: Data Rooms

Manual Level Three: Forms Design

Detail: Below is an example of converting en-GB date format of dd/mm/yyyy to date for manipulation and adding number of months to a date.

In the live formula you will be calling something like dateaddmonth('[DateFieldx]',numberofmonths)

The matching function below will need to be added to the Input form header in the JavaScript section.

function dateaddmonth(invalue,addmonth){
try
{
var parts = invalue.split("/");
var options = {
year: "numeric",
month: "2-digit",
day: "2-digit"
};
var d1 = new Date(Number(parts[2]), Number(parts[1]), Number(parts[0]));
d1.setMonth(d1.getMonth() + addmonth-1);
return d1.toLocaleDateString("en-GB",options);
}
catch(e)
{
return e.Message;
}
}

Please note this is an example and changes will be required for your environment or requirement e.g. you may need to add years or combine two dates.

Relevance Score: 0.1891018

Scheduling Archive/Deletion of Multiple Items

Manual: Administration

Manual Level Two: Job Scheduler

Detail: There are several ways to archive then delete items from a Data Room, below are the various options:

Data Room Setting : Auto Archive Items based on number of days since Last Edit Date.
Data Room Setting : Auto Delete Items based on number of days since Archived.
Job Scheduler : Auto Archive Items based on field value in Data Room.

Please note all of these options have potential to cause serious data integrity issues if not used correctly, the most serious being if "Auto Delete Items" is used incorrectly, as this is not recoverable at all.

Note : You can also archive individual items via API calls or Archive Item Data Action, API call also supports deletion of individual items, but please do not use this without discussing with Sysero first.

Auto Archive Items

This setting is found under Admin > Data Rooms > Data Restrictions. It requires the Job Scheduler to be running job action Process Sysero background tasks > Auto Archive. You set the number of days until after the Last Edit Date on the record, it will then archive them when the job runs. Not archiving is not Deletion and they can be restored at this point, but if not used carefully can archive lots of live records, which will require considerable effort to resolve.

Auto Delete Items

This setting is found under Admin > Data Rooms > Data Restrictions. It requires the Job Scheduler to be running job action Process Sysero background tasks > Auto Delete. You set the number of days until after the Last Edit Date on the record for only records that are Archived, it will then delete them when the job runs. Note this is non recoverable, the record is fully deleted, so extreme care should be taken when setting this up.

Auto Archive Items based on field value in Data Room

This setting is found under Job Scheduler as a Job Action that can be scheduled.

You need to set one or more initiate field(s) and value(s) for it to match on (it will and them together) . If using a date field you can use = < or > with pseudo value Today with + or - number of days required. The Complete Field can then also set a field value when the record is archived.

Relevance Score: 0.1891018

Welcome to the Sysero Knowledge Base