UX Populate Data from other Tables

[GGandhi]

Re: UX Populate Data from other Tables

also i am just stumped.
if you just want to fill in student data why not resort to auto suggest and fill in field sleet alpha do it for you instead of doing all this coding?

[Turnbullca]

Re: UX Populate Data from other Tables

if you need only information from the student table you don't need to connect to other tables for populating
the select statement will be

sqlSelect = <<%sql%
SELECT * 
FROM sen_ipm_header sen_ipm_header
       INNER JOIN student_table_name student_table_name
                   ON sen_ipm_header.student_id = student_table_name.id
WHERE id = :newID
%sql%
args.add("newID", id)

this will give you all the student information. looks like you don't need other information from your last post.

[Turnbullca]

Re: UX Populate Data from other Tables

GHANDI

This the error that I am getting and I need all of the Tables.

Chris

Error executing onDialogExecute event: Xbasic Event: onDialogInitialize Syntax error in Xbasic: Could not compile code: Missing or invalid keyword function populate as c (e as p) debug(1) dim id as n id = e.dataSubmitted.ipm_id dim cn as sql::Connection dim args as sql::Arguments dim result as l dim rs as sql::ResultSet result = cn.Open("::Name::conn") if result dim sqlSelect as c sqlSelect = <<%sql% SELECT * FROM (sen_ipm_header sen_ipm_header INNER JOIN sen_ipm_ara sen_ipm_ara ON sen_ipm_header.ipm_id = sen_ipm_ara.ipm_id INNER JOIN sen_ipm_progress sen_ipm_progress ON sen_ipm_header.ipm_id = sen_ipm_progress.ipm_id INNER JOIN sen_ipm_provision sen_ipm_provision ON sen_ipm_header.ipm_id = sen_ipm_provision.ipm_id ) WHERE id = :newID %sql% args.add("newId", id) result = cn.Execute(sqlSelect,args) if result rs = cn.ResultSet e._set.student_id.value = rs.data("student_id") 'e._set.firstname.value = rs.data("firstname") 'e._set.address1.value = rs.data("address1") 'e._set.address2.value = rs.data("address2") 'e._set.city.value = rs.data("city") 'e._set.zipcode.value = rs.data("zipcode") 'e._set.date_of_birth.value = rs.data("date_of_birth") end if cn.close else populate = "alert('could not connect to database');" end if end function function onDialogInitialize as v (e as p) 'Request - The Request object. Includes Request.Variables, which should be used instead of the older e.rv construct 'Session - The Session object. Should be used instead of the older e.session construct 'Inside e: 'e.tmpl = Component definition 'e.rtc = Runtime calculations - you can use this to store data to be passed to other server side events (Note: the 'rtc' object cannot be used to persist state information - i.e. you can't set a value in rtc and then read that value in a subsequent callback. To persist state info see e._state). 'e.arguments = SQL::Arguments object with values for each of the arguments defined in this component. To read a value from arguments: e.arguments.find("argumentName").data 'You can set the initial value of any field by setting the property (see comment at end for Javascript technique to use when setting values for a large number of controls) 'e.control. 'For example: 'e.control.firstname = "Fred" 'e.control.lastname = "Jones" 'To set the value of a field in a repeating section, you must use "_A5INSTANCE" and the instance number as a suffix on the fieldname. 'For example: 'e.control.firstname_A5INSTANCE2 = "Bob" 'sets the value in the 2nd row of the repeating section. 'To specify any javascript that you want to execute set the e.javascript property. For example: 'e.javascript = "alert('hello');" 'You can set 'state' variables in this event. The value of any state variables will be available in all subsequent ajax callbacks. 'To set a state variable: 'e._state.myvar1 = "value1" 'e._state.myvar2 = "value2" 'To edit this action, place insertion point anywhere in the command, then click the 'Action Scripting' button'. 'ExecuteServerSideAction("Populate Dialog from Tables::Populate_a_Dialog_with_data_from_one_or_more_tables") 'Using Javascript to Set Control Values and the Row Count in a Repeating Section -- Recommended For Maximum Efficiency (Advanced users) '--------------------------------------------------------------------------------------------------------------------------- 'When setting the value of a large number of controls, you will improve performance if you generate the Javascript directly, 'rather than setting properties in the e.control object (as described above) and then having Alpha Five generate the Javascript from this object. 'The following example shows Javascript code that sets the number of rows in a Repeating Section and sets the value of various control 'in the top section and Repeating Section of the Dialog: 'Generate the Javascript to emit 'dim js as c 'js = <<%txt% 'var rows = 7; 'var setFocusToFirst = true; 'var nullOutExistingData = true; 'var repeatingSectionId = 'CONTAINER_1'; 'DLG1_DlgObj._setRepeatingSectionRowCount(repeatingSectionId,rows,setFocusToFirst,nullOutExistingData); 'var _d = {}; '//Next command is optional. It is ONLY needed if the ._setRepeatingSectionRowCount() method has been called. '//The command copies default values into the _d object. Without this command, default values defined in the component will not be set. '//$u.o.assign(_d,DLG1_DlgObj.originalValues[0]); '_d['B'] = 'alpha'; //set the value of a control called 'B' '_d['E:2'] = 'beta'; //set the value of a control called 'E' in the 2nd Repeating Section row '_d['G:6'] = ['Red','Green']; //set the value of a checkbox called 'G' in the 6th Repeating Section row to 'Red' and 'Green' 'DLG1_DlgObj.prepare(); 'DLG1_DlgObj.populate(_d); 'DLG1_DlgObj.refreshClientSideComputations(); '%txt% 'Now, emit the Javascript... 'e.javascript = js 'NOTE: In the above example, if the ._setRepeating() method is called in the 'onDialogInitialize' event, you are responsible for setting 'values in ALL of the controls in the component. For example, if you have defined default values for some controls, and then set the values 'of other controls using the .populate() method, only the values set in the _d object will be set. (The default values will NOT be set). 'You can, however, put the default values into the _d object using this code: '$u.o.assign(_d,DLG1_DlgObj.originalValues[0]); 'Tip: You can use the xbasic varToJSON() function to help generate the Javascript object passed to the .populate() method. 'For example: 'dim j as p 'j.firstname = "John" 'j.lastname = "Smith" 'j.quantity_a5instance1 = "2" 'dim j.colorchoices_a5instance2[3] as c 'j.colorchoices_a5instance2[1] = "Red" 'j.colorchoices_a5instance2[2] = "Blue" 'dim jsTxt as c 'jsTxt = varToJSON(j) 'js = "DLG1_DlgObj.populate(" + jsTxt + "); DLG1_DlgObj.prepare();" end function

