Improve multi-create workflow for almost pure and binary relationships

[jrchudy]

This issue focuses on tables that are almost pure and binary associations. These tables have 2 foreign keys and some static columns. A good example of this is the Protocol_Author table in atlas-d2k. We want to be able to create rows using the modal interface but also need a way to allow for setting values for these associated rows after selections have been made in the modal.

To improve this workflow, we are changing what the Add records button does on record page for inbound foreign keys when certain criteria are met. If the table is "almost a pure and binary association" and has other columns that are not foreign keys, we will trigger the following alternate workflow. Currently for adding protocol author associations, add records opens recordedit app with only 1 row and the "author" value is empty.

Workflow change:

We discussed changing the workflow to the following for adding rows to an association like table when the pair of foreign keys are a unique relation (1 table entry for X main entity and Y leaf entity pair) and non-unique (N table entries for X main entity and Y leaf entity pair):

1. Start on record app like above and click "Add Records"
   
2. Recordedit is opened in a new tab with an recordset modal picker shown on top of the recordedit inputs on page load
   
3. After selections are made, click "Continue" to use those selections to fill in recordedit forms
   
4. More forms can be added using "Add more" which will show the recordset modal picker again
   a. in the case that the pair of foreign keys are a part of a unique key, this modal will show selected rows from step 2 as "greyed" out since they can't be chosen again
   
5. Click "Save" to add the new selections to the form
   
6. Finish filling in the forms and click submit. When closing the recordedit tab that is showing the resultset view, the record app will refresh to show the added rows

Notes:

• This feature will automatically trigger when the table is like an association table (only 2 foreign keys, both outbound) and any number of other non foreign key columns
• the modal picker for associating rows will grey out previous entries if both the main table and leaf table foreign keys are used in a unique key
• the save button in the modal changed to "continue"
• "Add more" will add more rows and not allow "changing" existing rows regardless of uniqueness
  • By default, add more will use default initial values
• recordset modal picker on load, add more recordset modal picker, and individual foreign key input modal pickers will disable rows that are already associated with the main record or are used in the current recordedit form
• For non-unique case, How do we add multiple rows at once for 1 value?
  • with these changes, we can add 1 more row, then use clone feature to create X more with that same value
  • create separate issue for this. pending...
• clone will be updated to not clone the foreign key field that is part of a unique key
• If the user wants to add more after submitting the recordedit forms, they can start from record page and the same process above is used
• Editing the data for the association table
  • Starting from record page, using bulk edit will show the bulk edit recordedit view with no changes since we can't "add new forms" in edit mode of recordedit app

Annotation changes to make:

Properties to add to annotations will be bulk_create_foreign_key and bulk_create_foreign_key_candidates in 1 case (table-display). Annotations to change include: display annotation on catalog, schema, and table, table-display annotation, display property of the foreign-key annotation, and the display property of the column directive syntax used in visible-columns annotation.

"tag:misd.isi.edu,2015:display": {
  "bulk_create_foreign_key": false | null
},
"tag:isrd.isi.edu,2016:table-display": {
  "entry/create": {
    "selector_ux_mode": "facet-search-popup" | "simple-search-dropdown",
    "bulk_create_foreign_key_candidates": [["schema_name", "foreign_key_name"], ...]
  }
},
"tag:isrd.isi.edu,2016:foreign-key": {
  "display": {
    "entry/create": {
      "bulk_create_foreign_key": ["schema_name", "foreign_key_name"] | false | null
    }
  }
},
"tag:isrd.isi.edu,2016:visible-columns": {
  "entry": [
    "col1",
    "col2",
    {
      "source": [
        { "outbound": ["bulk_create_foreign_keys", "viz_columns_to_main_fkey"] },
        "RID"
      ],
      "display": {
        "bulk_create_foreign_key": ["schema_name", "foreign_key_name"] | false | null
      }
    }
  ],
  "compact": [ ... ],
}

Notes:

• values accepted for this property:
  • ["schema_name", "foreign_key_name"]: foreign key name to be used as the foreign key input being bulk selected if that foreign key is in the visible columns list
    • _constraintName in ermrestJS for foreign key pseudo columns
  • false: Turns off the heuristics that trigger this feature.
  • null: Overrides inheritance for this property and use the default heuristics.
• display only allows for turning off the feature or ignoring inherited values and using default heuristics
  • ignoring inherited values only makes sense in this context if the feature is turned off at the catalog or schema level using the display annotation and this is being turned on for 1 specific table
