close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

concat firstname and lastname in child table

Thread began 9/26/2009 8:18 pm by jenkolaka19379118 | Last modified 1/27/2010 4:26 pm by Lee Firth | 5492 views | 14 replies |

jenkolaka19379118

Dynamic Dropdowns, Concat and Universal Email

I am trying to create a list which will help me select multiple users to email. I have created the dynamic dropdowns with no problem.

parent table = person category
child table = people

The only question I have is you are only allowed to select one "child text field" in the Create Dynamic array Server Behavior. For my example I would need to select 2 child text fields "firstname" and "lastname". Is there a way to manually concat "firstname" and "lastname" manually in the php generated code. If so would you be able to tell which line of code I should change?


Secondly the objective here is to create an email list (using the target list) and incorporate universal email. The recordset created by dynamic dropdowns SELECT * FROM my people table, contains all the neccessary fields I need for my email form i.e. "firstname" "lastname" "email". I would like to describe my idea to make it easier for you to help me.

I want to spread this process on 2 pages.

1. The first page will contain my WA dynamic dropdown list and the user will select the various people and move them to the "target list" for emailing then click submit.

2. The submit button will take them to the second page, a form which will have universal email applied to it. UE will send/echo the contents of the form to everybody in the "target list". The problem is I am not sure how to get the email addresses from the "target list", or perform a universal email recordset loop? From what I have read in this forum, on submit of the dynamic dropdown the child ID is passed, therefore dataassist search server behaviour is needed to get the other required recordset values.

In Summary what I need to know is;

1. How do I submit form1 (dynamic dropdown "target list" people) to another page, form2 the email form that will contain a textarea for email content? i.e. target list (page 1) to email form (page 2)

2. Which page do I apply data assist search server behaviour and how do I configure it to my "target list" to get the required email addresses, firstname and lastname? What do I set the trigger to? Which table do I pull from, parent or child? If I select the child table which is my people table in the add+ window to I leave the separator as AND? In the column: section to I select the ID field, email field, or ctrl all the fields that I would require to send out the email? Finally in the value: binding do I set the value to the target menu/list in the form?

3. Which page do I apply Universal Email? What do I set the trigger to? How do I configure it to send a email to everyone on the "target list"? What value do I select in the TO TAB when I click the lightening bolt to select a value? Would it be the datassist search session value?



Your Help in Greatly Appreciated

Sign in to reply to this post

jenkolaka19379118

comment closed with no response?

It appears my comment has been closed but I have not received a response yet?

Sign in to reply to this post

Eric Mittman

You are on the rite track to getting this implemented. The first thing you mentioned about concatenating the first and last names can be done with the recordset query itself. You can concatenate two columns in the sql like this:

SELECT CONCAT(firstnamecolumn, lastnamecolumn)
FROM your_table

To get the pages working together you should think of your page where you are selecting the users from as your search page. It will post the results of the form to your second page which you should think of as your results page.

You will have both UE and DataAssist Search applied to this second page. When you configure the DataAssist Search you should specify the trigger as the submit button from your first page. For the search criteria you will select a list type search then select your target list from the first page with the users that have been added. You will be searching the id column from this users table.

That should be about it for the search. This should give you a recordset on the second page that holds the details for the users that were selected in the list on the first page.

Lastly you will just add in the Universal Email server behavior to this page to send the form details to these users. The trigger for the UE should be same page post. The recordset should hold all of the same values for you and each listed person will get an email. Post back if you have any further questions or need more advice on this.

Sign in to reply to this post

jenkolaka19379118

Concat is not working - problem with dynamic array server behaviour

I have edited my "child" recordset to read.

SELECT CONCAT(firstname, ' ' ,lastname) AS fullname
FROM people

when i test the statement it works. When I check the recordset in the bindings panel it is there.

However when I access the create dynamic array server behaviour and look through the menu options for the "child text field" my recordset changes do not seem to have applied. I have tried deleting and redoing but the dynamic array server behaviour does not seem to aknowledge my "child" recordset. It appears the server behaviour is getting its values directly from the database and not the recordset query?

I have spent hours looking at the help documentation for this and it say's the "child" recordset sql statement should be as:

  The Recordset SQL statement for the dynamic array would be:
SELECT ChildID, ChildText, ParentID FROM Parent ORDER BY ParentText  



however this statement cannot be correct as the parent table described is a reference table, so does not contain the childID or the childText.

My revised sql statement for my child table is

SELECT person_id, person_cat_id, CONCAT(firstname, ' ' ,lastname, ' ; ' ,email) AS fullname
FROM people
ORDER BY fullname

The mysql test is successful and it creates the recordset, but for some reason the dynamic array server behaviour does not pick up the recordset, instead it just picks up all the fields in my child table (people) minus the new created recordset value "fullname". Therefore I am not able to set the child text field to anything other than the individual field names.

