Skip to main content
Question

Custom Data Rule

  • February 13, 2025
  • 0 replies
  • 56 views
Forum|alt.badge.img+5

Hello,

I want to have multiple data rules to stop an import if the information is not correct but I'm not sure how to word them. I want to be able to check fields and change the values if needed and to make sure an option is not created in my dropdown field.

  1. There are 2 fields for a name that I would like to check for capitalization and if it is not proper to change the field. I would also like for it to check to see if there is a comma and space where it should be.
  2. I need to check for any spaces in front or behind the information and delete them.
  3. I want to be able to stop the import if it has something different than the choices in my dropdown field.

Thank you

MarkShnierYou
Forum|alt.badge.img+24

Here is my suggestion for data scrubbing. I suggest that you import the data into your app in a two step process. You make a table which has all the correct field names matching your import and you call that a scratch file.  I think that's an old data processing term so call it what you want, but it just means a temporary table.

So you bring your raw data into this temporary table, and you use formulas to make any corrections or to detect any formatting errors. And then when the data is scrubbed, then you push a button which will run a saved table to table import and bring the clean data into your main table. 

So the idea is anything you can correct with formulas like capitalization or trimming off leading and trailing spaces you do that by formula in the scratch table and then it'll come in clean into your main table. 

 

What I like to do for clients is to create an admin import control record with which sits in a dedicated table with just one record in it. Because that will be Record ID number 1 you could connect that to the scratch file table by a reference field in the scratch table formula of 1. So then the admin table knows whether the scratch file has data or not.

Then you can put a URL Formula button there for example to purge the scratch file. 

Another button to import into the scratch table.

And another button to copy the scratch file into the main table. You can also have summary fields to count the number of invalid records and prohibit the import if the scratch table is not all clean.

Feel free to post back if you have any questions about this approach. 

 

 

 

 

 

Mark Shnier. Your Quickbase Coach
    Terms & ConditionsAccessibility statement