Bill and Todd's Awesome Crud ui (primereact)

This library contains UI components to build dynamic tables and forms using the state and controller functions returned from the useCrudApi and useQueryApi hooks in the @whidb/restapi library.

Documentation

• Getting Started
• CrudTable
  • Column array
  • Converting from whireact library

Other components

• Data driven menu

Examples

• Drop Downs (e.g. select/options)
• URL parameters
• Expandable rows
• Table tips/Examples
  • Use display:none to hide a column but keep it in searches
• Forms tips/Example
  • Improving the look of your forms
  • Generating id values for inserts
  • Columns where the DB generates values (e.g. sids, insert timestamps)
  • Custom field validation with Yup
  • Force input to be uppercase
  • Custom actions
• Using sub components for custom UI

Get Started

1. Install @whidb/restapi and its dependencies. Follow the setup instructions in the restapi readme

   npm i axios @tanstack/react-query @tanstack/react-query-devtools
   npm i @whidb/restapi

2. install @whidb/restui and its required dependencies

   npm i @whidb/restui
   npm i react-router-dom bootstrap primereact primeicons

3. add these imports to your index.js

   import "bootstrap/dist/css/bootstrap.min.css"
   import "primereact/resources/themes/md-light-indigo/theme.css"
   import "primereact/resources/primereact.min.css"
   import "primeicons/primeicons.css"
   import "@whidb/restui/dist/css/restui.css"

CrudTable

This "whole enchilada" component creates a PrimeReact DataTable from data returned from the restapi useCrudApi hook, with crud operations, sorting, global and col specific filtering, csv download.

e.g. code to build a CRUD screen from the F2_CAT endpoint...

import React from "react"
import { useCrudApi } from "@whidb/restapi"
import { CrudTable, checkColumns } from "@whidb/restui"

export function Categories() {
   const [qcatState, qcatCtrl] = useCrudApi(`https://devccc.whi.org/api/crud/F2_CAT`)

   const cols = checkColumns([
      { name: "QCAT_ID", label: "id" },
      { name: "QCAT_NAME", label: "Name" },
      { name: "QCAT_SHORT_NAME", label: "Short name" },
      { name: "QCAT_WEB_URL", label: "Web URL Stub" },
      { name: "QCAT_ORDER", label: "Order" }
   ])


   return <CrudTable heading="Categories" state={qcatState} controller={qcatCtrl} columns={cols} />
}

This code returns a screen that looks something like this...

This has the following features by default, but you can use properties to modify or turn off some of these features

• Dynamic table with one column for each item in the columns array (unless table=false, see column properties)
• Actions column with edit and delete buttons (Double clicking on a row also starts an edit)
• New button in header to create new records
• Many different field types available dates, checkboxes, selects, etc... See the column properties section to see all the customization available.
• Dynamic form dialog with an input item for every item in the columns array (unless input=false, see column properties).
• Column sorting turned on by default
• Global filtering
• Column specific filtering (the drop down right of the search input box)
• Csv downloads

Properties

Commonly used properties

• state - the state variable from useCrudApi. required
• controller - the controller from useCrudApi. required
• columns - the array describing the columns to add to the table and form (see column properties below). required
• heading - heading to use for the table and form
• rows - turns on paging if number of rows is greater than this property, defaults to null
• yupSchema - yup Schema to validate edits

Other properties

• crud operation properties

  • noNew - if true, no insert button will be created
  • noEdit - If true, no edit button will be created
  • noDelete - If true, no delete button will be created
  • defaultNewItem - an optional object to use for inserts. default is an object with "" for all values that do not have a default specified in the columns array.

• CSV props

  • noCSV - if true no CSV button will be created
  • csvFileName - the filename to use for csv files, defaults to the heading value with spaces changed to underscores

• Styling

  • className - class name(s) to apply to the div surrounding the dataTable and form
  • style - style to apply to the div surrounding the dataTable and form
  • dialogProps - props to pass to dialog form
  • tableProps - props to pass to table. Can be any

• Heading

  • headerLeftSection - optional code to replace left section of header
  • headerRightSection - optional custom code to replace right section of header
  • customHeader - optional code to replace entire header
  • getDialogHeader = function to generate the heading for the edit dialog. defaults to state => (state.insertMode ? "Create new item " :`Edit ${heading ? heading : ""}`).

• Other props

  • rowExpansionTemplate - a function returns detail information for a row. See the primereact datatable docs. if specified this generates the other primereact code to make the row expansion work, e.g. the expansion column and a rowExpansionToggle trigger

  • noFilter - if true no filtering input will be displayed

  • customAction - a function that returns a component that will be added to the buttons in the actions column of the DataTable. Gets passed in the current row

  • onRowDoubleClick - code to replace the default double click action

