"Seed" Database Records

Hello Bubblers!

I have a survey application where clients can conduct self-assessments. These assessments are made up of categories, functions, and controls. Each of the 6 categories has 3 functions, and each function has 5 controls. In all, there are 90 different records that need to be created when a client clicks “Create New Assessment.”

The database structure has tables for Client (which includes a list of Assessments), Assessments (which includes a list of categories as well as a pointer back to the Client), Categories (which includes a list of functions as well as a pointer back to the Assessment), Functions (which includes a list of controls as well as a pointer back to the Category), and finally, Controls (which includes a pointer back to the Function).

Currently, I have a big ‘ol honkin’ workflow that does the following - each step is a manual process:

  1. Create the Category
  2. Create the first function, point it to the Category created in Step 1.
  3. Repeat (2) for each function.
  4. Add all functions to the Category.
  5. Create the first control, point it to the Function created in Step 2.
  6. Repeat (5) for each control.
  7. Add all controls to the Function.
  8. Repeat (5) through (7) for each Function.
  9. Repeat (1) through (8) for each Category.

Needless to say, this workflow is enormous. I was wondering how all of you handle seeding your database in situations like this. I read a little about repeating/recursive workflows, but wasn’t sure if that was the best way.

Any ideas or recommended threads out there? I did try to search, but I must not have used the right search words.

Thanks - appreciated!

John

Every step that you have ‘Repeat N’ can be replaced by Create a list of things (using bulk data creation API) or Make changes to list of things (using a normal action)

You probably need what is currently step 1-8 in one backend workflow.

Then, you need that backend workflow to take a Category parameter. Schedule API workflow on list of Categories (created in front end or another backend workflow using 6 create a new thing actions or the bulk data creation API call).

When you’re referencing the category, reference the category that was passed into the backend workflow.

I also don’t really understand what categories/functions are but does it definitely not work for you to have an option set for categories, a Question data type or option set for Questions, and a Question Answer data type which contains the Question and the user’s answer (answer could be text, number, option set whatever). That’s normally how these things are best set up.

1 Like

Thank you! I will give this a try.

John

Just in case anyone else has this question, here’s how I set this up.

  1. The button on the Index page kicks off this process. The “Seed Category” table has the high level categories.

  1. Now we get down to the back-end workflows:

  1. So the first backend workflow adds the first category. It then calls another backend workflow to add the controls associated with that category. That backend workflow adds the controls, and then it calls another backend workflow to create the measures associated with that control.

Thanks so much, @georgecollier for your suggestion - it really helped guide my learning!

John