UX Repeating Section Xbasic Loop Code

[jacktheripper]

Hi All,

I have a repeating section that I need to loop through 1 row at a time, and then submit the data for each row.

With the help of http://wiki.alphasoftware.com/Parent...+in+Version+11, I wrote the following code.

I commented out the loop portions, to first get the sql insert to work. It does in fact submit the first row in the repeating section. As soon as I uncomment 'for i = 1 to ncount' and 'if len(e.dataSubmitted.WoChildId[i]) < 1 continue end if' data does not submit, and debug(1) does not fire.

Can someone look through my code, and let me know what I am missing?

function afterDialogValidate as v (e as p)

debug(1)
	
dim sql as c	
DIM cn as SQL::Connection
DIM args as sql::arguments
DIM cr as sql::CallResult

 
sql = "INSERT INTO WorkOrders (WoChildId, WoCustId, WoTechId, WoOpenDt, Approval, WoStatus, WoProjectId, WoDescription, WoSrtDtTm, WoEndDtTm, WoPropSrtDt, WoPropSrtTm, WoPropEndDt, WoPropEndTm, WoRespReq, WoTechPay, WoEmpId) VALUES (:WoChildId, :WoCustId, :WoTechId, :WoOpenDt1, :Approval1, :WoStatus1, :WoProject_Id1, :WoDescription1, :WoSrtDtTm1, :WoEndDtTm1, :WoPropSrtDt1, :WoPropSrtTm1, :WoPropEndDt1, :WoPropEndTm1, :WoRespReq1, :WoTechPay1, :WoEmpId1)"

cn.PortableSQLEnabled = .t.
cn.open("::Name::ProSQL")

dim i as n
dim ncount as n = e.dataSubmitted.WoChildId.size()

for i = 1 to ncount
	 
  'Test to see if the row in the repeating section has data. 
  '       If not, skip to the next row.
  if len(e.dataSubmitted.WoChildId[i]) < 1 
    continue
  end if
  
  args.Clear()
  args.set("WoTechId", e.dataSubmitted.WoTechId[i])
  args.set("WoChildId", e.dataSubmitted.WoChildId[i])
  args.set("WoCustId", e.dataSubmitted.WoCustId1[i])
  args.set("WoOpenDt1", convert_type(e.dataSubmitted.WoOpenDt1[i], "D"))
  args.set("Approval1", e.dataSubmitted.Approval1[i])
  args.set("WoStatus1", e.dataSubmitted.WoStatus1[i])
  args.set("WoProject_Id1", e.dataSubmitted.WoProject_Id1[i])
  args.set("WoDescription1",e.dataSubmitted.WoDescription1[i])
  args.set("WoSrtDtTm1",convert_type(e.dataSubmitted.WoSrtDtTm1[i], "T"))
  args.set("WoEndDtTm1",convert_type(e.dataSubmitted.WoEndDtTm1[i], "T"))
  args.set("WoPropSrtDt1",convert_type(e.dataSubmitted.WoPropSrtDt1[i], "D"))
  args.set("WoPropSrtTm1",CTOT(e.dataSubmitted.WoPropSrtTm1[i]))
  args.set("WoPropEndDt1",convert_type(e.dataSubmitted.WoPropEndDt1[i], "D"))
  args.set("WoPropEndTm1",CTOT(e.dataSubmitted.WoPropEndTm1[i]))
  args.set("WoRespReq1",convert_type(e.dataSubmitted.WoRespReq1[i], "T" ))
  args.set("WoTechPay1",e.dataSubmitted.WoTechPay1[i])
  args.set("WoEmpId1",e.dataSubmitted.WoEmpId1[i])
  
  cn.Execute(sql,args)
  cr = cn.CallResult
  e.javascript = cr.text;
  cn.Close()

next i

end function

Thanks,
James

[Davidk]

Re: UX Repeating Section Xbasic Loop Code

Hi James,

Gotta check this first... your code, as listed, reads...

len(e.dataSubmitted.WoChildId[i]) < 1

Is this actually your code? And not...

len(e.dataSubmitted.WoChildId[i]) < 1

I also prefer using the repeating section info for getting row counts... but... just a preference.

'In addition, the e object will contain information about the repeating sections
'e.repeatingSectionNames - a comma delimited list of the name (container ID) or each repeating section
'e.repeatingSectionInfo - a property array with one entry for each repeating section.
'The e.repeatingSectionInfo[] array will have these properties for each entry:
' - .activeRow - the row in the repeating section that had focus when the Dialog was submitted
' - .deletedRows - a CRLF delimited list of rows that were deleted. e.g. 1 and 3
' - .rowCount - a count of the number of non-deleted rows
' - .totalRowCount - a count of all rows in the repeating section including rows that were deleted
' - .fieldsInRepeating section - a CRLF delimited list of fields in the repeating section
' - .dirtyRowsInSection - a CRLF delimited list of rows in the repeating section that were edited. (A row that was edited, then deleted will still appear in this list).
' - .dirtyFieldsInSection - a CRLF delimited list of fields in the repeating section that were edited. for example: PARTNO_A5INSTANCE3 is the 'PARTNO' field in row 3.
'-------------------------------------------------------------------------------------------

[jacktheripper]

Re: UX Repeating Section Xbasic Loop Code

Thank you so much Dave!!!

Iv'e been working on this for a couple of days now. I should have looked more closely and have been able to see the &lt; &gt; are HTML <>. The page that helped me with the code must have reformatted the < and > in the code.

My understanding behind the use of e.dataSubmitted.WoChildId.size(), is to grab how many rows have data in the repeating section. My UX onInitialize prompts the user on how many repeating rows. User enters a number, and the form continues to load. If the user fills in a row in the repeating section and then needs to remove it, Alpha does not delete the row, but instead hides the row. I have added code to the repeating section delete button to clear the data in that row. This way only data entered by the user is actually inserted into SQL.

Thanks again Dave.

James

[Davidk]

Re: UX Repeating Section Xbasic Loop Code

I know... I've been bitten by that before... but only on the desktop and only when creating a mailing. All your HTML goes bonkers and turns into &lt &gt if you edit the email message.

Your repeating section stuff is all great... I just wanted to confirm something. Would .rowCount give you what you needed? Not that you don't want to check for data anyway. I was just wondering if .rowCount would give you the same count as you need for looping.

[jacktheripper]

Re: UX Repeating Section Xbasic Loop Code

Hi Dave,

I looked into .rowCount and I would think it would work just as well, the explanation is short, but I assume it does not count delete(hidden) rows?

I stumbled on to the wiki page looking for a way to use a UX with repeating section without binding to any tables and submit the rows manually. So i based the code off that page.

The UX is used to create multiple records in a flat table setup.

I now need to go back and put some error checking in place, and also perform a sql lookup of all Child Ids that were entered. (lookup the id in another table and export the address info to excel and email)

So still have a ways to go.