Column Array

The UI components require an array of column objects that describe how to handle the columns in the generated datatable and form. For the DataTable these will create primeReact <Column> components. For the edit form these will create Formik <Field> components. HINT if you create the columns array using the option checkColumns() function (see example above) visual studio code will provide autocomplete functionality for these properties

Column Properties

• label - required, label to use for table column and form input field

• name - name of column in the data array. Can be null if this is a display only column of calculated data (e.g. uses the body table parameter. input: false, table: {body: row={row.total/2}}).

• columnType - The type of column to use in the table and form. if null, assumes a text field. (except when options is specified, see below). Can be one of...

  • date - converts Javascript Date values to a string format.
    (MM/dd/yyyy is the default, you can use the format property to specify a different date format)
  • number - input will enforce that value is a number
  • money - displays as data formatted as money
  • check - checkbox, defaults to Y/N as values (use checkValues property to specify other values)
  • Y/N - shortcut for a checkbox with Y and N values saved to the database
  • 1/0 - shortcut for a checkbox with 1 and 0 values saved to the database
  • textArea
  • select - select item, no need to set this manually, set automatically when the options parameter is set
  • autocomplete
  • picklist - a fancier select value from list component
  • picklistMulti - This one lets you select >1 value from a list. These selected values are usually not stored as part of the edit row, but are used to drive inserts into a join table, e.g. in the papers system selecting a list of studies associated with a paper, the selected studies are inserted into a paper-studies table.
  • custom - a custom input component is passed in using the input.customComponent prop
  • break

• table - optional. either false if the column should not appear in the datatable, or an object of properties to pass to the PrimeReact <Column/> component

  • table: false - dont include the column in the DataTable.
  • otherwise an object of property values that will be sent to the PrimeReact DataTable Column component.
    e.g. use body parameter to customize the cell contents.

    table: { body: row => {row.COL.toUpperCase()}, className="blue" }.

• input - optional. Either false if the column should not appear in the edit form, or an object holding properties for the Formik Field component

  • input: false - dont include the column in the form (or the in the insert or update statements, use the invisible if you need the column in the statement).
  • otherwise this is an object of properties to send to the Formik Field element, e.g. input: { className: "me-2" }
    • custom properties - These are properties we've created used by the dynamic form code
      • invisible - boolean - used for columns that need to be in the insert/update statements but are not entered by the user, e.g. a db generated id number.
      • default - the default value. Used for inserts, see example below for code using this field to set a calculated id value for new rows.
      • disabled - either a boolean or a function that will be passed the row values and return a boolean
      • noUpdate - boolean - if true, column will not show up for edit, but will appear for insert, e.g. for ids or names that shouldn't be updated.
      • format - for columnType: "date" fields the date format to use, defaults to MM/dd/yyyy (uses datefns date formats)
      • fieldSet - string - all fields with the same fieldSet value will be grouped in a fieldset box
      • newLine - boolean - force a new line before displaying input
      • yup - yup rule for column.(overrides default yup rule for that column) e.g.

        input: { yup: yup.string().required('First Name is required')
           .max(20, 'First Name: Max 20 chars'), }

      • picklist - an object of properties to apply to the picklist field and dialog
        • header
        • options - the options to select from
        • columns - optional, the column properties for the option columns
        • onSave - optional for picklist, but required for picklistMulti. picklistMulti's onSave is passed toAdd and toRemove arrays that contain the values to be added/removed. Picklist will just set the fields value to the value
    • any other properties will be added to the <Field> component as props

• options - an array of option values for a select column

  • Each element in the array must contain the keys "value" and "label". Other key-value pairs can exist in array
  • If this is defined, columnType will be assigned in code to "select", you dont need to specify columnType
  • The value displayed in the datatable will be the label value that corresponds to column value, i.e. it does the lookup
  • if the required property is not set to true, a blank row {value:"", label:""} will be added to the top of the options array if one doesnt already exist.

• checkValues - Optional. For "columnType: "check"" columns, this array defines the values matching checked and not checked e.g. ["Y","N"] (checked/true value is first in array)

• labelPos - left, right, top, bottom

• custom - the custom component for a "custom" columnType field

Code Examples

Simple Usage

const [qcatState, qcatCtrl] = useCrudApi(`${CRUDURL}/F2_CAT`)
if (qcatState.isLoading) return "Loading..."

