Batch Edit: Batch edit for relationships

[sharadsw]

Component of #4929

There are 4 different types of relationships, in general.
- To-one dependent (for ex. collectionobjectattribute),
- To-one independent (for ex. Cataloger, CollectingEvent [when not embedded])
- To-many dependent (for ex. determinations, preparations),
- To-many independent (for ex. None for CO as the base table)

• Fields

  Modifying any field is possible, other than nodenumber, fullname, and highestchildnodenumber. If some fields get updated, only those fields are highlighted

• To-one dependent (for ex. collectionobjectattribute)

  These relationships get directly updated, and are not matched. If the to-one is not in the db, it'll create one.
  This also includes collectingevent when embedded.
  
  Test cases to consider:

  • When mapped, the record is directly updated.
  • When mapped, if the record is not present, it'll be created, if not null values are present.
  • If the record previously had values, and the values are removed (making the cells completely empty), the to-one dependent record will be deleted. Since it is possible that there may be other fields in the database (but not in the query), we may accidentally delete the record. Eample: user selected collectionobject -> collectionObjectAttribute -> remarks. And say they set remarks to empty in the spreadsheet, it is is possible that integer1 field in collectionObjectAttribute may have some value. To prevent accidental deletion, by default, we look at all the fields in the database for that record (other than system fields), to determine whether we can delete the record or not. This behaviour is controlled by a remote preference. (described in a later section):

• To-many dependent (for ex. determinations)

  Same as to-one dependent. These relationships get directly updated. If the corresponding record is not present, a new one gets created.

  Test cases to consider:

  • When mapped, the record is directly updated.
  • When mapped, if the record is not present, it'll be created, if not null values are present.
  • If the cell data is removed, and if every other field is empty in the database (can be disabled via a preference), the record will be deleted.

• To-one independent (for ex. cataloger)

  These relationships get matched, and uploaded (if match is not found). During upload, it performs a clone of the record (cloning all the non-unique fields, and dependents). The clone takes into account relationships also mapped. That is, if agent needs to be cloned, and you have mapped agentspecialty, it'll take the agentspecialty mapped (rather than cloning previous's agentspecialty).

  Test cases to consider:

  • Start from a collectionobject with a cataloger, and map some fields. Change some of the values (like, say, lastname and firstname) to of agents that are present in the db. verify that agent gets matched. Note that the match can be performed with just the visible fields, or can also include fields in the database, not included in the query. This is controlled via a preference. By default, to be cautious in matching, it uses just the fields visible in the query.
  • If it is unable to match, it'll clone the existing agent, with data from the sheet. Make an agent with addresses/specialties/variants. Make sure the workbench is able to clone the agent correctly, and if you've provided some dependents in the mapping, it takes it.
  • Similar to workbench, you could customize the match behaviour by changing the matching options (like "never ignore", "ignore when blank", and "always ignore")

• To-many independent

  Same as to-many dependent. The only difference is that we always perform an update (we don't delete these). If a mapped record is not present, it'll create one, without any matching.

  Test cases to consider:

  • Make collection objects, and assign them collectingevent. Do a query using collectingevent as the base table, and add fields of the CO table. Verify that resetting all fields does not delete the collection object (you'll also need to disable the preference that says to look at all fields for null checks)
  • If a record is not present, it'll create one, if there is a non-empty field.

Results

There are 4 new different type of results;

• NoChange

Reported when the record was meant to updated, but no change occurred. That is, all the values from the db were the same. This is not visible to the user.

• Updated

Reported when the record's fields were changed. This does not consider relationships (they are reported with different result)

• Deleted

Reported when a record is deleted. Happens when a dependent's cells are all empty.

• MatchedAndChanged

Reported when a to-one independent was matched to another record, different than the current one.

• The side panel also shows the results per table, for different categories.

Preferences

There are three different preference options.

Remote Preferenences (2)

• Defer For Match
  Set by sp7.batchEdit.deferForMatch.This preference controls whether database fields are included for matching or not. Defaults to false.

• Defer For Null
  Set by sp7.batchEdit.deferForNull.This preference controls whether database fields are included for determining if the record is null or not. For dependents, null records are deleted, so this preference is used to control the caution batch-edit follows

• User Preferenences (1)

• Number of query rows
  Determines how many number of query results are used for batch-edit. Defaults to 5000.

[realVinayak]

Make the change to upload plan construction too (for remote to ones). Ask Jason for more context.

See #6073 (comment).

[sharadsw]

Discuss the issue with setting lat1text and re-test in the PR for this issue. See #5417 (comment)