Sign in to reply to this post

Eric Mittman

Based on what you are reporting it seems that the query may be too complex for the server behavior to pick it up. There are a couple of things you can do to see if you can get around this. One thing is to try the query minus the alias name you are applying to your concatenated field list. I'm not sure if the query will work the same but it is worth trying to see if you get the desired result.

The other thing to try would be to create the recordset in a simple fashion then update it after you have created the 'dynamic array' server behavior. Doing it this way you may be able to force it to work with the query if it is altered after the fact. If you end up going this route you may need to update the server behavior where it matches the column name and have it work with your alias if needed.

Sign in to reply to this post

barbara224891

similar problem with concatenating names for Dynamic Dropdown menues

I was studying this string because I have a similar problem. I tried Eric's suggestion of redefining the recordset with the more complex query (with the concatenation) after the array definition server behavior. I found that this does permit the passing of the concatenated names, however, the array is no longer selected by the other variable. In other words, it lists all possible names, not those names that correspond to the selection of the parent field.

Here's the relevant php before the html page:
mysql_select_db($database_db, $db);
$query_contacts = sprintf("SELECT * FROM contacts ORDER BY contactID ASC");
$contacts = mysql_query($query_contacts, $db) or die(mysql_error());
$row_contacts = mysql_fetch_assoc($contacts);
$totalRows_contacts = mysql_num_rows($contacts);
?>
<?php
if ($row_contacts) {
echo "<SC" . "RIPT>\n";
echo "var WAJA = new Array();\n";

$oldmainid = 0;
$newmainid = $row_contacts["orgID"];
if ($oldmainid == $newmainid) {
$oldmainid = "";
}
$n = 0;
while ($row_contacts) {
if ($oldmainid != $newmainid) {
echo "WAJA[".$n."] = new Array();\n";
echo "WAJA[".$n."][0] = '".WA_DD_Replace($newmainid)."';\n";
$m = 1;
}

echo "WAJA[".$n."][".$m."] = new Array();\n";
echo "WAJA[".$n."][".$m."][0] = "."'".WA_DD_Replace($row_contacts["contactID"])."'".";\n";
echo "WAJA[".$n."][".$m."][1] = "."'".WA_DD_Replace($row_contacts["fullname"])."'".";\n";

$m++;
if ($oldmainid == 0) {
$oldmainid = $newmainid;
}
$oldmainid = $newmainid;
$row_contacts = mysql_fetch_assoc($contacts);
if ($row_contacts) {
$newmainid = $row_contacts["orgID"];
}
if ($oldmainid != $newmainid) {
$n++;
}
}

echo "var contacts_WAJA = WAJA;\n";
echo "WAJA = null;\n";
echo "</SC" . "RIPT>\n";
}
function WA_DD_Replace($startStr) {
$startStr = str_replace("'", "|WA|", $startStr);
$startStr = str_replace("\\", "\\\\", $startStr);
$startStr = preg_replace("/[\r\n]{1,}/", " ", $startStr);
return $startStr;
}
mysql_select_db($database_db, $db);
$query_contacts = "SELECT contactID, CONCAT(firstname, ' ',middlename,' ' ,lastname) AS fullname, orgID FROM contacts";
$contacts = mysql_query($query_contacts, $db) or die(mysql_error());
$row_contacts = mysql_fetch_assoc($contacts);
$totalRows_contacts = mysql_num_rows($contacts);
?>

here's the javascript code in the head of the html document:

function WA_ClientSideReplace(theval,findvar,repvar) {
var retval = "";
while (theval.indexOf(findvar) >= 0) {
retval += theval.substring(0,theval.indexOf(findvar));
retval += repvar;
theval = theval.substring(theval.indexOf(findvar) + String(findvar).length);
}
retval += theval;
if (retval == "" && theval.indexOf(findvar) < 0) {
retval = theval;
}
return retval;
}

