Use current record ID within a scripted Sql Query

Hi,
I'm not at a PC at the moment to be 100% sure of the syntax but try something like this:

sqlexecute('replace into computers (id_status) values (2) where id = "'+form1.gridsearch.row[form1.gridsearch.selectedrow].id+'"');

Hi Sparrow, I do agree, I replaced with an Update statement.

Derek's answer works like this

sqlexecute('update computers set id_status = "2" where id = '+form1.gridsearch.row[form1.gridsearch.selectedrow].id');

Now I am scratching my head to see if I can use an "OR" in the query as I need to use 2 sources for the current ID to change a combobox display.

Form1 has 2 grids: GridSearch (search all records with filters)
                                                                                                     
                               ShowMaint (Filters all records with 1 status only)

                                              => Both can open the same Admin form and the computers.ID should be consistent whether accessed from one grid or another but... it does not work. I tried to display the computers.id in a field on Admin using the object properties is and it does not refresh when I open the form using one grid after the other. I tried a script but I am stuck

What I want to do is change the Admin.StatusList combo display according to current status as soon as I save a certain status in Maintenance form.

Hi,
Without seeing the actual project (or a version of it where you can see the problem) it's not easy to suggest what the problem (and solution) might be.
Derek.

Hi,
Perhaps try it something like the attached (I've only done it for the 'gridsearch' and not for the 'showmaint'.
I've put the PC Name and record ID values into the form captions but they could just as easily be placed in discrete edit fields.
Derek.

A trick you could try, is where you can get the ID save it to a 'form_name.tag' then it is available to you everywhere while that ID is needed.

Hi Sparrow, CDB,
Like yourselves, I'd originally tried to find a solution using a calculated field (and also considered using a 'tag') but the scenario that neither appears to fix is if you create a new 'computer' record (form 'admin') and then go immediately (without saving) to create a new 'maintenance' record (form 'maintenance').  In this scenario, I believe the PC Name and ID would be blank.
I can't see any way of avoiding a bit of script to resolve this particular situation.
And if you have to write a bit of script, you then need to consider if it is the 'gridsearch' tablegrid or the 'showmaint' tablegrid that has been clicked (although without knowing more details, I don't see any advantage from having two tablegrids rather than just one with a 'maintenance' filter which obviously then simplifies the script considerably - see attached as a suggestion).
Derek.

The purpose is very simple, I need to use scripts to update information not shown in the current form to be updated according to the id of the record being edited and not using a field that could contain a duplicate. Why do I use 2 table grids in Form1 is because the client needs to see which computers are in need of maintenance and access them without having to lookup for them to not forget about them. There are other "prime" information displayed as well in the full project.

Now I have tricked the app by altering the database while the app was open so it would throw an SQL error to see exactly what it does when using "Show record" and "Save record" functions within the grids and the SQL query triggered contains indeed the id of the computer related to what I am doing!!! The id looks like it is stored as a temporary variable within MVDB framework when saving the record. So, where is this id stored that we cannot access it with scripts or even display in a field?

I even tried to select the computers.id within the search grid, it is displayed in the table grid but we cannot display it in a field as it is not available in the dropdown of the field properties!!!!!

The "show record" function triggers this SQL query:

SELECT table1.columnX, table1.columnY, table1.columnZ from table1 where table1.id=A

The "save record" function triggers this SQL query:

UPDATE table1 SET columnZ='F', columnY='D', columnX='S' where id=A

If you carefully read the MVD manual, in particular the BUTTON tabs, you will be able to find out that:

  dbGeneralTableId - Integer type -
  "Makes sense if the button has the "Save record" action assigned to it. Contains the
record id, if the record is not saved, it returns -1."

i.e.  If you have opened a form to edit a record, this property will contain the ID of the record from the table for which you are editing. If the entry fails, the ID will change to "-1" For a new entry, the ID will be "-1" by default. In order for the ID to be saved in the form button after recording, there is a dbDontResetID property.

And many more interesting things in the MVD manual.