Form components palette question

Hoping @nmudge can help here :slight_smile:

I’m using your Form Components add-in to create some CRUD screens for database tables. They’re working great, but I’ve run into a situation that I’m not sure how to handle exactly.

I have a database table with a few columns, an ID column (serial primary key), a name column that needs to be unique and not null, and another text column that needs to be unique, but can also be null. I’m not sure how to handle the last field. Is there a way to add to the validation method on the standard text field to convert the empty text field to a null value when saving to the database? I can’t save empty strings as that will violate the unique constraint on the second item that tries.

I’m also looking at the possibility of using a database trigger to convert empty strings to nulls as they are inserted/updated into the table. If that ends up being easier to deal with, I’ll go that route, but I was curious how this would be handled in ignition, if possible.

Ok, well, totally went down the rabbit hole on this one, but I did come up with a trigger function that is reusable for multiple tables, and will test multiple columns for empty strings. I figured I’d put it here; maybe it will save someone some time someday :sweat_smile:

The function is built for Postgresql. The SQLFiddle I was using listed version 9.6, but I think it might be backward compatible to somewhere around 9.0. It does require the hstore extension to be enabled on the database you are using this function on. As far as I know, the extension has been bundled with Postgresql on most distributions for a while now. Enable it by running the following code once:

CREATE EXTENSION hstore;

Add the function to your database by running the following SQL code:

CREATE OR REPLACE FUNCTION empty_str_to_null()
  RETURNS TRIGGER AS
$$
/*
 * This function takes a list of column names and 
 * checks the row being inserted/updated for empty strings in those columns.
 * If any empty strings are found, those columns will be converted to NULLs
 * and the row returned.
*/
DECLARE
   _col_value text;
   _col_name  text;
   i integer;
   cols_to_null text[];
   null_array text[];
   hstore_list hstore;
BEGIN
   -- make sure at least one argument was passed to the function
   IF TG_NARGS = 0
   THEN
     RAISE EXCEPTION 'Function empty_str_to_null requires at least one column name to be passed as an argument';
   ELSE
     FOR i IN 0..(TG_NARGS-1)
     LOOP
       _col_name := quote_ident(TG_ARGV[i]);
       EXECUTE format('SELECT ($1).%s::text', _col_name)
       USING NEW
       INTO  _col_value;
       -- check to see if the column is an empty string
       IF _col_value = ''
       THEN
         -- add this column name to the array of columns to be nulled
         cols_to_null = array_append(cols_to_null,_col_name);
         -- also add a null value to the nulls_array.  the hstore function expects same length arrays
         null_array = array_append(null_array, NULL);
       END IF;
     END LOOP;
     
     -- check to see if any columns were found that need to be nulled
     IF array_length(cols_to_null, 1) > 0
     THEN
       -- build hstore object and do the replace
       hstore_list = hstore(cols_to_null, null_array);
       EXECUTE format('SELECT ($1 #= ''%s'').*', hstore_list)  
       USING  NEW
       INTO   NEW;
     END IF;
   
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

Then create the trigger on your table with code similar to the following:

CREATE TRIGGER demo1
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE empty_str_to_null('txt', 'other_field');

The arguments you supply to the function are the names of the columns that you want to be converted to nulls if the field is an empty string.

To anyone reading, feel free to pick this apart and critique it. I’m sure there’s something I’m doing in there that could be done better.

1 Like

I modified the function to let it check for a value of -1, a popular null substitute for integer columns. I renamed the function as well, to reflect that I’m now checking for more than empty strings. It was a pretty simple modification. Here’s the updated code:

CREATE OR REPLACE FUNCTION null_substitutes_to_null()
  RETURNS TRIGGER AS
$$
/*
 * This function takes a list of column names and 
 * checks the row being inserted/updated for null substitutes (empty strings and -1 values)
 * in those columns.
 * If any empty strings are found, those columns will be converted to NULLs
 * and the row returned.
*/
DECLARE
   _col_value text;
   _col_name  text;
   i integer;
   cols_to_null text[];
   null_array text[];
   hstore_list hstore;
BEGIN
   -- make sure at least one argument was passed to the function
   IF TG_NARGS = 0
   THEN
     RAISE EXCEPTION 'Function null_substitutes_to_null requires at least one column name to be passed as an argument';
   ELSE
     FOR i IN 0..(TG_NARGS-1)
     LOOP
       _col_name := quote_ident(TG_ARGV[i]);
       EXECUTE format('SELECT ($1).%s::text', _col_name)
       USING NEW
       INTO  _col_value;
        -- check to see if the column is an empty string or '-1' (all fields in NEW are passed as text types)
       IF (_col_value = '') OR (_col_value = '-1')
       THEN
         -- add this column name to the array of columns to be nulled
         cols_to_null = array_append(cols_to_null,_col_name);
         -- also add a null value to the nulls_array.  the hstore function expects same length arrays
         null_array = array_append(null_array, NULL);
       END IF;
     END LOOP;
     
     -- check to see if any columns were found that need to be nulled
     IF array_length(cols_to_null, 1) > 0
     THEN
       -- build hstore object and do the replace
       hstore_list = hstore(cols_to_null, null_array);
       EXECUTE format('SELECT ($1 #= ''%s'').*', hstore_list)  
       USING  NEW
       INTO   NEW;
     END IF;
   
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

@nmudge, I needed to modify your component palette script to be able to use the dropdown list component with the ‘selectedValue’ field, instead of the default ‘selectedStringValue’ field. (Thanks for setting up the custom property to do that! I was trying to figure out how to do it myself when I discovered you had already thought about it :slight_smile: )

The section I changed was the ‘def setValue(self, value)’ function. I removed the str() cast from the combobox setattr function. Ignition was throwing an error about being unable to cast a string to an integer when stuffing the value in the selectedValue property. As my field was connected to an integer column in the database, it should have been fine to stuff into the property as-is.

Also, I’m guessing that a dropdown list configured to be using the selectedStringValue property is probably going to be connected to a database column of a string type. Therefore, casting to a string explicitly before stuffing the value into the selectedStringValue property is probably unnecessary. It seems to be working fine without it in my limited testing. I’m not sure if you had a reason for the explicit cast. If so, we might be able to check which field the component is configured to be using, and cast appropriately before stuffing the value in the configured property.

The function I have now is:

	def setValue(self,value):
		for comp in self.input.getComponents():
			if isinstance(comp, (PMITextField,PMITextArea)):
				if value == None:
					value = ""
				comp.text = value
			elif isinstance(comp, PMINumericTextField):
				if value == None:
					value = 0				
				mode = FormInput.numericModes[comp.mode]
				setattr(comp,mode,value)
			elif isinstance(comp,PMIRadioButton):
				if value in ["",None]:
					value = "Default"
				if value == comp.text:					
					comp.selected = True
			elif isinstance(comp,PMIComboBox):
				if value == None:
					if comp.ValueProperty in ["selectedStringValue","selectedLabel"]:
						value == ""
					elif comp.ValueProperty == "selectedValue":
						value = -1
				# removed str function from around value argument below
				setattr(comp,comp.ValueProperty,value)
			elif isinstance(comp,PMIDateTimePopupSelector):
				comp.date = value
			elif isinstance(comp, PMICheckBox):
				if value == None:
					value = 0
				comp.selected = value