Can I make a type of Repeating Section for the top-level table of a UX Component?

[acceptthis]

I want to enter several records at a time, like the repeating section in a UX, but I don't want it based on a one-to-many table; I'd like it based on the top-level one-to-one table. Maybe it might be best to use an "Add records only" Grid, but a Grid doesn't give me the flexibility for easier data entry like a UX component does-- at least I don't know how to do it in a Grid if it does.

What I'm attempting to do is build a Time Sheet for payroll entry. I created a UX with the employee as the top-level and the hours entered as the one-to-many table, but I'm getting a few errors. This is where I am:

2019-02-13 Labor Hours with error.png

The User enters the default employee and pay period above, and those become the defaults for the repeating section entries. The error I got in my first attempt is shown in the top part of the image...it's an error in the a5DialogHelper_Execute_CRUD_Statements() function, which I assume is a built in function that creates the SQL INSERT statement.

I decided to start over fresh, creating a more simplified UX, but then I got a different error. That error is shown in this image:

2019-02-13 Labor Hours2 with error.png

That's when I figured I was attempting create a one-to-many in a way alpha couldn't handle. Maybe it's because I'm attempting to "append" to an existing one-to-many. I don't know... That's when I decided to go back to entry from a Grid. It works, but I don't know how to create the defaults that would make data entry easy and less prone to error.

Is there a better direction to go? Where can I look to fix these errors, if they can be fixed?

[lvasic]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

You can do something like this. This is an editable list control. https://www.screencast.com/t/JhOJkPHw

[RichCPT]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Lee, that is really good. Are you going to publish an e-book or something on these great things you have come up with? (I think the above is one you showed us on IADN.)

