Best way to 'feed' a child table with many options?

[WindForce]

Hi
I have a "Countries" table for different countries. I would like now to add Areas for those countries, so i have a Areas table (AreaID/AreaName), and an AreaCountries table (AreaCountryID, AreaID, CountryID)
What is the best / fastest way to have the user build the Areas data? I would like for instance to give the possibility to select all countries, then just deselect one or two then save this in a new area.
The reason i ask is that it seems quite tedious to use a repeating section where the user will have to add the countries in a specific area one by one...

Thanks for any ideas
Jaime

[forskare]

Re: Best way to 'feed' a child table with many options?

I'm not sure what you you mean by, "best / fastest way to have the user build the Areas data". Do you mean, Populate the area table or do you mean to show the area withing each country? If it's the first, unless you can download a list of "areas" within each country. you will have to enter the data one record at a time. If there are lists available, to download or at least on a document. you could copy the list (drag/copy) and the paste it onto a spreadsheet. Then, simply import the spreadsheet into Alpha. BTW, what is AreaCountryID for? If you are linking the two tables, the only ID field you need is the CountryID field. The area table is OK using the record number unless you want to link other tables (child to the Area table).

Hope this is helpful.

[J.R. Noe]

Re: Best way to 'feed' a child table with many options?

Like Ken says, you need the tables with the countries and country areas built and populated first, but once you have that, the hard work is done. If I understand you correctly, you want a user to select a country, like from a dropdown control, and have a repeating section fill with the areas for the country they selected. They can then remove or deselect the areas they do not want to save. Once they are done, they can Save the repeating section records to another table.

I use this concept to allow users to send emails to groups. If they select "supervisors" then the repeating section fills with all personnel in the supervisor group, if they select "Fleet Management" then only those personnel show in the repeating section, and so on. There is also an <All Personnel> which goes to everyone in that agency.
The same would work for you. Since it is a repeating section, they can edit it before they save it, such as deselect a logical field on areas they do not want to select, or remove the selected area from the repeating section. But for this to work, we needed the emailgroup table, which is a child table of our web_users table. Just like you need the countries and country areas tables to read values from.

Here is the xbasic code I use. There may be a better way to write this, but it is what I found that worked when I was needing it.
The value "5627" means <All Personnel> and the value "1177" means Current Personnel (since former employees remain in the users table)
This is a multi agency application so the :agid (coid) is a variable used to identify the agency of the user logged in.
The "PersGroup" control is a dropdown control with the Email groups.
I have an ajax callback on that controls OnChange event which calls "fillWithSups" so it automatically fills the repeating section when the value changes.


function fillWithSups as c (e as p)
dim vSQL as c
dim oldagid as c
dim varselectclass as n
select
case e.dataSubmitted.persgroup = "5627"
vSQL = "SELECT * FROM web_users WHERE companyid = :agid AND currentstatus = 1177 ORDER BY namefull ASC"
case e.dataSubmitted.persgroup <> "5627"
vSQL = "SELECT * FROM v_pers_emailgroupaddresses WHERE companyid = :agid AND emailgroup = :vpersclass ORDER BY namefull ASC"
end select

oldagid = e.dataSubmitted.agencyid
varselectclass = e.dataSubmitted.persgroup
masteragid = oldagid
masterselectclass = varselectclass
dim vjscmd as c
dim j as p
dim cn as SQL::Connection
if cn.open("::Name::conn") then
dim rs as SQL::ResultSet
rs = cn.ResultSet
dim sqlCommand as c = vSQL
dim args as SQL::Arguments
args.add("agid",masteragid)
args.add("vpersclass",masterselectclass)
delete rs
dim cnt as n = 0
if cn.Execute(sqlCommand,args) then
dim rs as SQL::ResultSet
rs = cn.ResultSet
while rs.NextRow()
cnt = cnt + 1
eval("j.PERSNAME_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("persid")
eval("j.PERSNAME_TEXT_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("namefull")
eval("j.AGENCYID_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("companyid")
eval("j.PERSEMAIL_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("userid")
end while
delete rs
vjscmd = "{dialog.object}._setRepeatingSectionRowCount('CONTAINER_1'," + cnt + ",true,true);"
vjscmd = vjscmd + "{dialog.object}.populate(" + varToJSON(j) + ",1,false,true);"
else
vjscmd = "alert('No personnel found with this classification.);"
end if
cn.Close()
else
vjscmd = "alert('Connection Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
end if
fillWithSups = vjscmd
end function

I hope I understood you correctly and this helps. There may be, and probably is, a better way to do it, but this is what we found works for us. Good luck.
-JR

[WindForce]

Re: Best way to 'feed' a child table with many options?

JR
A million thanks for taking the time!! The code will prove useful for sure.
This time i opted for a different tactic by using lists, will prepare a nice post with explanations and update here