4. Application with In-Memory Database
How to create an application connected to a in-memory database?
The Goal
The basic principle of all database applications is to create, read, update and delete records in persistent data storage. We will prepare an order application based on an advanced table editable through a side-bar panel with in-memory database integration.
The Description
We will prepare a basic layout following the in-memory database integration serving as a storage for data. Data variables and button events will bind actions for creating, reading, updating, and deleting table records. Creating or editing an order will trigger the sidebar panel checking data validation and the difference between required actions.
The Knowledge You Will Get
Use InMemory Database integration
Create actions both for reading and writing data
Create a master/detail View
Display a View in a sidebar
Use events to handle button actions
The Project Structure
1. Create a Basic Layouts
Let's prepare a basic layout structure for the Main content and Overlay sidebar.
Create the View[MainView
] showing actual orders with a Header containing a Button to Create New Order
Container [
cntRoot
] -> Flow ->Under each other
; Horizontal Alignment ->Stretch
; Padding ->Wide
; Spacing ->Medium
Container [
cntHeader
] -> Flow ->Into row
; Horizontal Alignment ->Stretch
; Vertical Alignment ->Middle
;Label [
title
] -> Value ->Orders
; Font Size ->Large
; Icon Name ->mdi/package-variant
; Icon Size ->Large
; Horizontal Alignment ->Left
; Vertical Alignment ->Middle
; Item Flex ->Stretch
Button [
btnNewOrder
] -> Value ->Create New Order
;
Advanced Table [
orderTable
] -> Spacing ->Medium
;Column [
Date
] -> Width ->15%
Text [
txtDate
]
Column [
Customer
]Text [
txtCustomer
]
Column [
Description
]Text [
txtDescription
]
Column [
Amount
] -> Width ->10%
Text [
txtAmount
]
Column [
#5
] -> Horizontal Alignment ->Right
Container [
cntItemActions
] -> Flow ->Into row
; Spacing ->Narrow
Button [
btnItemEdit
] -> Icon Name ->mdi/pencil-outline
; Size ->Medium
; Button Style ->Custom
; Style Background ->LIGHT_GRAY
; Style Border Radius ->100px
Button [
btnItemDelete
] -> Icon Name ->mdi/trash-can-outline
; Size ->Medium
; Button Style ->Custom
; Style Background ->LIGHT_GRAY
; Style Border Radius ->100px
The following View[OrderForm
] serves as the content of an Overlay sidebar, containing editable fields of existing or new orders.
Container [
cntRoot
] -> Flow ->Under each other
; Horizontal Alignment ->Stretch
; Role ->Form
; Padding ->Narrow
; Spacing ->Medium
Text Input [
fldCustomer
] -> Placeholder ->John Doe
; Label Value ->Customer
; Required ->true
Text Input [
fldDescription
] -> Placeholder ->Some Order
; Label Value ->Customer
; Required ->true
Number [
fldAmount
] -> Placeholder ->Amount
; Label Value ->Amount $
; Required ->true
Date and time [
fldDate
] -> Label Value ->Date
; Required ->true
Container [
cntButtons
] -> Flow ->Into row
; Horizontal Alignment ->Right
; Spacing ->Narrow
;Button [
btnSave
] -> Value ->Save
; Type Role ->Submit
; Width ->90px
;Button [
btnCreate
] -> Value ->Create
; Type Role ->Submit
; Width ->90px
;Button [
btnCancel
] -> Value ->Cancel
; Type Role ->None
; Background ->Secondary
; Width ->90px
;
2. Connect inMemory Database
Connecting in-memory Database will be immediately available after creating an Integration.
inMemory DB[
OrdersDB
]Primary Key ->
id
; Auto generate ->UUID
3. Create an Order Action and Bind it
First, bind the Create New Order
Button with an action sending data into the in-memory database. Creating a new unique UUID record in-memory Database based on the fourth required input parameter.
Create a
CreateOrder
Action, saving new records into the inMemory DatabaseInput Parameters:
Name ->
customer
-> String; Required ->true
Name ->
amount
-> Float; Required ->true
Name ->
description
-> String; Required ->true
Name ->
date
-> String; Required ->true
inMemory DB / Insert Parameters:
Document ->
ƒx(/* inMemory DB / Insert)
ƒx(inMemory DB / Insert):
Bind the CreateOrder
with the Button btnCreate
on Click Event in OrderForm
.
Because Creating New Order
would be a part of an Overlay sidebar, it's essential to close it after sending data to the in-memory Database. Even a success toast message would be in place.
On Event -> Call Action -> Select ->
CreateOrder
; Parameters:customer ->
fldCustomer.value
amount ->
fldAmount.value
description ->
fldDescription.value
date ->
fldDate.value
On Success:
Close Overlay -> Overlay ID ->
orderForm
; Button ID ->submit
Show Toast Message -> Type ->
Success
; Message -> "Order has been created.
"; Duration(sec) ->3
4. Read All Existing Data and Bind Items with Table
Reading all existing data from the in-memory Database is passed by Action Variable. In our case, it's named orderList
, which data is referenced by Advanced Table. You can seek its data structure in the Data Explorer.
Create a new
GetAllOrders
Action is returning all existing data from the in-memory database.On Start -> inMemory DB / Get All
The orderList
Variable provides the connection between the in-memory Database and MainView
. Keep in mind that any changes have to be reflected by reloading the Variable.
Add Data from Action -> ID ->
orderList
; Action Name ->GetAllOrders
Associate existing
orderTable
-> Items ->orderList.data
5. Associate Table Items with orderList
Datasource
orderList
DatasourceMaking our Table life means associating required Item elements Values with orderList
Variable. Remember that the data Array is now part of the orderTable
, which is visible under the name items
in the Data Explorer. But, if you will select or associate any of the existing Table components, its map structure will appear item
in the Data Explorer.
txtDate
-> Value ->DATE_FORMAT(item.date, "yyyy-MM-dd")
txtCustomer
-> Value ->item.customer
txtDescription
-> Value ->item.description
txtAmount
-> Value ->"$" + FORMAT_NUMBER(item.amount, 2, ".", ",")
6. Open&Close Sidebar Panel on Create New Order
Both of our Views are separated until we bind them by putting OrderForm
into an Overlay sidebar panel.
Clicking on the Create New Order
, Button will trigger the Overlay sidebar panel; closing it a force to reload the orderList
Variable to refresh the content of the Table.
Create an on Click Event in
btnNewOrder
Button.On Event -> Open View in a Sidebar -> Overlay ID ->
orderForm
; Size ->Medium
; Header Title Value -> "Create Order
"; Header Font Size ->Medium
; Header Icon Name -> "mdi/asterisk
"; Icon Size ->Medium
; View ->OrderForm
On Close -> Update State -> Method ->
orderList.reload
Clicking on the Cancel Order
Button will simply close the Overlay sidebar panel.
On Event -> Close Overlay -> Overlay ID ->
orderForm
; Button ID ->btnCancel
7. Delete Record on btnItemDelete
Action
btnItemDelete
ActionAny single of Items orderTable
has its own icon for Deleting records. First, let's make DeleteOrder
Action and bind it with the Delete Button.
Deleting required data is associated with the Input id parameter.
Create an
DeleteOrder
Action.Input Parameter:
Name ->
id
-> String; Required ->true
On Start -> inMemory DB / Delete -> Key ->
params.id
Over-clicking on the Delete Item Button is secured by an Open Confirmation Dialog message.
Create an
btnItemDelete
on Click Event.On Event -> Open Confirmation Dialog:
Overlay Header Title -> Value -> "
Confirm Delete
"Overlay Header Icon -> Name -> "
mdi/delete
"Text -> Value -> "
Do you really want to delete order ${item.description}?
"Confirm Button -> Text Value -> "
Delete
"; Background Color ->ERROR
Cancel Button -> Text Value -> "
Cancel
"; Background Color ->SECONDARY
On Confirm -> Call Action ->
DeleteOrder
; id ->id
On Success -> Update State -> Method ->
orderList.reload
8. Editing Existing Records
For editing the existing records, we will use the same OrderForm
View. That means we have to add an Input parameter containing a record id, create an action reading exact data, and handle the difference between save and create buttons.
Add Parameter -> Name ->
id
-> StringCreate an
GetOrder
Action.Input Parameter:
Name ->
id
; -> String; Required ->true
On Start -> inMemory DB / Get -> Key ->
params.id
Add Action Variable in
OrderForm
View:ID ->
orderEntity
; Action Name ->GetOrder
; Parameters id ->params.id
Enabled ->
params.id != null
It's good to notice that GetOrder
Action would be enabled just if the input id parameter won't be empty.
If we are editing a record, the input parameter is not empty. It's also a logic parameter switching between creating and saving buttons.
btnSave
-> Visibility Render ->params.id != null
btnCreate
-> Visibility Render ->params.id == null
Fields Values are associated just if in the orderEntity
existing loaded data.
fldCustomer
-> Value ->orderEntity.data.customer
fldDescription
-> Value ->orderEntity.data.description
fldAmount
-> Value ->orderEntity.data.amount
fldDate
-> Value ->orderEntity.data.date || ""
9. Updating Existing Records
The last Action we will prepare in this tutorial named UpdateOrder
saving the specified id
record by clicking on the btnSave
Button.
Create an
UpdateOrder
Action.Input Parameters:
Name ->
customer
-> String; Required ->true
Name ->
amount
-> Float; Required ->true
Name ->
description
-> String; Required ->true
Name ->
date
-> String; Required ->true
On Start -> inMemory DB / Update -> Key ->
params.id
Document ->
ƒx(/* inMemory DB / Update)
ƒx(inMemory DB / Update):
Create and on Click Event of
btnSave
.On Event -> Call Action -> Select ->
UpdateOrder
; Parameters:customer ->
fldCustomer.value
amount ->
fldAmount.value
description ->
fldDescription.value
date ->
fldDate.value
On Success:
Close Overlay -> Overlay ID -> "
orderForm
"; Button ID -> "submit
"Show Toast Message -> Type ->
Success
; Message -> "Order has been saved.
"; Duration(sec) ->3
10. Validating The Order Form
The last step of our tutorial would be to set up the OrderForm
Validation, checking up on missing input Values. Until the Form isn't valid, the save or create Button won't be enabled.
Create a new State Variable
isFormValid
:Initial Value ->
fldCustomer.valid && fldDescription.valid && fldAmount.valid && fldDate.valid
btnSave
-> State Enabled ->isFormValid.value
btnCreate
-> State Enabled ->isFormValid.value
The Conclusion
Making the basic nice-looking CRUD application using the in-memory Database cannot be easier. We have done a great job in the ten steps.
Last updated