[GGandhi]

Re: UX Populate Data from other Tables

did you add the arguments as I mentioned in post #29?

[GGandhi]

Re: UX Populate Data from other Tables

function populate as c (e as p)

debug(1)
dim id as n
id = e.dataSubmitted.ipm_id
dim cn as sql::Connection
dim args as sql::Arguments
dim result as l
dim rs as sql::ResultSet

result = cn.Open("::Name::conn")
if result
dim sqlSelect as c
sqlSelect = <<%sql%
SELECT *
FROM (sen_ipm_header sen_ipm_header
INNER JOIN sen_ipm_ara sen_ipm_ara
ON sen_ipm_header.ipm_id = sen_ipm_ara.ipm_id
INNER JOIN sen_ipm_progress sen_ipm_progress
ON sen_ipm_header.ipm_id = sen_ipm_progress.ipm_id
INNER JOIN sen_ipm_provision sen_ipm_provision
ON sen_ipm_header.ipm_id = sen_ipm_provision.ipm_id )
WHERE ipm_id = :newID
%sql%
args.add("newID", id)
result = cn.Execute(sqlSelect,args)
if result
rs = cn.ResultSet
e._set.student_id.value = rs.data("student_id")
'e._set.firstname.value = rs.data("firstname")
'e._set.address1.value = rs.data("address1")
'e._set.address2.value = rs.data("address2")
'e._set.city.value = rs.data("city")
'e._set.zipcode.value = rs.data("zipcode")
'e._set.date_of_birth.value = rs.data("date_of_birth")

end if

