Create form with custom layout Topic is solved

[nadir]

Hello,

I would like to create a form with a custom layout that looks like the following screenshot.

screenshot.png

Basically I want to give the user option to pick a date and based on the selected date populate a table. What would be the best way to go about it.

Thanks,
Nadir Latif

[kev1n]

Hi Nadir,

Use a Type "Run" object with Target "Iframe".
This will embed a (browse) form within another form.

For an example, see the demo at https://demo.nubuilder.cloud/ — check the "Run iFrame (Form)" tab.

[nadir]

Hi Kevin,

How can I build a browse form with custom fields and data. The data will be pulled from a database table. Should I use a Code Snippet. If so what should be the format for the snippet.

[kev1n]

"How can I build a browse form with custom fields and data" -> what do you mean by that? You can use an SQL query to fetch data.
Use the Form Builder to create a Browse form, specify the columns.

[nadir]

Well, the data can be fetched using SQL query but it needs to be processed a lot. A new column needs to be added by the processing. It cannot be done by SQL alone.

[kev1n]

You can use the Before Browse (BB) Event to fetch SQL data and programmatically process or modify row values as needed. Let me know if you need more information—if you can provide a more concrete example, I’d be happy to help further.

[nadir]

I want to pull data from the attendance table. It has the following fields:

employee_id, timestamp, type, comments, attendance_nulog

1. employee_id is the employee id. Its a foreign key
2. timestamp is the unix timestamp of when the attendance was recorded
3. type is either "check in" or "check out"

I would like to read data from the attendance table and populate a form that looks like the screenshot in the first post. The TimeIn, TimeOut and NumHours are derived from the timestamp field.

I can write Php code for fetching the data and processing it so its in the correct format. I would like to know the format of the Php code. For example should it return some array or call some function for populating the form.

[kev1n]

Write the PHP code to select the rows in the BB event, example (change table names, column names accordingly).
You can also filter by a date by passing a Hash Cookie and use that in your code (WHERE Clause).

Code: Select all

filterDate      = nuGetProperty('filter_date');

$select =
  "SELECT
     e.employee_id      AS employee_id,
     e.full_name        AS Name,
     FROM_UNIXTIME(MIN(a.`timestamp`), '%H:%i:%s') AS TimeIn,
     FROM_UNIXTIME(MAX(a.`timestamp`), '%H:%i:%s') AS TimeOut,
     ROUND((MAX(a.`timestamp`) - MIN(a.`timestamp`)) / 3600, 3) AS NumHours
   FROM attendance a
   JOIN employee     e ON e.employee_id = a.employee_id
   WHERE DATE(FROM_UNIXTIME(a.`timestamp`)) = '$filterDate'
     AND a.`type` IN ('check in','check out')
   GROUP BY a.employee_id
   ORDER BY e.full_name
";

nuCreateTableFromSelect('#TABLE_ID#', $select);

Then, in the Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID#

[nadir]

Hello Kevin,

The PHP code you sent works fine. Thanks a lot. Only issue is that I would like the TimeOut column to be "17:00:00" if there is no type column with value "check out". Also in this case the comments column should be "Not checked out".

[kev1n]

Great to hear it’s working! To add the logic where:

TimeOut should default to "17:00:00" if no "check out" entry exists.
Comments should be "Not checked out" in that case.

We’ll use a conditional with MAX(CASE WHEN a.type = 'check out' THEN FROM_UNIXTIME(a.timestamp, '%H:%i:%s') END) to detect if a "check out" exists, and IF(... IS NULL, '17:00:00', ... ) to substitute the default.

Here’s the modified query:

Code: Select all

$select =
  "SELECT
     e.employee_id AS employee_id,
     e.full_name   AS Name,
     FROM_UNIXTIME(MIN(a.`timestamp`), '%H:%i:%s') AS TimeIn,
     IF(
       MAX(CASE WHEN a.`type` = 'check out' THEN a.`timestamp` END) IS NULL,
       '17:00:00',
       FROM_UNIXTIME(MAX(a.`timestamp`), '%H:%i:%s')
     ) AS TimeOut,
     ROUND((MAX(a.`timestamp`) - MIN(a.`timestamp`)) / 3600, 3) AS NumHours,
     IF(
       MAX(CASE WHEN a.`type` = 'check out' THEN 1 END) IS NULL,
       'Not checked out',
       ''
     ) AS Comments
   FROM attendance a
   JOIN employee e ON e.employee_id = a.employee_id
   WHERE DATE(FROM_UNIXTIME(a.`timestamp`)) = '$filterDate'
     AND a.`type` IN ('check in','check out')
   GROUP BY a.employee_id
   ORDER BY e.full_name
";