const columns = checkColumns([
   { name: "QCAT_ID", label: "id"},
   { name: "QCAT_NAME", label: "Name" },
   { name: "QCAT_SHORT_NAME", label: "Short name" }
])

return <CrudTable columns={columns} state={qcatState} controller={qcatCtrl} />\

More complicated example

const [dbState, dbCtrl] = useCrudApi(`${CRUDURL}/fact-db`,
   { staleTime: 1000 * 60,
     processFetchedData: data => data?.sort((a, b) => new Date(b.DB_FREEZE_DATE) - new Date(a.DB_FREEZE_DATE))
})

const maxId = getNextId(dbState.query.data, "DB_ID")

const columns = checkColumns([
   {  name: "DB_ID", label: "Id",
      input: { default: maxId, style: { width: "3em" }, required: true }
   },
   { name: "DB_SHORT_NAME", label: "Short Name", input: { required: true } },
   { name: "DB_DESCRIPTION", label: "Description" },
   { name: "DB_FREEZE_DATE", label: "Freeze Date", columnType: "date" },
   {
      name: "DB_FILE_ACRONYM", label: "File Acronym",
      input: {
         style: { width: "5em" },
         yup: yup.string().required().max(3, "Max of 3 characters allowed")
      }
   },
   {
      name: "DB_HOST_PORT_SID", label: "Host:Port:Sid",
      input: {
         default: "SNAPSHOT:1521:XXX",
         yup: yup.string().required()
         .matches(/\w+:\d+:\w+/, "invalid value for Host:Port:Sid")
      }
   },
   { name: "DB_USERNAME", label: "Username",
     input: { default: "WHIDEVO", required: true }
   },
   { name: "DB_ALLOW_QUERY_RUN", label: "Allow Query Run", columnType: "Y/N" },
   { name: "DB_NEEDS_PASSWORD", label: "Need to specify password", columnType: "Y/N" }
])

return <CrudTable heading="Databases" columns={columns} state={dbState} controller={dbCtrl} rows={8} />

• useCrudApi
  • using processFetchedData to sort data after fetching
  • using staletime to avoid refreshes within a minute
• Column options
  • using columnType option
  • using nextId function to get next id value. Note that i'm using state.query.data instead of state.data. This is because filtering doesnt change state.query.data but does limit state.data
  • using default values
  • using required option
  • sending style values to input itmes
  • yup schemas to validate input fields

Screen with lookup fields (options) and generating id values for inserts

This screen will create a crud table and screen for datasources

• it calls useCrudApi to query audience rows and uses those as option values for the DSRC_AUD_ID field. The table will use the options array to display the AUD_NAME value that corresponds to the DSRC_AUD_ID value.
• a default value is set for the id column based on a function that calculates the next id value.

