I want to implement Offline validation rules to enforce data integrity of some local tables in SQLite.
When editing a local table in LongRange Studio, I’ve found that I can display a window called Conditional formatting rules manager with an Expression builder, is it intended for declaring validation rules?
In a document called Offline Programming with LongRange dated February 2013, I’ve found that it was planned to:
• Create Data constraints (Between 2 values, Inlist, Inlist from SQL, Custom script) in LongRange Studio
• Be able to attach Data constraints defined in Studio to any UI elements (TextBox for example)
Is it possible now to use them, or is it under development/planned/not planned?
Otherwise, what could be current solutions?
• Manually add constraints on SQLite tables?
• Using scripts?
• Other ideas?
Regards
Yann
Offline validation rules
-
MarkDuignan
- Posts: 346
- Joined: Wed Apr 18, 2012 10:33 am
Re: Offline validation rules
The first question about "Formatting Rule" in LR-Studio has nothing to do with validation rules. It is a somewhat obscure feature of the Telerik UI library we are using for LR Studio UI.
For the rest - there are many ways to validate data.
This may sound like a bit of a ramble – but I think this is really important to offline application design approaches and possibly a new way of thinking about how and when data gets validated.
The primary issue to consider is that the way that you validate data for a mobile device ‘novice’ end users may be very different how you have done it in the past for 5250 or PC based ‘expert’ users. eg: To avoid loss of data the user has input already you may need to store invalid data and validate it (again) later. A quite different application design approach may be needed because in field mobile users are not in-office expert users who use computer systems all day every day as the core of their jobs.
When designing 5250 or PC applications developers tend to view the UI as a window to the data base – and that everything coming in needs to be very rigorously validated and rejected until it is valid. Keeping the database clean is a very important to most IT departments and their developers.
I think it is a mistake to think that just because data is stored in database tables on a mobile device that that it is anything like a database in traditional sense that the BPCS or MAPICS databases on your IBM i server are.
I think that when designing offline applications you should view the UI as a window to some temporary and transient documents (much like a paper document) rather than to a traditional data base. A document that maybe filled in correctly, is sometimes filled in incorrectly - and is often incomplete or partial – but that is never rejected and the data input lost.
Otherwise you risk creating a mobile application that needs that requires ‘expert’ and ‘trained’ users – and doing that risks severely frustrating the mobile novice audience – to the point that they will reject the application.
Early next year we will put together a document on designing offline applications – and include high level and very specific low level ideas for how to approach different data validation situations.
I think it would be better to approach this issue first from the position of reviewing what you need to do (ie: put the ‘what’ before the ‘how’).
Do you have some specific application requirements in mind?
For the rest - there are many ways to validate data.
This may sound like a bit of a ramble – but I think this is really important to offline application design approaches and possibly a new way of thinking about how and when data gets validated.
The primary issue to consider is that the way that you validate data for a mobile device ‘novice’ end users may be very different how you have done it in the past for 5250 or PC based ‘expert’ users. eg: To avoid loss of data the user has input already you may need to store invalid data and validate it (again) later. A quite different application design approach may be needed because in field mobile users are not in-office expert users who use computer systems all day every day as the core of their jobs.
When designing 5250 or PC applications developers tend to view the UI as a window to the data base – and that everything coming in needs to be very rigorously validated and rejected until it is valid. Keeping the database clean is a very important to most IT departments and their developers.
I think it is a mistake to think that just because data is stored in database tables on a mobile device that that it is anything like a database in traditional sense that the BPCS or MAPICS databases on your IBM i server are.
I think that when designing offline applications you should view the UI as a window to some temporary and transient documents (much like a paper document) rather than to a traditional data base. A document that maybe filled in correctly, is sometimes filled in incorrectly - and is often incomplete or partial – but that is never rejected and the data input lost.
Otherwise you risk creating a mobile application that needs that requires ‘expert’ and ‘trained’ users – and doing that risks severely frustrating the mobile novice audience – to the point that they will reject the application.
Early next year we will put together a document on designing offline applications – and include high level and very specific low level ideas for how to approach different data validation situations.
I think it would be better to approach this issue first from the position of reviewing what you need to do (ie: put the ‘what’ before the ‘how’).
Do you have some specific application requirements in mind?
Re: Offline validation rules
I suppose we can design Mobile applications like previously Web applications, i.e. for:
• Occasional users (i.e Internet)
• Business users (i.e Extranet/Intranet)
At the moment, our prospects asked only for applications designed for Business users. In this case, I think that local rules need to be as strong as possible to facilitate the upload process.
I know that I can actually reduce errors by using:
• Local table definition in LongRange Studio: using adapted Type of data and Initial value.
• Form view elements:
o Specific elements with self controls: Checkbox, Dropdown (static, dynamic with SQL), Date/Time/Datetime (date/time picker), Image, Video, VoiceClip, Barcode
o TextBox properties: DataType, TextInput and its sub-properties
But I don’t know how to simply achieve these validation rules:
• Be able to check that a column is not null (i.e. this column cannot have a default value AND the column’s value must have been typed/selected): for example, ADDRESS must be filled.
• For a specific String column, be able to check that this column's value is not blank.
• For a specific Integer/Real column, be able to check that this column's value is not zero.
• Being able to compare columns within a row (i.e. simple or complex expression, date validation): for examples, ColumnA > ColumnB; DateA > (DateB + 365 days).
• Be able to check that a column value is between Value1 and Value2 (i.e. Range).
• Occasional users (i.e Internet)
• Business users (i.e Extranet/Intranet)
At the moment, our prospects asked only for applications designed for Business users. In this case, I think that local rules need to be as strong as possible to facilitate the upload process.
I know that I can actually reduce errors by using:
• Local table definition in LongRange Studio: using adapted Type of data and Initial value.
• Form view elements:
o Specific elements with self controls: Checkbox, Dropdown (static, dynamic with SQL), Date/Time/Datetime (date/time picker), Image, Video, VoiceClip, Barcode
o TextBox properties: DataType, TextInput and its sub-properties
But I don’t know how to simply achieve these validation rules:
• Be able to check that a column is not null (i.e. this column cannot have a default value AND the column’s value must have been typed/selected): for example, ADDRESS must be filled.
• For a specific String column, be able to check that this column's value is not blank.
• For a specific Integer/Real column, be able to check that this column's value is not zero.
• Being able to compare columns within a row (i.e. simple or complex expression, date validation): for examples, ColumnA > ColumnB; DateA > (DateB + 365 days).
• Be able to check that a column value is between Value1 and Value2 (i.e. Range).