cn.close
else
populate = "alert('could not connect to database');"
end if

end function

[Turnbullca]

Re: UX Populate Data from other Tables

Ghandi

I did

This is the current code:-

function populate as c (e as p)

debug(1)
dim id as n
id = e.dataSubmitted.ipm_id
dim cn as sql::Connection
dim args as sql::Arguments
dim result as l
dim rs as sql::ResultSet

result = cn.Open("::Name::conn")
if result
dim sqlSelect as c
sqlSelect = <<%sql%
SELECT *
FROM (sen_ipm_header sen_ipm_header
INNER JOIN sen_ipm_ara sen_ipm_ara
ON sen_ipm_header.ipm_id = sen_ipm_ara.ipm_id
INNER JOIN sen_ipm_progress sen_ipm_progress
ON sen_ipm_header.ipm_id = sen_ipm_progress.ipm_id
INNER JOIN sen_ipm_provision sen_ipm_provision
ON sen_ipm_header.ipm_id = sen_ipm_provision.ipm_id )
WHERE id = :newID
%sql%
args.add("newId", id)

result = cn.Execute(sqlSelect,args)
if result
rs = cn.ResultSet
e._set.student_id.value = rs.data("student_id")
'e._set.firstname.value = rs.data("firstname")
'e._set.address1.value = rs.data("address1")
'e._set.address2.value = rs.data("address2")
'e._set.city.value = rs.data("city")
'e._set.zipcode.value = rs.data("zipcode")
'e._set.date_of_birth.value = rs.data("date_of_birth")

end if

cn.close
else
populate = "alert('could not connect to database');"
end if

end function

[GGandhi]

Re: UX Populate Data from other Tables

WHERE id = :newID

I was copying from my table
it should be
WHERE sen_ipm_header.ipm_id = :newID
sorry about that

[GGandhi]

Re: UX Populate Data from other Tables

Also I am testing in my local system, I will post back later any progress

[GGandhi]

Re: UX Populate Data from other Tables

okay
I made dummy tables in mssql 2012 and created a dialog and populated with a button from these three tables
take a look
http://screencast.com/t/xmhXSxvi

[Davidk]

Re: UX Populate Data from other Tables

I guess I didn't understand this one at all.

I thought you had a UX with a Parent table and several Child tables all with a One-to-Many relationship.

In a UX this would present itself as a Parent section with Repeating Sections for the children.

Then I thought the process was to select a current record from a dropdown and make a copy of a that record (Parent and Children) keeping in mind that the Parent and children keys were all auto-increment... that the Parent Key, used as a link to the Children, was auto-increment.

[GGandhi]

Re: UX Populate Data from other Tables

I tried to do different ways, I am not able to populate the repeating sections.
sorry.

[Davidk]

Re: UX Populate Data from other Tables

I don't think you need to go back to the tables to accomplish this. The UX, after populated, carries all the data for the parent and child tables... the parent and repeating sections.

Since all the data is available there's only one issue... the autonumber key for the parent... which is the linking key for the child. You can't create the child without first saving the parent.

The attached video and UX accomplish this by:

1. Saving all the data from the record, parent and child, to a session variable.
2. Using that data to create a Parent record and saving it.
3. Using the saved Parent data key (now available from step 2) to build and save the child.

You never have to go back to the tables for data and you never have to worry about adjusting your code because you added or removed fields on the UX.

http://www.youtube.com/watch?v=ELRRx60ZVSg

The attached UX is data bound so it won't work on another system but the code is the important bit and it's all there.

[Turnbullca]

Re: UX Populate Data from other Tables

Thanks both and sorry for the delay in getting back to you. I have had a few days vacation.

I have added the code and changed the bits I think need changing and while I think I understand the logic and some of the code. The problem I am having is that the Header record is saving but is blank and I cannot get it to load any data from the current record.

I have attached the component with the code in it and would appreciate it if you can give me some pointers.
Also I have multiple repeating sections - have I setup the autonumber code for tat correctly.

David the video is fantastic -

Chris

[Davidk]

Re: UX Populate Data from other Tables

Chris,

If you want to export your tables, with data, and post them or send them, then I can have a look.