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
• Column properties
• CrudTable component
• QueryTable component

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

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

2. install @whidb/restui-pr and its dependancies

   npm i @whidb/restui-pr
   npm i bootstrap date-fns formik primeicons primereact react-icons yup

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-pr/dist/css/restui.css"

CrudTable

This "whole enchilada" component creates a primefaces DataTable with crud editing ablities from the state and controller values returned from a useCrudApi hook and an array that defines the data's columns.

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

import React from "react"
import { useCrudApi } from "@whidb/restapi"
import { useCrudTable } from "@whidb/restui-pr"

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

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

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

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

• One column in the datatable for each row in columns array (unless table=false)
• Actions column with edit and delete buttons (Double clicking on a row also starts an edit)
• New button in header to create new records
• Column sorting turned on by default
• Global filtering
• Column specific filtering (the drop down next to the search input box)
• Csv downloads
• Dynamic form dialog with an input item for every row in the columns array (unless input=false). Appears when new or edit buttons are clicked.

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.

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)
  • 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
  • autocomplete
  • number - input will enforce that value is a number
  • money - displays as data formatted as money
  • select - select item, no need to set this manually, set automatically when the options parameter is set
  • custom - a custom input component is passed in using the input.customComponent prop

• table - optional. either false or an object of PrimeReact Column properties

  • 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 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
      • required - set to true if field is required (used by default yup schema validation)
      • default - the default value. Used for inserts, see example below for code using this field to set a calculated id value for new rows.
      • 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.
      • 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.
      • fieldSet - string - all fields with the same fieldSet value will be grouped in a fieldset box
      • 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'), }

      • newLine - boolean - force a new line before displaying input
      • fieldSet - all columns with the same fieldset value will be grouped together in a <fieldset> tag. e.g. fieldset="Options" The heading of the fieldset will be 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)

• format - for columnType: "date" fields the date format to use, defaults to MM/dd/yyyy (uses datefns date formats)

Code Examples

Simple Usage

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

const columns = [
   { 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 = [
   {  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

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

• the invisible parameter if true, 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, 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: <setvalue> }

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