TODO

#Docs to Enter | Documentation
#Docs to Enter | Documentation

Note: It’s just much more pleasant to type in here and then we can transfer to the corresponding MD files in the “docs” folder in the repo. This is also a great brain-dump so what is entered here should be trimmed up to make sense in the docs.

⁠

#API

[ApiExplorerSettings(IgnoreApi = true)] Use this attribute for Controller methods that we should not have exposed through Swashbuckle

⁠

#Triggers

All triggers are done through stored procs so that we can manually fire the triggers

Since we try so hard to keep faux-updates from setting of triggers but only updating the field values that have actually changed.

⁠

#Activity

Activity_Type

? How do we differentiate from straight API requests made by external clients to the exact same requests that are made from our front-end app?

The Session_Type (API vs App/Web-app)

so when API:

For the external-API request we just log the type as API-GET, etc..

For the API requests from our app, we want to show “Save/Submit Page”

For Forms with multiple-parts, like with multi-records forms, we make separate API calls for the form or maybe even for each record (see Omma’s arch) in the multi-rec-form, so we don’t want all of these getting logged in the activity (and we probably don’t want all of the authentication of the session and user that has to happen on each request to so we need to have each multi-record form submit all of the records at once so its just one request

But we could get (and need to in order to keep the mods for the sub-forms together or for multi-part forms) a mod_id as a type of verified-key out, so that for every subsequent request, we don’t have to do all of the validation (except that we do have to check the user’s permissions to add or edit records in that table).

⁠

#Address (and Emails & Phones)

We store all of the addresses in a separate table that we then reference on the records via the “x_address_id” column (f.is_ref and f.ref_table_id)

So that we can store their history of prior addresses and manage address data better, then just flat fields on the various records (plus its just ugly to keep repeating the address fields over and over, like for home vs ship address)

including date_from, date_to

Essentially, we will override the address data if the user just updates the address fields, but we could setup a special trigger that will automatically create the new address record

Officially, the user should click on the “enter new address” when they are updating the address to a new one, which will be its own special form,

could use “is_new_rec” or “is_insert” flag on the JSON to know if its

Flat-Format vs Full-Format

We want the API and forms to be blind to the underlying refed-fields-structure, so we can pass in the minimal amount of keys or ids for the fields in an easy structure, so the forms serialize the data easily

#Shared-Sub-Table (Ref_Table_Type) (Address)

Refs to the Name field like Ref-Fields usually do

But also allows us to pull additional ref-table-fields (like City, Zip Code),

similar to the Mod_Rec_Main_Vals

For RecSaver()

The key is the “ref_field_id/key” property (like home_address_id)

For Flat-Format, we have the ref_field_id/key point to the field on its table and the id/key point to the field on the ref-table

Full-format uses a "Ref-Table" property on the Field, which has its own fields

When flat-format, we are essentially transferring over the to the “full-format” in RecSaver

So we convert/move these refed-fields (where same ref_field_id/key) into a new sub-table under the current-table

with the table_id = field.ref_table_id

and rec_id = field.value (if there is a field that is just for the field-id/key

remove these fields from the main object (if we are copying instead of moving)

We call RecSaver for this sub-table first so we can then enter this id on the “ref-field”

create the ref-field if it doesn’t exist already

Use the same mod-id

Order Nuances

we have to get the field.value_old out first because we might not have the

? How do we know that the ref-table is of this special type that refs to a table with multiple values?

new prop for f.ref_type_id

Lookup

How do we make the address_id saving easier and not rely on the user to pass it in each time?

Like we could have a “-1:None” rec for address that we use so that null value passed in for the address_id will always pull out the existing value.

(If we actually have data for any of the fields)

? How do we abstract away from our table-structure (meaning pass in a flat-format without sub-tables) ?

like using “.” in the db_col (or key) to know its based on a refed-table

like contacts.address.street

but would be much better to not rely on the db_col parsing (and having the read-only) fields included

We could parse the fields in the RecSaver(), so that we take out all of the ones

For View_Mods (which is the main way that API-Users will be interfacing with the system)

?How do we easily pass in the values without the complicated structure and ensuring that the address_id that is passed in, belongs to that record (so we don’t have any mixing of data)?

We could utilize a GUID key instead of the id

A huge challenge is the way that RecSaver() and View_Mods have to interface since we want to use RecSaver() as the ultimate base for all of the Mods

Concerns

Allowing the users to pass in the value for the address_id, needs some validation to ensure that they are owners of that rec

But we could have some shared addresses between contacts

and we want to update it in only one place if it needs to be corrected.

but we could just keep it simple that there is no address sharing between contacts, and just have special functionality that we update all of the same ones (if the user chooses)

(families are put together as a single entity)

but what about children in home who would have that same address_id until they move out.

If we have the JSON for the RecSaver() call the

⁠

#Mod-Layer | #RecSaver | #View_Mods

Mod-Layer refers to the unified insert/update/delete operations that we run through a single class that ensures we track all of the modifications to the field-values and that we only insert/update the columns that have actually changes (or for insert that have values) so that we have the least amount of triggers go off.

It’s really important that we only pass in and hence update the field values that were actually touched, since we don’t want “concurrent form users” to override each others changes (by opening the form at the same time but saving different field value changes).

I considered having the db_col be part of the inputs names so when we save the data, we can just parse the view_field_id and db_col from the name.

Since we are Init such complex Models/Services for each of the insert/update operations, there is a concern about memory use of all of the objects, so a major part is to only pull the cols/fields that we really need to init these objects for the Mod-Layer.

We can call the Mod Create() after the update has happened because we already have the old values,

Permissions

We loop through all of the view_tables and we call p_user_can on them which checks their permission access to the table.

If there are no filters on the table, then we can cache the results in a permission_can_results table, which we can then call by the view_id + prm_id + can_type (view vs edit) so its really quick instead of having to check each and every table included on the view. - BUT - these recs have to be deleted/refreshed every time that the permissions change.

Questions

? What about straight API Calls to modify field values on a table (by specifying the db_table and db_col in the JSON instead of the view-tables and view-fields) ?

? How do we pass in the field_id, along with the column name

? Do we have to call p_mod_create and p_mod_val_create (which handles the ref-field-values) instead of directly?

Yes, the p_mod_create does a lot

? What about entering child recs under parent-recs? (Like Case under Contact)

so we have to know the order of the data/tables for the form/view to loop through it and we have to

? How do we pass in the main/parent_rec_id column/id-value ?

essentially as a separate input-param “parent_rec_id”

we have to build a dynamic object of all the tables and fields included on the form

we pull the “tables” rec and see if the parent_table_id is not null, and if so, then we

mod_rec_main_vals

At what point do we call the “p_mod_rec_main_vals_create”?

Flow

loop through the view_tables (service instead of model for all of these) by ord col

? Can we deserialize the JSON straight into the models or how do we best parse it to match the view_tables parts?

loop through the view_fields (for that view_table)

use a keyed object like IDictionary in order to ensure that db_col is not repeated

there can be multiple records per table to loop through

for sub-tables, the key is to make sure that we have the

? Should we first loop through and

AI

Class for Multiple Insert/Update Operations with Modification-History

We have API and forms that submit multiple records to be inserted/updated together

Some of the records must be created before the others - based on the “Tables.parent_table_id” property, but we mainly rely on the “View_Tables.ord” to go down through the submitted data object that contains the field values

We pass in the “Views” objects which has the “View_Fields” and “View_Tables” as multi-records objects inside of it.

We need to track all of the modification details via the

We pass in the Session object and the Activity Object and create the Activity Record

We use “mod_vals” records to store the field values for all of the fields updated/actually-changed or inserted by calling p_mod_val_create to enter the mod_vals recs (via model.services.Mod_Vals.Create() ).

Ideally, we don’t create a “mods” record via (p_mod_create → model.services.Mods.Create()) unless there are actually changes to field values (always for “insert”) but its okay to have a mods rec without corresponding “mod_vals” records, although its not ideal.

the “Mods” record create() returns the “mod_id” that we use for the Mod_Vals.Create()

Input Params:

Mod_Type (see the enum for it below) - determines whether it is for insert or update

table_id & table_name

IDictionary<string, object> fieldValues - that were possibly changed on a form (touched).

For Inserts:

We have to see if the standard “id” column is an identity and if not

We check if the values have actually changed by pulling the existing values in the db.

(as a single database call to get all of them out and loaded)

Like [Copy code from existing file]

(We don’t worry about Delete operations for this because we use a soft delete of “rec_type_id = 4 (deleted)”)

—

Help me write the code for an insert, update and delete layer, where all modifications are tracked in their own table with a sub-table for every field which was modified with its old value. If the field is a lookup field then we store both the id and lookup value of the old and the new values.

If there is no record ID supplied, then we insert the record instead of updating it.

If its a delete operation, then we store all the not-null field values in the Modification-History table.

For the fields that have been updated we generate an update statement for just those field whose field values have changed and for each of these fields we enter into the this Modification-History table.

If no field values have been updated then we do nothing.

This has to work without predefined tables and columns, so its truly dynamic, so that users can add new columns to the tables in the db without having to generate different code.

Use all the best practices and include lots of comments so beginners would understand.

Let me know if you have any clarifying questions.

⁠

#Validation

Check that the record actually exists (for this id) since it could have been deleted (like after the user opened the form)

We init all of the View_Fields.Fields with the data_type (using v_fields)

Fields - conditionally requirements checking

Where we use a Filter, so would be much more complicated since either have to do a combo of sql query to get the other field vals or compare in .NET by pulling out all of the existing field values into dynamic objects)

⁠

#Default-Entered-Field-Values

We have default field values per table and per form/view. The table ones are entered only when we create a new record, but the form/view auto-entered field values are for both insert/update (so will override existing values)

From the “filters” table with the filter_type_key of “default-entered-field-values” and “default-entered-field-values-views”

Fields.default_val VS Filters for table-default-entered-field-vals

The big upside to using filters (table-default-entered-field-vals) instead of having it set on the Fields as a property is that it is per Table, so this works with sib-tables who share fields.

Fields.default_val works at the DB Level, so it has a default constraint setup and will set all of the existing values to that one. Its meant more for bit fields.

⁠

#Filters (Default-Entered-Field-Values)

⁠

#View_Mods

since this is mainly for our own forms (although we could have the client use this, they will most likely use the RecSaver() since its much more straightforward and allows them more control and don’t have to match up the

vf.id⁠

)

Backwards-Linking: View_Tables to Tables to View_Fields Fields so that the Tables and Fields are tied together

The View_Tables.Tables.Fields are the same as the View_Fields.Field

we backwards link the Field to the View_Fields (for ValidationResults)

via the Field.ForViewField (View_Field)

View_Fields having the field_id saves us an extra query and matching up but no big deal

we have an efficient way to get all of the field_ids to the corresponding vf_id

#View_Mods-Validation

we utilize the

field.validatorPropName⁠

and this logic to make it return the

vf.id⁠

when for View_Mods: .WithName(field => $"{field.validatorPropName ?? field.db_col}")

⁠

#Views

p_view_data is the main way to access views.

TODO: Create a Service that extends the “Views” model that will allow us to pass in all of the rest of the params

We can use the “p_Code_Gen_Service_for_Proc” to Auto-Gen

⁠

#Checklists

? Can we allow MR-Tables more then one level away from the target/base table to be used on the Checklists? Like if the Checklist is based on “Case” and there is “Adoption” (MR) and then there is “PAR” (MR) under that, so can we really go that far down. In SAM we were limiting which MR tables (even just one level away from the Checklist-Base-Table) to not be shown unless we specifically set the property on the Table to allow it.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.

#Docs to Enter | Documentation#Docs to Enter | Documentation