• table-display would allow for multiple foreign keys to be defined that are checked based on the columns visibility to the user
• foreign-key, defined on one foreign key relationship and be unidirectional
• visible-columns, like foreign key above, this would be defined on one foreign key of the relationship and be unidirectional

[jrchudy]

We decided to improve the foreign key modal picker to behave the following way:

• The selected row will show as a blue circle with a check in it
• Rows selected in other inputs or other already created records are shown as a grey circle
  • hovering over those grey circles will show a tooltip explaining why it's disabled

Example:

[jrchudy]

Names for property:

• multi_value_foreign_key_constraint_name
• outbound_multi_picker
• bulk_create_foreign_key <--
• bulk_select_foreign_key

Values for property:

• "constraint name syntax" that is used for naming foreign keys in schema documents
  • ["schema_name", "foreign_key_name"]
• false turns off the heuristics that trigger this feature
  • provides a way for data modelers to suppress this property
• null to override inheritance for this property and use the default heuristics
• anything that is NOT false or constraint name syntax will use default heuristics (the value is not defined) unless another annotation exists further up the hierarchy chain

Annotations to add to:

visible-columns and visible-foreign-keys annotation

Protocol Author table

"visible-columns": {
  "entry/create": [{
    "source": <source path>, // foreign-key to protocol table
    "display": {
      "selector_ux_mode": <facet-search-popup|simple-search-dropdown>,
      "bulk_create_foreign_key": ["schema_name", "fk_name"] | false | null
    }
  }]
}

foreign-key annotation

Protocol Author Table

"foreign-key": { // foreign-key to protocol table
  "display:" {
    "entry/create": {
      "selector_ux_mode": <facet-search-popup|simple-search-dropdown>,
      "bulk_create_foreign_key": ["schema_name", "fk_name"] | false | null,
    }
  },
  "domain_filter_pattern": "",
}

Notes:

• defining it here allows for anytime records are created for this table and this is the foreign-key we are prefilling
• false would disallow this feature from triggering during prefill on this table if this is the prefilled foreign key
  • could be a way to make a definition uni-directional

table-display annotation

Protocol Author Table

"table-display": {
  "entry/create": {
    "selector_ux_mode": <facet-search-popup|simple-search-dropdown>,
    "bulk_create_foreign_key_candidates": ["schema_name", "fk_name"]
  }
}

Example:

"table-display": {
  "entry/create": {
    "selector_ux_mode": "popup",
    "bulk_create_foreign_key_candidates": [["protocol", "protocol_author_protocol"], ["protocol", "protocol_author_author"]]
  }
}

Notes:

• defining it here would communicate which foreign key should be used for this feature when records are being created for this table with a prefill
  • pick the first constraint name in the array that is not the foreign key being prefilled to use for the multi picker modal
  • can be used when many foreign keys exist on this table
• this annotation is always contextualized, so this property can only be defined for "entry/create" context
  • otherwise it will be ignored

display annotation

"display": {
  "show_null": { context: ... },
  "show_key_link": { context: ... },
  "show_foreign_key_link": { context: ... },
  "hide_row_count": { context: ... },
  "show_saved_query": ...,
  "bulk_create_foreign_key": false | null
}

Notes:

• there needs to be a way to disable the default heuristics for this feature for data modelers
  • or reenable if it was turned off at the catalog level but should be on at the table level
• define as null or false for "display" annotation
  • can be defined on a catalog, schema, or table to turn this feature on or off at different levels of the model with inheritance

[jrchudy]

true | null usage in configuration

NOTE: true doesn't mean anything different from null in the context of this annotation. true is a way to use the "default heuristics" for this feature. This also happens to be the "default" as well

annotations

null is needed to override inheritance

• display annotation:
  • "An explicit setting of null will turn off inheritance and restore default behavior for that model element and any of its nested elements."
• indexing-preferences
  • "If a column-level annotation sets a preference of null, this suppresses any table-wide preference for the same indexing type, requesting built-in service defaults for the column."

null means undefined, same as not existing in schema document

• immutable, generated, non-deletable, required
  • anything not FALSE turns this feature on
  • false turns it off in case of inheritance
• visible-columns
  • for non filter contexts: "Any non-string, non-array value (e.g., null): The client will use the default heuristics for generating list of visible columns."
    • nothing explicit in our docs about null being defined for filter
• visible-foreign-keys
  • nothing explicit in our docs about null
• chaise config properties
  • null for chaise config properties generally means undefined so the default is used

[jrchudy]

The first part of this issue has been implemented. This issue is pending annotation changes to allow for configuring this without relying on the heuristics.