[RichCPT]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Lee, (I hope I'm not giving anything away by asking) when you implemented the Window Overlay that goes over a row in a LIST when the users switches the row to edit mode, did you have to use the new feature that is supposed to prevent the user from tabbing out of the popup overlay or have you tested to see if the user is confined to the popup overlay? And, did you have to specify exact control widths & heights so the list display controls and overlay controls occupy exactly the same height and width?

[lvasic]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Rich,
I put this together a couple of months ago before that feature and I haven't tested that. The exact widths & heights are hard coded. I've been meaning to send you that component. Let me see if I can find it and send it.

[acceptthis]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

I've never done a list control and thought of using it here but was afraid of spending the time without results. You mentioning it, I feel more confident that I'll get results. Thanks so much. I assume this list control will allow me to continuously add to the list?

[lvasic]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Have you looked at the spreadsheetInput control? That might be something that works for you.

[acceptthis]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

nope. I'll look at that as well. Thanks.

[acceptthis]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Have you looked at the spreadsheetInput control? That might be something that works for you.

In my opinion (which isn't really worth much), the spreadsheetInput control wouldn't work for my situation. I want to have default data written each time I go to enter another row. The only way I saw that defaults could be written was during first initialization of the control. I couldn't find any way to get a default written with each new row.

I really like the list control option. It looks promising for me. I assume there is a way to dynamically filter the list based on two other controls on the UX? I'd want to filter the list based on the Employee and the Payperiod.

[madtowng]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

It sounds like you ran into a problem much like I did.
I wanted a way to enter multiple bloodsugar readings and wanted the
benefits of a repeating section.
I finally figured out that I would have to skip the no-code method to make
it work. Took extra time to do, but it works great now.
It's not committed to anything public yet because I want to group the
reading based on being near the same time.

[Davidk]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Brad, if you want to stay with a Repeating Section, then you'll need to use unbound data controls within the Repeating Section, and handle the table inserts in XBasic. Alpha passes all rows and all data of a Repeating Section into an Ajax Callback in a couple of formats... one of which is JSON. You can parse the JSON, then loop through each row and each column preparing a SQL Insert statement.

The attached UX takes you through this process.

ux_Payroll_Time_Entry.zip

[madtowng]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

This is probably one of the ugliest posts I've done, but it is 01:45.
The images below show that my UX has a repeating row section and no top level section.
The video in the zip file shows how it preset some values in added rows yet gives you the
option to adjust any of the prefilled items. Finally it shows the 4 rows added really did
get added to the table.

I'm not sure how you do payroll, whether it's all employees on day at a time, or all days
worked by an employee for a given payroll period, but the options are there.

canRptSectionRowAdd

ar arn = {dialog.Object}.getRepeatingSectionActiveRow('REPEATINGSECTION_PRICKS')
var val1 = {dialog.Object}.getValue('NICKNAME:arn')
var val2 = {dialog.Object}.getValue('METERTIMESTAMP:arn') 
var val3 = {dialog.Object}.getValue('STRIPGLUCOSE:arn')
var val4 = {dialog.Object}.getValue('STRIPSUSED:arn')
var val5 = arn
var ok = false

if(val4 != '' && val3 != '' && val2 != '' && val1 != ''){
ok = true
//	if(arn > 1){
//		
//		val5 = arn -1
//	}
//	alert(val5)
//	alert(val2)
//	{dialog.Object}.setValue('METERTIMESTAMP:val5',val2)
}
return ok

afterRptSectionRowAdd

var arn = {dialog.Object}.getRepeatingSectionActiveRow('REPEATINGSECTION_PRICKS')
var val5 = arn+1
var val1 = {dialog.Object}.getValue('NICKNAME:arn')
var val2 = {dialog.Object}.getValue('METERTIMESTAMP:val5') 
var val3 = {dialog.Object}.getValue('STRIPGLUCOSE:arn')
var val4 = {dialog.Object}.getValue('STRIPSUSED:arn')
if(val2 != ''){
alert(val5)
alert(val2)
	{dialog.Object}.setValue('METERTIMESTAMP:val5',val2)
	{dialog.object}.setFocus('NICKNAME',val5)
}

dialogValidate

dialogValidate.hasError = .f.
dialogValidate.errorText = ""
	if e.datasubmitted.recordtype="" then
		e.control.recordtype = "2"
		e.datasubmitted.recordtype="2"
			if .not. contains( e.dirtycolumns,"recordtype") then
				e.dirtycolumns = e.dirtycolumns+crlf()+"RECORDTYPE"
			end if
	end if
if e.datasubmitted.metertimestamp = "" then
	dialogValidate.hasError = .t.
dialogValidate.errorText = "Prick time cannot be blank"
end if	
if e.datasubmitted.stripglucose = "" then
	dialogValidate.hasError = .t.
dialogValidate.errorText = "Blood Reading cannot be blank"
end if

afterDialogValidate

dim processThis as p
dim ptArgs as sql::Arguments
dim ptCn as sql::Connection
dim ptRS as sql::ResultSet
dim ptSQLcode as c
dim ohoh as c
dim myCounter as n = 1
dim itworked as l

processThis = e.datasubmitted
if processThis.nickName.size() > 0 then
ptSQLcode = <<%txt%
insert into meterData (NickName,SerialNumber,MeterTimeStamp,StripGlucose,StripsUsed)
values (:a1,:a5,:a2,:a3,:a4)
%txt%
'''debug(1)
itworked = ptCn.Open("::name::personal")
	while myCounter <= processThis.nickName.size()
		ptArgs.Set("a1",processThis.nickName[myCounter])
		ptArgs.Set("a2",time("yyyy-MM-dd 0h:0m",convert_type(processThis.meterTimeStamp[myCounter],"T")))
		ptArgs.Set("a3",processThis.stripGlucose[myCounter])
		ptArgs.Set("a4",processThis.stripsUsed[myCounter])
		ptArgs.Set("a5",processThis.serialNumber[myCounter])
		itworked = ptCn.Execute(ptSQLcode,ptArgs)
		ohoh = ptCn.CallResult.text
		myCounter = myCounter+1	
	end while
	e.javascript =  "var ele = $('pricks');" + crlf() + "{dialog.object}.closeContainerWindow(ele);"
ptCn.Close()
end if

2019-02-14_1-13-13.png
2019-02-14_1-14-08.png

[acceptthis]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Brad, if you want to stay with a Repeating Section, then you'll need to use unbound data controls within the Repeating Section, and handle the table inserts in XBasic. Alpha passes all rows and all data of a Repeating Section into an Ajax Callback in a couple of formats... one of which is JSON. You can parse the JSON, then loop through each row and each column preparing a SQL Insert statement.

The attached UX takes you through this process.

[ATTACH]45623[/ATTACH]

Thanks for putting that code together. I like that it's handled in xbasic because I come from a desktop app background. However, I'm a novice with sql, and I got an error when entering one row. I got "too few parameters." This is the sql statement the code produced:

INSERT INTO LaborHours (EmpID, CalendarName, EstimateID, Description, Details, StartTime, EndTime, Lunch, Hours, PayPeriod) VALUES (8,"Steinfeldt, Brad",1,"This is a test","","2019-02-14 08:13:20","2019-02-14 08:13:20",1,3,"2019-02-14")

I changed your field names to match my field names.
Also, I attempted it again by adding a second row, and I got a different error-- "Missing semicolon (;) at end of SQL statement." I don't know enough about sql to know what's causing the error. I entered this sql directly, bypassing the coding loop, and got no error:

"INSERT INTO LaborHours (EmpID, CalendarName, EstimateID, Description, Details, StartTime, EndTime, Lunch, Hours, PayPeriod ) VALUES (33,'Green, Isaac', 1,'this is a description', 'this is detail','2019-01-20 08:00:00', '2019-01-20 08:00:00', 1, 3, '2019-02-09' )"

Is it a double quote vs single quote issue?

One last thing...if I add multiple rows in the repeating section and then delete them, the REPEATINGSECTION_1.size() reflects the total that were added; it doesn't go to the number of rows displayed. For example, if I add 5 rows and delete two of them, REPEATINGSECTION_1.size() is at a count of 6 (1 original and the 5 added). I inserted an if/then statement to skip the row if it was deleted, and that fixed the problem. There's a property on the repeating section, REPEATINGSECTION_1[].__deleted, that I used to skip any that were deleted.

[acceptthis]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

Gregg,

I watched your video, and it's very similar to what I want to do. I'll go through what you've done to see if I understand it. As for the way I do payroll and how it has been done on our desktop app, we'd enter one employee individually, entering the project and how long he/she's was working on that project, entering all the projects and time for the entire pay period. Then, I had a duplicate button that would duplicate his entries for any of the other employees that worked with him. Worked slick, but converting it to web based is proving to be very complicated.

[Davidk]

Re: Can I make a type of Repeating Section for the top-level table of a UX Component?

I've changed table and fieldnames to what you're using and changed the double-quotes to single. All looks ok.
Also, I'm using e.dataSubmittedJSON_NoDeletedRows now, which solves the deleted rows problem.

Which SQL back end are you using? My testing is with MySQL. Also, what build of Alpha are you using? Try this UX.

ux_Payroll_Time_Entry_a.zip