const [dsrcState, dsrcCtrl] = useCrudApi((`${CRUDURL}/F2_DATASOURCES`)
const [audState, audCtrl] = useCrudApi((`${CRUDURL}/F2_AUD`)  //not using crud ops

if (dsrcState.isLoading) return "Loading..."

//add value and label fields required by options parameter
const audiences = audState.data.map(row => ({...row, value: row.AUD_ID, label: row.AUD_NAME }))

const maxId = getNextId(dsrcState.query.data, "DSRC_ID")

const columns = [
   {name: "DSRC_ID", label: "id", input: { default: maxId }},
   {name: "DSRC_NAME", label: "Name"},
   {name: "DSRC_AUD_ID", label: "Audience", options: audiences}
]

return <CrudTable state={dsrcState} controller={dsrcCtrl} columns={columns} />

Dealing with columns where the DB generates the values (sids, insert timestamps, etc...)

• For values generated by the database that dont need to be in the insert or update statements (e.g. insert_timestamps) you can just set input: false, or dont include the column at all if you dont want it in the table.

  { name: "XXX_INSERT_TIMESTAMP", input: false },

• if the invisible parameter if true, code will not show the field in the form, but will include the column in the insert and update statements.

  if values are generated by db, but the field is required, you can use the invisible prop to make them not display in the form, but still get the column included in the insert/update statements

  input: {invisible: true }

  For Client side generated values (e.g. fk columns from a parent table or calculated id values), pair this with the default parameter.

  input: {invisible: true, default: getInitialValue() }

Tips to improve the look of your forms

• Change the size of the form fields to match your data. e.g. input: { style: { width: "3em" }}
• Set the row and col properties for textarea columns. e.g. input: {rows: 5, cols: 100}
• Use fieldSet to group related input fields. e.g. input: { fieldSet: "Publish to" } on all related fields
• Use newLine property to force next inputs onto a new line. e.g. input: { newLine: true}
• use dialogProps to set custom width for small forms

Force input to be uppercase

use the html onInput attribute to do this...

      {
         name: "DB_HOST_PORT_SID",
         label: "Host:Port:Sid",
         input: {
            onInput: e => (e.target.value = e.target.value.toUpperCase()),
         }
      },

Custom Yup validation

use the input yup property to define custom validation. See the Yup library documentation for more detail. Note some basic yup validation is already generated by the system, this replaces that validation

{
         name: "DB_HOST_PORT_SID",
         label: "Host:Port:Sid",
         input: {
            default: "SNAPSHOT:1521:XXX",
            yup: yup
               .string()
               .required()
               .matches(/\w+:\d+:\w+/, "invalid value for Host:Port:Sid")
         }
      },

 input: {
    yup: yup.string().required().max(3, "Max of 3 characters allowed")
 }

min max for number

yup.number().min(min).max(max).required(),

Custom actions

Here's an example from the fact query screen.

I'm adding a "copy" button to custom actions that takes makes a new object from the data of the current row and calls setNewItem with that object to allow creating a new query from a copy of an old one.

customAction={row => (
<button onClick={() => qryCtrl.setNewItem(newQryFrom(row))} title="Copy">
<FaRegCopy />
</button>
)}

Expandable rows

Pass a function to rowExpansionTemplate to define a detail component that displays when the user clicks on an expansion icon. e.g.

rowExpansionTemplate= {row => <MyDetailComponent rowData={row}>}

Using sub components for custom UI

if you need a more custom solution you can use some of the subcomponents that make up CrudTable. or just use useCrudApi with your own UI code.

<DynamicHeader> - has the heading, global filter input, new and csv buttons
<DynamicTable> - creates a dynamic table based on column info
<FormDialog> - wraps a dialog around <DynamicForm>
<DynamicForm> - builds a dynamic form based on column info. Displayed when state.editItem is not null

for example if you want to replace the datatable with your own custom code to display the data, but still want the crud operations and the filtering and csv functionality, You could do something like...

*Note I did not include the properties you would need to pass to the subcomponents

const [dsrcState, dsrcCtrl] = useCrudApi((`${CRUDURL}/F2_DATASOURCES`)

return (
<>
   <DynamicHeader heading="Columns" columns={dcolCols} state={dcolState} controller={dcolCtrl} />
   {dcolState.data.map(row => (
      <div key={row.DCOL_ID}>
         {row.DCOL_ORDER} {row.DCOL_COLUMN_NAME}
         <button onClick={() => dcolCtrl.setEditItem(row)}> Edit </button>
         <button onClick={() => dcolCtrl.doDelete(row)}> Delete </button>
      </div>
   ))}
   {dcolState.editItem && <FormDialog state={dcolState} controller={dcolCtrl} columns={dcolCols} />}
</>
)

Utility Functions

getNextId(array, column)

returns the max value+1 for a numeric column in the given array. Use this to get default values for spid columns

Converting from whireact library

• whireact is now split into two libraries @whidb/restapi and @whidb/restui
• getNextId - state.data is now the data after filtering, use state.query.data for getNextId or other functions that need non filtered data
• picklist option changes
  • The columns array now has a picklist element that contains all the properties for the picklist, e.g. picklist: {header: "things"} instead of picklistHeader: "things"
    • You can now add an optional column array to control the columns that display in the datatable
  • Array doesnt have to have value and label columns if you use the valueCol and labelCol properties

Data Driven Menu

example of getting menu from table, building that and using as the reports submenu.

import { Menubar } from "primereact/menubar"
import { jsonToMenuModel, menuLink } from "@whidb/restui"

function PageWrapper() {
const [reportMenuResp] = useQueryApi([`${QUERYURL}/menu-items`, { id: "databuddy" }])
let reportMenuItems = jsonToMenuModel(reportMenu)

const menuItems = [
      menuLink("Publish Sets", "/pubsets"),
      menuLink("Data Sources", "/datasources"),
      menuLink("Find Columns", "/colFinder"),
      menuLink("Rest", "/rest"),
      {
         label: "Lookup Tabs",
         icon: "pi pi-fw pi-pencil",
         items: [
            menuLink("Audiences", "/aud"),
            menuLink("Categories", "/qcat"),
            menuLink("Data Types", "/dtyp"),
            menuLink("Db Connections", "/db"),
         ]
      },
      { label: "Reports", items: reportMenuItems }
]

   return (
      <>
      <Menubar className="no-print" model={menuItems} />
         ... other code
      </>
      )
}