function WA_UnloadList(thelist,leavevals,bottomnum) {
while (thelist.options.length > leavevals+bottomnum) {
if (thelist.options[leavevals]) {
thelist.options[leavevals] = null;
}
}
return leavevals;
}
function WA_FilterAndPopulateSubList(thearray,sourceselect,targetselect,leaveval,bottomleave,usesource,delimiter) {
if (bottomleave > 0) {
leaveArray = new Array(bottomleave);
if (targetselect.options.length >= bottomleave) {
for (var m=0; m<bottomleave; m++) {
leavetext = targetselect.options[(targetselect.options.length - bottomleave + m)].text;
leavevalue = targetselect.options[(targetselect.options.length - bottomleave + m)].value;
leaveArray[m] = new Array(leavevalue,leavetext);
}
}
else {
for (var m=0; m<bottomleave; m++) {
leavetext = "";
leavevalue = "";
leaveArray[m] = new Array(leavevalue,leavetext);
}
}
}
startid = WA_UnloadList(targetselect,leaveval,0);
mainids = new Array();
if (usesource) maintext = new Array();
for (var j=0; j<sourceselect.options.length; j++) {
if (sourceselect.options[j].selected) {
mainids[mainids.length] = sourceselect.options[j].value;
if (usesource) maintext[maintext.length] = sourceselect.options[j].text + delimiter;
}
}
for (var i=0; i<thearray.length; i++) {
goodid = false;
for (var h=0; h<mainids.length; h++) {
if (thearray[i][0] == mainids[h]) {
goodid = true;
break;
}
}
if (goodid) {
theBox = targetselect;
theLength = parseInt(theBox.options.length);
theServices = thearray[i].length + startid;
var l=1;
for (var k=startid; k<theServices; k++) {
if (l == thearray[i].length) break;
theBox.options[k] = new Option();
theBox.options[k].value = thearray[i][l][0];
if (usesource) theBox.options[k].text = maintext[h] + WA_ClientSideReplace(thearray[i][l][1],"|WA|","'");
else theBox.options[k].text = WA_ClientSideReplace(thearray[i][l][1],"|WA|","'");
l++;
}
startid = k;
}
}
if (bottomleave > 0) {
for (var n=0; n<leaveArray.length; n++) {
targetselect.options[startid+n] = new Option();
targetselect.options[startid+n].value = leaveArray[n][0];
targetselect.options[startid+n].text = leaveArray[n][1];
}
}
for (var l=0; l < targetselect.options.length; l++) {
targetselect.options[l].selected = false;
}
if (targetselect.options.length > 0) {
targetselect.options[0].selected = true;
}
}

and finally, here's the child list in the body of the document

<select name="contact" onchange="WA_FilterAndPopulateSubList(contacts_WAJA,MM_findObj('orgID'),MM_findObj('contact'),1,0,false,': ')">
<option value="" <?php if (!(strcmp("", $row_project['contact']))) {echo "selected=\"selected\"";} ?>>-- select --</option>
<?php
do {
?>
<option value="<?php echo $row_contacts['contactID']?>"<?php if (!(strcmp($row_contacts['contactID'], $row_project['contact']))) {echo "selected=\"selected\"";} ?>><?php echo $row_contacts['fullname'] ;?></option>
<?php
} while ($row_contacts = mysql_fetch_assoc($contacts));

?>
</select>


In case it's helpful, I'm also attaching the whole page. Oops, it won't let me attach a php file, scratch that.

Thanks for any help you can give me.

Sign in to reply to this post

Eric Mittman

As a test for this try doing the same thing by updating the query after the fact to include the extra info. However before you do this part make a copy of the page. Once you have modified the query on the page test out both of them to see how they are working. If you do not get the desired result on the page with the updated query check to ensure that you are not referencing columns that did not exists in the simpler query that works.

So long as the name of the rs is not changed and the column names are still present then the updated query should work like the original one but with more info being returned.

Sign in to reply to this post

jenkolaka19379118

That is Working Now!!!

Hi Eric,

I got that working now. This is the steps I took.

1. created my parent recordset normally e.g SELECT * FROM person_category.

2. created my child recordset, however just the fields I required and applied the CONCAT() function. e.g SELECT people.peopleID, CONCAT (people.firstname, ' ' ,people.lastname) AS fullname, people.email, people.person_category_ID FROM people.

3. Tested both quiries successfully in the recordset dialog box.

4. applied the dynamic dropdown server behaviours. In the "select child text", my recordet set was still not appearing so I just selected any field i.e. firstname, then clicked OK.

5. went into code view in dreamweaver and changed the array from "firstname" to my CONCAT name "fullname".

6. went to tag inspector> behaviors> WA dynamic Dropdowns> populate List from array.

7. tested and it works!!!

Many thanks for your help.

Sign in to reply to this post

jenkolaka19379118

Sorry to be a pest,

I am still trying to implement the idea I first decribed at the beiginning of this thread. You suggested that I apply da search server behaviour and universal email to the second page. I have created page 1 which is holds the dynamic dropdown and I have set the form action to;

action"page2.php"

I have created a new page called page2. when i click the da seach server behaviour from the behaviour panel it will not let me create a search because it say's there are no recordsets on the page. This is true because the recordsets are on page1. page2 is completely blank interms of recordsets and server behaiours. Do I need to do extra to page 1 to get it to submit the recordset to page2.

I am really confused about how all this works.

Any help will be much appreciated

Sign in to reply to this post

Eric Mittman

Since the form is on the fist page and it is the 'search' page then second one is where you will need to have the recordset since the results that are returned will just be a filtered subset of the recordset. So on this second page you will add in your recordset that selects all of the records from the table you are searching. Then you apply the DataAssist Search server behavior to this page to get the search working properly.

If you go back to my first reply I give a little more detail about the flow and where the recordsets and server behaviors belong.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...