how to remove the empty rows from CDT before inserting in to Data base?

Question

Hi, 
 We have requirement to use editable grid to add/delete/edit rows into CDT. Noting was mandatory from the Interface. where as while writing to DB some of them are NOT NULL columns. 
 User can submit the empty rows by clicking the Add link in editable grid. Write to Data Store throws an exception because Null values. 
 how to remove the empty rows from the CDT before giving to Write to Data Store. 
 Or At least from the interface it self to delete the empty rows before submitting the form. 
 Working example will help to solve this ASAP. 
 Thanks in Advance.

chetany · Answer

This is straightforward. There are two ways to handle this: 1. In Database itself (in DDL) OR 2. In Appian In Database DDL: Just use default values for columns which could be NULL. So if the input from Appian is coming as NULL/blank, the database will use the default value. Default value needs to be specified in the CREATE statement of the table. You can also specify it in the XSD. However, this will not be good choice if you strictly want that no blank records ever get written to database table. In Appian: Use the fn!filter() function or a fn!reject() function with a custom predicate function which returns true if the input is empty or null. I am describing this technically here: 1. You must be having an array of CDTs local variable which is being updated when user adds/deletes rows in the editable grid. Lets say this array is ri!listEmployees 2. There must also be an id column/other columns which are not null in database. The CDT must have fields mapped to it. 3. Lets say your CDT is Employee(id, name, age). And say id is PK 4. Now create an expression rule which takes as input only one single CDT . Lets call this rule rule!filterEmptyRow 5. In the rule filterEmptyRow, create one rule input of the type CDT Employee (this should be single and not multiple). Lets call this rule input as ri!employeeInput 6. Use this in the rule definition: if( isnull( index(ri!employeeInput , "id", null) ) or isnull( index( ri!employeeInput , "name", null ) ), true, false ). Note: instead of checking for "name", check for some field which is mandatory in UI input as well as database. 7. Now just before writing to database use a filter expression: fn!reject( rule!filterEmptyRow , ri!listEmployees ). 8. The above expression in point 7 will return only those CDTs which are not having any null/blanks. In other words, all CDTs members will be filled. You can safely write the array returned by the reject() function to the database using Write to datastore entity. 
 
 One more way to handle is: if the above reject() returns a non-empty list, then you can show some validation on SAIL UI - and use some message which requests the user to either fill the blank rows or delete them.

jeremys · Answer

Hi, 
You can use a null check to determine which row is not being used, and then use the remove() function to remove the specified rows from the CDT.

shafaly · Answer

Hi ramp, Suppose you have cdt variable ri!CDT of type multiple and then you can use the code: difference(ri!CDT,type!Employee()) This will give you the CDT variable without null entries. Hope this will help you. Thanks

Sunil Zacharia · Answer

Hi, As suggested by other practitioners you may use the reject function to remove null values in your cdt array before comitting in your DB. Ex: reject(fn!isnull, cdtArray) - The first parameter is the predicate that runs against each item referenced by the second input parameter. Regards, Sunil Zacharia

how to remove the empty rows from CDT before inserting in to Data base?

Top Replies