Text auto complete works pretty good except for the fact that it won’t accept NEW entries.
It pulls the possible options just fine, but if I don’t want one of those options and enter a new value, it won’t save it in the db.
Any ideas?
Dave
Re: New entry in text auto complete
You are responsible for updating your database. You can use a JS/AJAX call to update table using SQL: INSERT IGNORE or something to perform this.
Regards,
Scott.
Re: New entry in text auto complete
Hi!
If you solved this, could you give me some hint how to do it?
Thanks!
Re: New entry in text auto complete
Have the same question…auto-complete is nice and works, but you can’t save a new text to database. Any solution on this? On what event should I place what code? Why isn’t there a setting within the auto-complete to allow also new entries if needed?
Also…when I have a data set and delete the data in auto-complete field, there is nothing in database. Now…when I want to re-edit the auto-complete field, the auto-complete seems to be broke for all but the last data set in editable grid view…I get no auto results below the fields anymore. with a new data set or the last data set it works.
Re: New entry in text auto complete
Auto complete fields are just designed to load existing data. If you want to update the table with new entries, then you will need to do this when you save.
onAfterUpdate:
// update field with new entry
$sql = 'INSERT IGNORE INTO myTable SET';
$sql .= ' myfield = {form_field}';
sc_exec_sql($sql);
This will add the new entry to the table and will be available next time.
Regards,
Scott.
Re: New entry in text auto complete
Hi ScottMartin,
thanx a lot…I tried to figure this out…due to the fact I am new to PHP and not that advanced with SQL it took me some time. Your solutions seems to solve what I was looking for…cheers from Germany 
Re: New entry in text auto complete
You can use my syntax, or you can use your syntax, but you need to use VALUES in your version
INSERT IGNORE INTO table (field) VALUES (value)
Test your syntax in a DB manager first (ie: phpmyadmin), then update your code.
Regards,
Scott.
Re: New entry in text auto complete
…
Re: New entry in text auto complete
Not sure I understand what you are doing here …
Inserting a new record into a table and then trying to update that same record with the form data?
I was under the impression that you where using a lookup table for your auto-complete field?
The code I provided was to execute SQL, after the save of the main form, that then updates the lookup table with the new value if it does not exist.
Regards,
Scott.
Re: New entry in text auto complete
Hello Scott,
can’t get the correct data in database. He ignores the current {service_title_DE} data and keeps writing the same data that is already stored in database. When I write service_title_DE = “1”, then updates database with this value.
Re: New entry in text auto complete
I would turn on debug for that form and see what the SQL is generating. I suspect it is using the original form data and not the new changed auto-complete data.
Try storing the value to a variable and use that instead of the form field value.
Regards,
Scott.
Re: New entry in text auto complete
// ****************************************
// update auto-complete field with new entry
// code onAfterUpdate
$db_event_data_service_id = {db_event_data_service_id}; // current id
$service_title_DE = [var_service_title_DE]; // current field input
$sql = 'INSERT IGNORE INTO db_event_data_service VALUES (service_title_DE)'; // ignore auto-complete insert
$sql = 'UPDATE db_event_data_service SET service_title_DE = "' . $service_title_DE . '" WHERE db_event_data_service_id =' . $db_event_data_service_id; // update with current field input
sc_exec_sql($sql);
// next line to catch current user input
// [var_service_title_DE] = {service_title_DE}; // set current input global
// application->global variable make sure to set variable var_service_title_DE OUT only
// END
I cannot catch the current user input! Where should I put this code to pass the current data which user just entered in field? I tried everything, but auto-complete seems to override the current input and sets field data back to whatever is already in database.
Would be fantastic to get a solution since there is no option for entering new data with auto-complete function. This could be sample code for SC also.
[var_service_title_DE] = {service_title_DE}; // set current input global
SOLVED: Autocomplete requires extra javascript to accept new entries
You have two options to solve this:
The easiest is to use the “Capture” option in the fields settings for your autocomplete field. This feature will place a search icon next to the field that the user will need to click in order to initiate the search. However, if the user just types in the field without invoking the search, this value will be saved in the database even if it is a new value.
My users will never click the search icon, so I decided to use the more complicated option that requires javascript.
For autocomplete fields, Scriptcase actually uses two fields internally - one field for the user to type in and another to store the value that goes into the database. Scriptcase does not push the value of the field that is typed in into the field that stores in the database unless an autocomplete option is selected. We can push that value over by adding extra javascript.
You will need to define two javascript events in Scriptcase’s javascript section, one to execute on form load and one to execute on submit.
Here are the two generic functions (you will need to substitute your field names for this to work – see below):
ADD THE FOLLOWING CODE INTO THE SCRIPTCASE FORM LOAD EVENT:
//the autocomplete does not by default allow new values, therefore we need to capture the value as it is typed and re-assign it before submitting the form. There are two fields at play. The field where the input occurs and the field that gets saved to the database
save_actual_value = ""; //initialize holding variable
//on form load, the autocomplete will blank the input field, if we are returning to the form from a validation failure, we will now repopulate the input filed with the current value
document.getElementById("id_ac_XXXXX").value = document.getElementById("id_sc_field_XXXXX").value;
//only define the events if they are not already defined - this prevents javascript from locking up
if (typeof document.getElementById("id_ac_XXXXX").onkeyup !== "function")
{//define event to capture the input field on each keypress
document.getElementById("id_ac_XXXXX").onkeyup=function(){save_actual_value = document.getElementById("id_ac_XXXXX").value};
}
if (typeof document.getElementById("id_ac_XXXXX").onblur !== "function")
{//define onblur event to capture the input field when the value has changed by selecting from the autocomplete dropdown list
document.getElementById("id_ac_XXXXX").onblur=function(){save_actual_value = document.getElementById("id_ac_XXXXX").value};
}
ADD THE FOLLOWING CODE INTO THE SCRIPTCASE FORM SUBMIT EVENT:
if (save_actual_value != "")
{//only execute if meaningful to do so - this prevents the save button from becoming unresponsive
//assign the input that was captured from the input field to the field that will be saved to the database
document.getElementById("id_sc_field_XXXXX").value = save_actual_value;
}
To use the above code, you will first need to paste those code fragments into a text editor to do a search and replace based on the field id names.
In order to discover the field id names that sciptcase uses internally, run the form in a browser, right click inside the autocomplete field and select “inspect element”
The field id name will show as id=“ScriptcaseInternalFieldNameHere”
There are two possibilities for the internal field names:
[LIST=1]
- replace all id_sc_field_XXXXX with id_sc_field_sc_field_AddTheNumberHere
- replace all id_ac_XXXXX with id_ac_sc_field_AddTheNumberHere
Usually, you will get the second case if your field name contains a space character.
This solution works in Scriptcase 6 - I cannot vouch for other versions.
Also, please note that manipulating and referencing the Scriptcase fields directly strays outside the Scriptcase framework and offers no guarantee that the code will continue to work after upgrading to an new version of Scriptcase or migrating an application, etc. In other words, use at your own risk.
[QUOTE=robydago;37892]@Einstien
Thanks a lot for sharing!
It works with SC8.[/QUOTE]
How did you get this to work in SC8? With a direct copy & paste of this code (replacing the XXXXX with the field name) I get errors in PHP…
I get an unexpected “=” on line number #### where the save_actual_value variable is set. [ This line–> save_actual_value = “”; //initialize holding variable ]
When I add square brackets to that line ( [save_actual_value] = “”; //initialize holding variable) I start getting the same error from the other lines setting the onkeyup and onblur events.
I put the code in the Events section in onLoad. I put the second bit of code in the onValidate section, but I removed that as it was throwing the same errors and I was trying to just get the onLoad section to work without errors.
Am I missing something simple here?
Hi,
Does works in editable grid view form?
I don’t know what to put to replace the XXXXX since all the fields IDs are incremented.
Like id_sc_field_XXXXX_1, id_sc_field_XXXXX_2, etc.
Hi. I’m working with a text autocomplet box in SC9.
I was using this code on the event OnBeforeUpdate to add new street names into direcciones table from where i took information to do the lookup in {home_addy} for the street names
$calle=trim(ucfirst({home_addy}));
$ciudad={home_city_id};
$query=“SELECT description FROM direcciones WHERE ((description=’$calle’) and (city_id=$ciudad))”;
sc_lookup(ds,$query);
if ( empty({ds}) ){
$insert_sql = ‘INSERT INTO direcciones (id, description, city_id) VALUES (NULL,"’ . $calle . ‘",’ . {home_city_id} . ‘)’;
sc_exec_sql($insert_sql);
}
Is there a way to get the new value written in {home_addy}? when i write something that already exists in direcciones table the form is saved ok. when i write a new value in {home_addy} everything is saved ok except for the name of the street.
I’ve placed several sc_alert({home_addy}) to find where the value is lost but no success.
I tryed to use the solution given by Eistein but no success.
Can anyone helo me? Thanks
No, it will not work in editable grid view for the reason that you mentioned.
The problem you are having is probably unrelated to the autocomplete issue.
With the Scriptcase autocomplete field, the field value does not get dropped until you save the form. You can do data validation and otherwise manipulate the field without issue until you have saved the form, even if the inputted value is outside the scope of the SELECT. Since you are running you code OnBeforeUpdate, the autocomplete is probably not causing your problem (However you WILL need to apply javascript workaround that I posted previously to avoid losing the value when you save the form)
I would guess the problem is with your SQL. I would recommend echoing your $query and $insert_sql variables to ensure they are producing valid SQL. One thing to realize is that for text fields, Scriptcase pre-applies single quotes to the field value, so if you quote the field, or any variable derived from the field, the query will fail the query silently. For example, if the user types the word HELLO into the form element, then the Scriptcase field value will be ‘HELLO’ (with single quotes) when referenced in PHP, but if you quote the variable in your query, it will result in ‘‘HELLO’’ (two sets of single quotes) and the query will silently fail. So that means your $calle variable should not be quoted in your SQL.
Needless to say, you will also need to run SQL injection safeguards before you pass your inputs to the database using the sc_sql_injection() macro.
That’s just my 2 cents. Good luck.