Principles:
Keep it simple, yet configurable and overrideable; hide the advanced stuff behind an “adv-opt” link. Keep it uniform so everything works the same way as much as possible.
tbl.tblkey is unique to each tbl rec and used to identify tables in code instead of by “tblid” which is harder to keep up with and could change. Very important for shared-tables like fldsec, which is used for regular sections and form-blocks.
TblTpID - Main Entity Type vs Multi Rec vs Case Table
IsMainEntTp - Easy way to keep track of which Tables are shown in the list of Main Entity Types to choose from when creating a new O. Separate from TblTpID because some of the TblTpID:"Main Entity Type" aren't shown in the list of "Main Tables to Choose From"
Form-HTML is spit out as ready as possible, loaded into a documentFragement to databind, and then appended to actual-DOM in one chunk (except for sub-forms which are loaded async)
To show a form as a datatable we just append "&dt=1" to the QS like this:
http://localhost:3000/#/form/4/?&dt=1
oformGen is the heart of generating the form templates doesn't store anything - it outputs the gen so it can be accessed like x.fviewtmpl/x.fedittmpl
We only have two main types of tables Multi-Rec and Not-Multi-Rec/Single-Rec. (Case tables are technically Multi-Rec but have some special treatment/functionality)
The o.tblid is always for a multi-rec table (or it defaults to being the same as the o.maintblid). Even for special forms, like “Accounting Details” (single-rec), the o.tblid would be for the parent-rec, like Fam or Case. Once we set the o.tblid to a multi-rec table and we add fields then we can’t change the o.tblid (without removing all the flduse and fltr).
When Multi-Rec-Sub-Form (non-dt) then uses “ul” with “list-group-flush” to separate each record:
http://localhost:3000/#/form/7/?mainrecid=1&isdt-1=0
In order to allow Main Forms to contains multi-record-tables, like “Case” or “Latest Communication”, we have a special option in the “Table-Select” to “select multi-rec tables to include in form, but not be based on them”, which allows the tables to be “left-join” included on the form (and used in the sort to show the latest/whatever), but not have the o.tblid to be set for this multirec table
We have to limit creation of single-rec tables to just one level because “Details of Details” doesn’t make any sense.
tbl.fldttlprefix - To prefix the field titles, like when there are multiple of the same table referenced in different ways, like "Main vs Shipping Address"
## tblfltr
tblfltr use the '[tu]' as an "tbluse-alias-placeholder" to replace with the tbluseid in the query, like '[tu].tbltpid = 1'
When tblfltr we will update the record with that filter after the record is created (but before the rec.triggers fire)
(in leiu of “default entered field values”, we just use the “tblfltr” split by “and”; only for “=“ oprtr)
## 'vw' field
We use the 'vw' field on tables to be able to hide certain recs from the regular users, like for forms we hide all the forms belonging to lists and other special system forms we don't want them to see in the main form list.
'vw=1' filter is automatically applied to all main tables and we can manually set the 'vw' as a 'tblfltr' for certain tables too, like 'flds'
Use Route to setup base fields:
/tbl-setup/&isall=1
For the “create new table” form we just pass the “mainrecid”, which actually means the “prnttblid” in this case
Base Fields:
entrdt, entrusr, entrfrm,
moddt, modusr, modfrm,
nm,
memo,
ord
“ismultirec”:
all main tables are “ismultirec”
“ismultirec” is the most important setting (maybe needs to be a faux-radio instead of a chk so that its very clear whether details vs multi-rec)
we shouldn’t be able to change/edit the “multi-record” field once the record is inserted (or once the field is set) - need this as a special field option to protect some fields
tbl.”tbljoincol" is an override to the prnttblidcol - we only use the "tbljoincol" in the tbluse-str, like for “Sub-Tables” we use "tbljoincol" = “prnttblid” (to override instead of “tblid”)
When “tbl” record is created, then we call a trigger to create the acutal table and base fields (and if the table is already created, we will ensure that we have the fields created we need)
Setup the “base fields for table” as a separate function to create each field individually (and remove the “s” at the end of “Table Title” so “Form Entered Date” instead of “Forms Entered Date”)
TODO Later
Setup link from “Table Properties” Sidebar (but this is secondary to the “Add Record” in the “Sub-Tables” section:
http://localhost:3000/#/form/14/?mainrecid=1100&id-tbl=1100&isadd=1
For "Main Address" where we pull the latest address based on "Addres Type" and "Current". Also For things like “PAR” where we prefill.
Basically we just create non-multirec Sib-Tbl of a Multi-Rec-Table with a tblfltr AND
- duplicate the prnttblidcol as "pt[tblid]" to use as the tbl.tbljoincol -
- so we only have the prntrecid set in the "pt" field for the one rec that meets the fltr and is first by sort
uses rec.triggers() - query if the tblnm has tbl rec where tbltpid=6 - (or we could use a tbl.isdervs flag to avoid the query)
- and if so then we find the "derived-recid" and check if matches the existing one, if not then update to the existing one (and unset the old one)
- changes the tbl.tbljoinprntcol
"tblfltr" is not necessary in tblusestr when tbltpid=6 - because its just used to select the "derived-rec" - so just have it ignored in the tbluse
Suggested-Uses: “Latest Communication” or “Largest Donation”/“Latest Donation”
Uses tbl.fldttlprefix for when there are multiple of the same table, like "Main vs Shipping Address" or “Largest Donation” vs “Latest Donation”
TODO:
could use the "isdervs" fld (set to true on all the tables of the same tblnm) to make the query lighter in rec.triggers()
http://localhost:3000/#/form/6/?&mainrecid=1&&id-fld=1049
If the table is used as a sibtblid then we just do “vw=0” to hide it
If the table NOT is used as a sibtblid then we delete the fields
Delete List (with ispurge):
http://localhost:3000/rec-del/&tblid=4&recid=[list-tblid]&ispurge=1
vs - Disable - ??? Use tbl.tblfltr for “istblon=1” because “vw” is reserved for trully hidden recs - so will have a separate table and o’s for “Disabled Tables”
## ispurge
“Delete/Clean-Out Table” option for developers (which really cleans it out)
When we delete a table record, we don’t get rid of the actual database table - unless we do “ispurge” into the delete function
When forms based on that table then delete them
Sub-forms are usually for multi-rec tables, meaning we pass in the “prntrecid” into odata based on fldsec.useotbluseid, so after we get load the data for the main/parent-form we can pull the “prntrecid” from the data via “ t[fldsec.useotbluseid]_id”
For Sub-Forms which are for sngl-rec-tables under a multi-rec-table, like “Case Accounting Details” under Case, they are treated the same way as other multi-record sub-forms, so we pass in the “prntrecid” using the “fldsec.useotbluseid” for “Case” table.
For Sub-forms for truly non-multi-rec tables, meaning they aren’t under any multi-rec-tables, like “Fam Details”, we just pass in the mainrecid (there shouldn’t be a fldsec.useotbluseid, but if there were it would just be redundant to the mainrecid)
We always pass the mainrecid into the sub-forms/fldsec (just in case the “prntrecid” is not passed in)
For a sub-form for sngl-rec-table or with the same o.tblid as the parent-o, like “Fam Details”, we pass the “forrecid” (if its under a multi-rec table) or we just pass the mainrecid
Sub-form will always be o.ismultirec, unless it is for the maintblid, because even if for a singl-rec table like “Accounting Details” it will use the “o.tblid” for the nearest-parent multi-rec tbl
“useotbluseid” is for the parent-o-tbluse.tbluseid - we don’t really need it if we use the tblkey lookup for the tbluse records, but its easier/faster to have it saved
For Form-Block-Titles and/or Section-Titles where Not-isttl - we use the “customize-visible” class that will hide it normally - to show these elements when customizing - we remove this class from all elements
Auto-name the fldsec.ttl based on the sub-form (o.nm) - via an ifnull() for the fldsec.json - so that the two stay in sync until a name is set - but this way makes it to where it will technically be empty when we open the edit-field-val-popup
could be “Template” (when fldsec.otp=3)
To keep the QS simple and shorter - we just use “fltr-[oid]” and “sort-[oid]”, instead of serializing JSON, for each of the sub-forms.
When “isdt” then we don’t do sub-forms
when not “addoid” then use “edtoid” - link is setup with all the tbluse-ismutlirec-tables - since it can be set directly as the href (instead of dealing with onclick)
## Sub-Form Loading
When the sub-form tblid == maintblid then we don’t pass the “prntrecid” or “snglrecid”
When the sub-form ismultirec and the tblid != parent-o.tblid then we use pass the “prntrecid” into the sub-form, based on the fldsec.useotbluseid
When the sub-form ismultirec and tblid == parent-o.tblid then we pass the “forrecid” into the sub-form, based on the o.tbluseid - example: “Case Details” sub-from of a “Case Overview” multi-rec sub-form -
Sub-form will always be o.ismultirec, unless it is for the maintblid, because even if for a singl-rec table like “Accounting Details” it will use the “o.tblid” for the nearest-parent multi-rec tbl
“useotbluseid” is for the parent-o-tbluse.tbluseid - we don’t really need it if we use the tblkey lookup for the tbluse records, but its easier/faster to have it saved
Sub-Forms load into their “slots” (based on “recid” and “useoid”)
When the sub-form prnt-tbl is not the main-tbl, then we get the id from the div-class-“datacntr”, via the “data-[tblkey]” attribute
think: Family Form -> Cases Sub-Form -> Communications per Case Sub-Sub-Form
“isdt” will call and use a different schema file, so for main-multi-rec-datatable forms we have to pass in the “isdt” to the o-class.
“isdt” is a “view-type” so any multi-rec form can be viewed either way; which changes the qs “isdt-[oid]”
“isdt” can be overriden in the fldsec when its being viewed as a sub-form. fldsec.isdt uses ifnull for useoid.isdt, so once its set in the fldsec, it has to be managed there.
“isdt” default to true on the “o” rec for Reports and Multi-Rec Forms - but can be changed, like for the “My Case-Load” form on the “Home” dash, which is basically a Report viewed as a Form
For main-multi-rec forms we use the storage for “o” to know if should be viewed as a multi-rec form
===
# Adding vs Editing Records
Pass 'isadd' in the qs when adding a record to make it very clear between adding a new multi-rec and editing multi-recs at once. (because "edit=1" by itself is confusing)
When editing a single record - the route will be like "#/form/[oid]/&mainrecid=x&id-[o-tblkey]=x&edit=1" - but when adding it will be "#/form/[oid]/&mainrecid=x&isadd=1"
“mainrecid” always refers to the main-table and we always use it as part of the query (as an extra security precaution to make sure that user doesn’t try to change the QS, or that we just don’t get rec-ids mixed up)
When the o.tblid is for a multi-rec table, we pass in either “forrecid” that is based on the o.tblid, or we pass in the “prntrecid” to filter by the o.prnttbluseid->tblidcol.
!!! When o.main - we pass in the “mainrecid”, from the route, but the prntrecid and forrecid can only be known once we load the schema and use the “tblkey” to look for the ids in the QS.
We filter by the parent rec id on the parent-table instead of filtering on the actual multi-rec table so we don’t have to worry about how the multi-rec-table is joined, and because the tblusestr includes all the parent tables up to the main-tbl, so filtering on the parent of the multi-record table is sufficient.
[But we should see if there is a performance gain if we filter directly on the target multi-rec table]
To show all recs, we just don’t pass in: mainrecid, snglrecid, or prntrecid
2018-02
===
Passing Rec-IDs in QS
We rely on the “tblkey” which should be unique so the qs would be like “&id-[tblkey]=[recid]”
When o.ismultirec then we have to pass the "snglrecid" or "prntrecid", which we get via the qs like “&id-[tblkey]=[recid]”
(We can view multi-record forms, like “Coms Overview” or “Docs for Case”, by themselves (they don’t have to be inside of fldsec), in which case we use the o.tbl->prnttbl->tblkey to get the id from the QS)
"fltrecids" - for "extra-multi-rec-tables" - (meaning tbluse where ismultirec but tbluse.tblid <> o.tblid and tbluse.tblid <> o.maintblid), like “Case” on Main Forms:
We pass all the the "id-[tblkey]" to Odata - in the same format as they are passed around on the client - then we find the ones to use based on the tbluse records
The schema on the client contains the tbluse, which we loop through (but only for the "extra-multi-rec-tables") to search the qs for key equal to "id-[tblkey]"
Its only a little redundant to deal with the "fltrrecids" on the client (instead of passing all of them to Odata), because we use them to show in the title like “Smith, Joe and Jane - Document Details - Case ID: 123 - Document ID: 567”
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
When o.ismultirec - or no mainrecid then we first do a record count, like “select count(*) from [tblusestr] where …” (no order by)
if the rcount is less than the the “recnum”, then we don’t do paging.
We return the record count as part of the data, like { data:{}, rcount: }, but only when not returning all the records, otherwise we can just count the data.length.
So when o.ismultirec - we check if the “data” returned has a “data” collection, and if so then we use that as the “data.data” and then “recnum”.
When Not-Report we show a simple Nav bar with just “Records 10 of 21” which is a dropdown to show “Show Records [Next Set]/11-20”, “Show All Records” or “Show 100” records (so the recs/page is predefined but can be overriden per QS)
??? Records Count when Filter is applied ??? - won't be true record count
(10 records being the system default for non-report MR) -
- with the next drop down being “Sorted by [Field]”, which is where they can change the sorting too, in case.
- for Filters we show each as a separate row in the Nav.
Reports use a completely different Recs/Page and Record Count System because we show Record Counts per each multi-record table
For Reports, we allow full Pagination
??? We really don’t want the user to be able to pull more than 100 records on the form because that can take a while and will slow the page down, so do we really allow them to “Show All Records” if there more than 100.
- so we really need to get them to use the “Search” to find the records that they need, instead of “paging” through them.
==================
# Sorting
Can be sorted by multiple fields
we need the sql and the fld-use-ids and asc/desc - for the qs
Default sorting is set by flduse recs where fldusetp=3 - which sets the o.sort (a sql string for the "order by"),
and sets the schema."sortfu" a CSL, because this is what is used by the o-class to show what is sorting the form
Default sorting defaults to "TblIDcol" desc ("Latest")
For multi-field on the fly sorting we pass the "sortfu-[oid]" as a CSL with the "sorttp" matching its order.
For o.isverflds or fldsec.isverflds - we just have the col set to 1 in the oformGen so we can switch back and forth between vertical and horizontal without having to resetup order
(same for sections)
fldsec.prnt is for “form-blocks”, where each block is a “grid-row”, and each block/row can have a number of columns set, like 1-4, defaults to 1 column but usually its 2 columns, - these
form-blocks can only change their row(ord) to move them up or down (so they don’t use the “col”)
sections are put in block-columns by “prntcol” (on the fldsec, meaning the section record not the block record)
each block-column is like a separate grid-section
so regular-fldsec and flduse are different from form-blocks because they can be set as Not-isver which allows multi-cols/rows
most sec will be vertical (not multi-col) and the user will set the form-blocks-cols-num (fldsec.colsnum) to have multiple-cols in that form-block
isverflds means that only a single column, so to have more than 1 column it becomes a “horizontal” form where we add new fld (or sec when for fldsec) as a new column, unless there are over 6 (config-auto-col-max-num) in a row (so its not overcrowded), even though we can technically have 12 cols.
We only show the “Move Field/Section” as “Row 1 - Col 1 (FldTtl)” if Not-o.isverflds and for fldsec if Not-fldsec.isversecs
For “Move Section” we show a separate ddl for “Form-Block-Col” when the fldsec.colsnum > 1;
Although its possible to have “Form-Block” with multiple-cols and have Not-fldsec.isversecs, this type of configuration gets complicated so we want to keep the user from selecting it through the UI - so if a form-block has colsnum set to > 1 we want to set the fldsec.isversecs = true for that form-block, but then we could manually override this to allow such an advanced config
For o.isverflds or fldsec.isverflds - we just have the col set to 1 in the oformGen so we can switch back and forth between vertical and horizontal without having to resetup order - (same for sections fldsec(prnt).isversecs)
We can move a field to any section as long as Not-fldsec.useoid - meaning its a sub-form
Form-Blocks aren’t created until the user moves a section to a form-block-2 or they move a section form-block-col-2 - which are going to be separate ddls (shown after checkbox for “Advanced Options”
So we may actually not need “fldsec.colsnum” because it will be automatic calced from max(fldsec.col)
fldsec.isgrid is redundant to isverflds - so it won’t be used
# Moving fields/sections and keeping row/col sequential
Rows and Cols are always sequential, its colsize that allows some columns to be offset/larger/smaller, but this is only when Not-isverflds/isversecs.
When we move using the “tofu” method, we first add +1 to “row” (when isverflds) or +1 to “col” on the same row (when not-isverflds) to all recs using the same parent (either oid or fldsecid);
When moving a field/sec down or up in the same parent(sec/row-when-not-isverflds):
when moving down: we do -1 to row/col where row/col <= tofu.row/col and row/col > old-row/col
when moving up: we do +1 to row/col where row/col <= tofu.row/col and row/col < old-row/col - still confused about this last part but seems to work with “row/col< old-row/col”
(when moving different parent sections (or rows when not-isverflds) then we just do the +1 to row/col to push the others out of the way)
3 to 5 (moving down)
-1 for 4, 5;
5 to 3 (moving up)
+1 for 3,4; (no minus needed)
To make the ord sequential:
loop by each section, (with form-blocks having each column treated as separate sections), get the recs for that section, sort by row, loop by row, sort by col, loop by col and set the indexed row/col/ord
For flduse we have to keep the “ord” based on the form-block, field-section, so we first make the form-block order sequential (just rows) and then field-sections and then flduse, so it has to all be done in one big sweep, with separate variables for form-block-ord/row, field-section-ord/row/col, and flduse-ord/row/col.
When isverflds we make the col as 1.
Each tbl rec has an "vwoid" field to define which of the forms to open (like from Reports), so this is used for the Cover-Page setting
Instead of having different cover-pages setup per program (or case-type), we do conditional field and section visibility - because this makes the forms more standardized, but yet only show what is needed for that program/case-type
Tabs are for sub-records/details-forms of an mainrec
Tabs are just like the sidebar links (there is no difference in functionality, just in appearance)
Tabs are “link-lists” set per maintbl, with the “cover-page” tab being a non-editable default (to change which o is used for the cover-page use tbl.vwoid)
Each tab has an “oid” defined, so we can route to a form without specifying the tabid in the QS, and if there is no matching tab, then lookup tab by “tblid”, and if none match then we don’t show any of the tabs as “active”
- so we keep the tabs config in the local-storage
Each “o” has multiple “link-lists” (1: sidebar, 2: “sidebar-adv-options” )
o.sidebar-links are per “o” - so its contextual to when that “o” is shown - (sub-forms can’t have sidebar links)
For multi-record forms:
When-forrecid: then only show the “edit record” link by default
When-not-forrecid: then show “add record” link by default
We can use special lnk.lnkurl like "/#/hub/185/?{[qs]}"
"sidebar-links" - vis
- disabled for now - the links are loaded before the data, so that we don't have to wait on the data to render the links so the ones with vis-conditions might need to be hidden until we get the data
"sidebar-links" - for "hub"/"isdt" - ??? Could be an override for the link, like "Show as DataTable" but then would need a "Show as Form" option too.
Each list is treated as a “main-rec” - with its own form - but we use a single form for the overview, add/edit (no details)
- so we will have a lot of redundant forms, but that will allow us to handle attributes
- default form only has item-title, hint, ord, enabled, color
- setup is done via rec.trigger - when insert tbl and istbllst = 1 - then add extra flds for enabled and color, and automatically setup new form
List-forms are hidden from the “Forms Lists” - but they have to be editable - so we use the “vw” flag to hide them because they are only accessible via the “/lists/[tblid]” route
"gohref()" is very important for multi-record forms because it returns the user back to this form (unless onsaveisedit which means its a multi-part-add/edit-form)
“ord” uses the special “order-trigger” which re-orders the recs sequentially based on the parent-recid,
tbl.istblord to determine if using the ‘ord’ column
(For special things, like "Sidebar Links")
First create as new MR Table under the parent table - and then set the tbltpid = 5 and then call /tbl-basefldscreate/&tblid=
“Main-Programs” are just a way of grouping “Main-Entities” that belong to those programs, mostly for visual reasons, like when doing Permissions.
All Main-Entity Records are stored in the “ent” table, so their basic data is shared across all programs, and we filter by the “etp” chk-list-field when not using the “mainrecid” to show for just one record.
Coms/Docs are shared between “Main Entities” records, unless they have the “etp” field set, meaning that record is only shown when seeing it through that main-entity-type.
(So these shared multi-rec tables are different from the sngl-rec tables like “Family Details”, in that we filter the “etp” by where empty or the “etp” matches the “etp” of the “ent” rec.)
Os (Forms/Reports/Templates) are based on a Main-Entity (except when based on the special “All-Main-Entities”, where we don’t apply the “etp” filter
Each Main-Entity has its own case table.
Main-Table, and then “Entities within Program”, so that {Adoptive-Family, Child, Birth-Parents} are all under the “Adoption” main-table.
“[Professional] Contacts” are a top-tier/Main-Table, broken into “Person” or “Org”.
(We don’t do “Main-Programs” as the top-tier table with “Main-Entity” under it, because each of the “Main Entities” is a multi-rec table so it wouldn’t make sense to show Families and unrelated children on the same report, just because they are in the same program.)
When we do search for a BSL - we have to search all entities, even if we are really only supposed to ref certain kind of entities (like “Case Manager” field is only for Persons
To add records, we either use a drop-down like “number of children in home”, or we have an “Add Record” button in or under the section heading, with a “remove record” button for each record, where the “details/edit” links wouild normally be.
*** recnum is not passed in to odata or we pass the maxnum/show-all
Do not use pagination for multi-rec-sub-forms on add/edit forms
(which is why its important that sub-forms must be set as “editable”, otherwise a large-rec-count sub-form like “Coms Overview”, could end up on the add/edit form)
For saving recs on add/edit sub-forms we treat each record separatly, so a separate POST is made, and we store the “created-recid” in the hidden input, so if the form was resubmitted it would not create a duplicate record.
Sub-Sub-Forms are possible (like “States lived in” per “Person in Home”).
For mainAddRec Form - we load them as empty section
each sub-form is submitted individually - in the onsave
…
## Record Number Selection (o.ismrnumsel)
“Record Number Selection - Field Title” (o.mrnumselttl)
“Record Number Selection - Field Memo” (o.mrnumselmemo)
“Record Number Selection - Max Number” - (o.mrnumselmax) - When selecting a number of records to add
“Record Number Selection - Min Number” - (o.mrnumselmin) - When selecting a number of records to add - will automatically display this many records when adding/editing
…
the schema.formhtml contains the html we need so we store the html in a hidden div refed by the “oid”, so that when “Add Record” is called, we create a new form/div-container (id’d by the index) and copy that element to inside of it and we keep a “select” or hidden input with the number of records we have.
form-serialize should exclude the sub-forms-fields - (the sub-forms uses sep “form” tags so could try to check by the parent form)
(otherwise end up submitting a lot of data)
We show the “Delete Record” link at the bottom of a record-section - when the records that haven't been saved yet, but if they are saved already then that must be done through the view form (to keep the prm and this functionality simple)
Later:
When there are existing records and the user can delete them, then we have to “disable” the list items less than the rec-count and enable them if they delete recs.
test rec:
http://localhost:3000/#/form/30/?mainrecid=1
