update and read values from browse subform, from checkboxes

[rfeltham]

I have been asked to transfer a Filemaker Pro DB to something opensource. (Os updates have broken FileMaker)

It involves patients and scheduling; most everything is working, except:

They would like to be able to search for patients according to different search criteria, and send them an email.
Some examples would be:
- all friday patients
- all patients in a specific time slot
- all patients from a specific funding source
- all patients in a specific program

Many of the fields I need to search are checkbox fields (multi select would not work, as they are prone to forgetting to hold a CTRL key down when ,multi selecting, causing unnoticed data loss)

I have created a test form, where the idea is to have them enter data for text fields they want to match, check checkboxes for time slots, or for whole days, and build a SQL query by clicking on a button.
I would put the SQL query into a text box to verify it for now, but in the long run, it should update a browse iframe or subform with the results of the query.

If they are happy with that, they can then click on a button to send the email, using text from a text form as the body of the message, and go through the results of the query (possibly swapping in names for #name?) grab the email, and send the message to each person in turn.

Issues that I have run into are:
- I cannot get the value of a query checkbox. $(‘#fri’).val() does not work.
- I cannot update the sqlquery text box. No value shows up.
- I do not get how to update the browse subform or iframe.
- I can’t figure out how to get values out of the query results, row by row

I feel close, but I am a sysadmin, not a programmer. PHP and Javascript are unfamiliar to me.

From reading there forums, I think the browse form iframe or subform needs to be rebuilt with a
$s = "CREATE TABLE #TABLE_ID# SELECT * FROM invoice";
nuRunQuery($s);

I am not sure where to put that (Before Broswe? Before Edit?)
Or how to access the data (Does there browse iframe need to refer to #TABLE_ID# instead of the original table?)

I have attached screen shots which are hopefully complete enough that the answer is obvious. To someone. ( Not me. Sysadmin, not programmer :-/ )

Thx,

Richard F.

[rfeltham]

From the thread "Start new form with prefilled data taken from other form" I got the sqlquery update to work.

$('#sqlquery').empty(); // not convinced this did anything
$('#sqlquery').val(cond); // this did not work without appending .change(); - see below

$('#sqlquery').append(cond); did nothing, even when appending .change()

nuSetProperty('sqlquery',cond); // did nothing
$('#sqlquery').change(); // even when followed with this

This works:

$('#sqlquery').empty();
var newquery = 'SELECT * FROM person' + cond;
$('#sqlquery').val(newquery).change();

Still need to find a way to allay the SQL to the browse subform./iframe, and find a way to read data from that table. And read values of checkboxes.

Thx,

R

[kev1n]

Hi,

You should be able to do this without using much javascript.

In the Browse SQL you can use Hash Cookies (e.g. #qfri# will evaluate to 0 or 1)

Code: Select all

SELECT * FROM person WHERE 
name LIKE '%#qname#%'
AND qfri = #qfri#

...add the other conditions in the same way

Then to refresh the Browse, call

Code: Select all

$("#personruniframe")[0].contentWindow.nuGetBreadcrumb(0);

( BTW, this is how you would get the value of a checkbox: $('#qfri').prop('checked'); )

[rfeltham]

I tried kev1n's code, and it works perfectly.
I did need to figure out the logic of the SELECT (see below) so that it worked as needed.

At this point, I have the browse form filling in with just the selected records.
I have added a button, next ot the /Search/Add/Print buttons in the iFrame, with:

Code: Select all

    nuAddActionButton('RunEmail','Send Email','runEmailScript()');

in the iFrame's CustomCode/Javascript section.

I suspect I need to change runEmailScript() to nuRunPHPHidden('emailPHPProcedure'), and in the emailPHPProcedure, I need a script like:

Code: Select all

while ($r = db_fetch_object($object)) { /* MyEmailFunctionHere($subject,$body,$r->email_1); */}

The question is, what should $object be? '#TABLE_ID#' ? '#browse_table_id'? something related to the personruniframe?


Thx,

Richard F.

----------------------------------------------
For the select, I wanted each group of things to narrow the results, but elements within a group to expand the results. Anything not selected should not narrow the results.
So a name field left blank has no effect, but filled in, narrows the results.
A set of checkboxes would narrow the results, but selecting multiple checkboxes in the group should expand the list of results.
So a group of (mon, tues, wed, thurs, fri) checkboxes would narrow the results. Checking more than one gives results for each day selected. It does not select only those persons who have appointments on more than one day.

Code: Select all

SELECT * from persons
WHERE (('#q_child#' IS NOT NULL) AND (child_name LIKE '%#q_child#%'))
AND  (('#q_parent#' IS NOT NULL) AND (parent_name LIKE '%#q_parent#%'))
AND  ( ('#q_condition#' IS NOT NULL)
         AND (    (condition_1 LIKE '%#q_condition#%')
               OR (condition_2 LIKE '%#q_condition#%') ) )
/* This selected for  people who had appointments on multiple days
AND  ( wed = 1 OR ( '#q_wed#' = 0  AND wed = 0 ) )
AND  ( thurs = 1 OR ( '#q_thurs#' = 0  AND thurs = 0 ) )
AND  ( fri = 1 OR ( '#q_fri#' = 0  AND fri = 0 ) ) 
*/
AND (  ('#q_wed#' = 0 AND '#q_thurs#' = 0 AND '#q_fri#' = 0)
     OR  ('#q_wed#' = 1 AND wed = 1)
     OR ('#q_thurs#' = 1 AND thurs = 1)
     OR ('#q_fri#' = 1 AND fri = 1)
     )
AND ( ('#q_clinic#' = 0 AND '#q_grtherapy#' = 0 AND '#q_otherclinic#' = 0 )
     OR ('#q_clinic#' = 1 AND pr_clinic = 1)
     OR ('#q_grtherapy#' = 1 AND pr_grtherapy = 1)
     OR ('#q_otherclinic#' = 1 AND pr_otherclinic = 1)
     )

[kev1n]

I think it might work like this. The SQL must be passed to a PHP function. With nuSetProperty() a hash cookie can be set.

Code: Select all

Add a button:
nuAddActionButton('RunEmail','Send Email','runEmailScript()');

// run function when clicked the "Send EMail Button"
function runEmailScript() {

   var sql =$("#personruniframe")[0].contentWindow.nuCurrentProperties().browse_sql;
   nuSetProperty('personruniframeSQL', sql);

   // Run a procedure
   nuRunPHPHidden("runEmailScript", 1);
}

Then create a procedure that looks like this:

Code: Select all

// Retrieve the SQL from the hash cookie:
$s	= "#personruniframeSQL#";
$t	= nuRunQuery($s);

while($r = db_fetch_object($t)){
	nuSendEmail(..,$r->email_1,.....);    
	// See here for details: https://wiki.nubuilder.net/nubuilderv3/index.php/PHP_Functions#nuSendEmail.28.24to.2C_.24from.2C_.24fromname.2C_.24content.2C_.24subject.2C_.24filelist.2C_.24html_.3D_false.29
}

[rfeltham]

I think I understand the overall logic. Hopefully.

With this line,

Code: Select all

   var sql =$("#personruniframe")[0].contentWindow.nuCurrentProperties().browse_sql;

(If I understand the logic right) we are supposed to get the SQL code with this, and pass it to the PHP script as a Hash Cookie, which then runs the query again.
I assume that all Hash Cookies are globally defined, so that the values from the form (q_thurs or q_fri) are available for the rerun of the SQL query. (as "#q_thurs#" or "#q_fri#")


Unfortunately, I get this error on console:

Code: Select all

TypeError: undefined is not an object (evaluating '$("#personruniframe")[0].contentWindow')

Did I miss something?

Richard F.

[kev1n]

Strange, since this works all fine for you

Code: Select all

$("#personruniframe")[0].contentWindow.nuGetBreadcrumb(0);

$("#personruniframe")[0].contentWindow shouldn't result in an error .

Do you see the same error when running $("#personruniframe")[0].contentWindow in the developer console (F12)?

[rfeltham]

>Strange, since this works all fine for you
>
>

Code: Select all

$("#personruniframe")[0].contentWindow.nuGetBreadcrumb(0);

It does work, so this is baffling.

>$("#personruniframe")[0].contentWindow shouldn't result in an error .
>
>Do you see the same error when running $("#personruniframe")[0].contentWindow in the developer console (F12)?

I tried that, and it does result in an error.
$("#personruniframe")[0] is apparently undefined.

My only guess at this point is that that exists in the context of the outer form, but not in the context of the iFrame.
I'm not familiar enough with the console to be able to guess what context it thinks it is in, or to make it change it's context.

If I put a button in the outer form with the Javascript code, and click on it, it does not generate the error.
On the other hand, I did not see the debug output I expected if it successfully ran, either.
I will have to check the PHP code it should be running, and see if I remembered to put in the nuDebug(nuHash()) code I planned to.

----
And having checked my code, it appears that when the iFrameSQL code is passed as a Hash cookie, it escapes the single quotes. I changed them to double quotes, (and changed the numeric compares for the checkboxes to quotes: WHERE ... AND ("#hashvar#" = "0" OR ("#hashvar#" = "1" AND tableware = 1)) )

I also had issues passing form values to the PHP procedure, until I passed them as Hash Cookies (nuCurrentProperties())

My only remaining issue is sending the email.
I tried sending an email to myself and the email server does not see an inbound connection for that address.

Are there logs on the nuBuilder localhost that relate to emails?

Richard F.

[kev1n]

I tried that, and it does result in an error.
$("#personruniframe")[0] is apparently undefined.

My only guess at this point is that that exists in the context of the outer form, but not in the context of the iFrame.
I'm not familiar enough with the console to be able to guess what context it thinks it is in, or to make it change it's context.

If I put a button in the outer form with the Javascript code, and click on it, it does not generate the error.
On the other hand, I did not see the debug output I expected if it successfully ran, either.
I will have to check the PHP code it should be running, and see if I remembered to put in the nuDebug(nuHash()) code I planned to.

If that function is called in the context of the iframe, nuCurrentProperties().browse_sql will do it.

And having checked my code, it appears that when the iFrameSQL code is passed as a Hash cookie, it escapes the single quotes. I changed them to double quotes, (and changed the numeric compares for the checkboxes to quotes: WHERE ... AND ("#hashvar#" = "0" OR ("#hashvar#" = "1" AND tableware = 1)) )

I also had issues passing form values to the PHP procedure, until I passed them as Hash Cookies (nuCurrentProperties())

You could try encoding the sql string in JavaScript using btoa() and in PHP base64_decode() to decode the it.
https://developer.mozilla.org/en-US/doc ... de_Strings

My only remaining issue is sending the email.
I tried sending an email to myself and the email server does not see an inbound connection for that address.

Are there logs on the nuBuilder localhost that relate to emails?

Richard F.

I'm assuming you've done the e-mail settings. (Setup -> Email Settings). To evaluate the return value of the email function, assign a variable to it. Then check the debug log.

Code: Select all

$result = nuSendEmail(.....);
nuDebug($result);

[rfeltham]

The result code I see id:

[0] : Array
(
[0] =>
[1] => Message sent successfully
[2] => SMTP connect() failed. https://github.com/PHPMailer/PHPMailer/ ... leshooting
[3] =>
)

So I suspect it is an issue with the fact that the account is a gmail G Code account, which should make this relevant:

Gmail, OAuth2 and "Allow less secure apps"
From December 2014, Google started imposing an authentication mechanism called XOAUTH2 based on OAuth2 for access to their apps, including Gmail. This change can break both SMTP and IMAP access to Gmail, and you may receive authentication failures (often "5.7.14 Please log in via your web browser and then try again") from many email clients, including PHPMailer, Apple Mail, Outlook, Thunderbird and others. The error output may include a link to https://support.google.com/mail/bin/ans ... swer=78754, which gives a list of possible remedies. There are two main solutions to this in PHPMailer:

Gmail doesn't like unexpected or unfamiliar clients connecting to gmail accounts, so it may require you to log into your gmail account in your browser as usual (this will be mentioned in error output visible if you set SMTPDebug = 2), or to visit the unlock CAPTCHA page mentioned in their support doc.
Enabling "Allow less secure apps" will usually solve the problem for PHPMailer, and it does not make your app significantly less secure. Reportedly, changing this setting may take an hour or more to take effect, so don't expect an immediate fix.
PHPMailer added support for XOAUTH2 in version 5.2.11, though you must be running PHP 5.5 or later in order to use it. Documentation on how to set it up can be found on this wiki page.
Using encryption

Not being the email admin, I can't make the "Allow less secure apps" change.
Is there a way to go the "PHPMailer ... (with) XOAUTH2" route?

Richard F.