Besides Automations, is there a way to compose the data submitted through a flat form into structured (multi-table) rows?

1

Given a doc with multiple tables, all related through various lookups, the use case is to enable the user to input data through a simple “flat” form that would automatically create and connect the appropriate entries in these tables rather than have to input new rows in each table and connect them all.

So far, I have been able to create a temporary “dump” table that contains all the pertinent fields from the tables flattened, and a form to create new rows in this dump table. Then, after submitting the form, the user must press a button to process the latest “dump” row into the entries in the core tables (using a sequence of nested AddRow()s). This is not ideal as it requires two actions to be run in a specific order. Automations could be used to run what the button does automatically upon new “dump” rows being created, but this is not ideal.

For example, with a domain like Student, Course, School, the dump table would have fields like “Student name”, “Studnet number”, “Course name”, “Course number”, “School name”, “School address”, and then use all those fields to create new rows in each of the Student, Course, School tables (finding and reusing existing rows as possible).

Anyone have any insight on making this pattern work?

2

Hi @loucadufault
unfortunately there is no easier way. This is a common situation ont only in Coda: every time you need a data transformation, an ad-hoc logic must be put in place in order to respond about data consistency (both from a design and content perspective).

So, the complexity really depends on the underlying structured data model and the actual use-case.
Are all the “flattened” entities as potential new rows? i.e. I might create a School row every time I submit a form or I can rely on pre-populated drop-downs?

To keep the integrity consistence “transactionally” (in the Coda context, we are certainly not talking about real transactions), you might want to run the new flat rows under RunActions() and check if the looked up entities. e.g. the just created School) does exists or not.

Such as this (in almost pseudo-code)

RunActions(
  db_FlatTable.Filter(IsImpoted.Not()).FormulaMap(CurrentValue.withName(record, // for each record of the flat table
    db_Students.Filter(StudentNumber=record.StudentNumber).first().withName(student, // check if student exists. if not create a new one
     If(student.IsBlank(), db_Student.addRow(....), student).withName(newStudent, // you have the student reference here
      db_schools.Filter(SchoolName=record.SchoolName).withName(school,
       If(school.IsBlank(), db_Schools.addRow(....), school).withName(newSchool, // you have the school reference here and in the add row you can refer to student as a valid lookup
// [...]
),
record.modifyRows(IsImported, True()) // just mark the flat record as imported
      
)))))))

There are minor consideration to take into account, but overall it should work.
Let me know if this helps.

Cheers!

4 Likes
3

Thanks for the answer. That looks similar to what I am doing, and I suppose there is no other way than having a button that runs all this.

One thing that would be cool would to have the Submit button of a form be an editable button (like any other, but pre-populated with the logic to create the row), that one could use to hook into form submission and run some custom code (even code that would modify the inputted data to better ingest it, for example).

4 Likes
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.