<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
	<channel>
		<title><![CDATA[My Visual Database — Use current record ID within a scripted Sql Query]]></title>
		<link>https://myvisualdatabase.com/forum/viewtopic.php?id=8547</link>
		<atom:link href="https://myvisualdatabase.com/forum/extern.php?action=feed&amp;tid=8547&amp;type=rss" rel="self" type="application/rss+xml" />
		<description><![CDATA[The most recent posts in Use current record ID within a scripted Sql Query.]]></description>
		<lastBuildDate>Mon, 05 Jun 2023 19:15:18 +0000</lastBuildDate>
		<generator>PunBB</generator>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47822#p47822</link>
			<description><![CDATA[<p>@Sparrow, I did read the help before posting but it was so unclear that I did not understand how to use it. I think YOUR explanation should replace what is written in the help <img src="https://myvisualdatabase.com/forum/img/smilies/smile.png" width="15" height="15" alt="smile" /> To be fair, the MVD manual is pretty thin on how exactly we should/could use the components/methods and properties.</p><p>@Derek, the counter idea is a good idea, I will keep it for another purpose!!</p><p>I got it working with just this script:</p><div class="codebox"><pre><code>procedure Admin_OnShow (Sender: TObject; Action: string);
begin
Admin.Adm_PC_ID.Text:= IntToStr(Admin.Adm_save.dbGeneralTableId);
end;</code></pre></div><p>I still think this should not be so cumbersome to get this ID!! </p><p>Thanks folks once again!</p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Mon, 05 Jun 2023 19:15:18 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47822#p47822</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47819#p47819</link>
			<description><![CDATA[<p>Hi Tcoton, Hi Sparrow,<br /></p><div class="codebox"><pre><code>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.</code></pre></div><p>If your concern is using a field that could contain duplicates, then why not use a counter?&nbsp; <br />I appreciate that you&#039;re not using primary keys so it won&#039;t be as fast (although probably not noticeable unless you are talking about very large amounts of data).<br />Derek.</p>]]></description>
			<author><![CDATA[null@example.com (derek)]]></author>
			<pubDate>Mon, 05 Jun 2023 17:49:36 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47819#p47819</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47818#p47818</link>
			<description><![CDATA[<p>If you carefully read the MVD manual, in particular the BUTTON tabs, you will be able to find out that:<br /></p><div class="codebox"><pre><code>  dbGeneralTableId - Integer type -
  &quot;Makes sense if the button has the &quot;Save record&quot; action assigned to it. Contains the
record id, if the record is not saved, it returns -1.&quot;</code></pre></div><p>i.e.&nbsp; 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 &quot;-1&quot; For a new entry, the ID will be &quot;-1&quot; by default. In order for the ID to be saved in the form button after recording, there is a dbDontResetID property.</p><p>And many more interesting things in the MVD manual.</p>]]></description>
			<author><![CDATA[null@example.com (sparrow)]]></author>
			<pubDate>Mon, 05 Jun 2023 16:47:29 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47818#p47818</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47817#p47817</link>
			<description><![CDATA[<p>I want to add that it is super easy to get the id with a written SQL query but we all prefer to use internal features of MVDB as much as possible as it saves a lot of coding, this is why I want to understand how to get access to the hidden record ID stored by MVDB when editing a record in a form while using auto queries.</p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Mon, 05 Jun 2023 16:04:41 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47817#p47817</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47814#p47814</link>
			<description><![CDATA[<p>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 &quot;prime&quot; information displayed as well in the full project.</p><p>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 &quot;Show record&quot; and &quot;Save record&quot; 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?</p><p>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!!!!!</p><p>The &quot;show record&quot; function triggers this SQL query:</p><div class="codebox"><pre><code>SELECT table1.columnX, table1.columnY, table1.columnZ from table1 where table1.id=A</code></pre></div><br /><p>The &quot;save record&quot; function triggers this SQL query:</p><div class="codebox"><pre><code>UPDATE table1 SET columnZ=&#039;F&#039;, columnY=&#039;D&#039;, columnX=&#039;S&#039; where id=A</code></pre></div>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Mon, 05 Jun 2023 14:19:42 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47814#p47814</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47808#p47808</link>
			<description><![CDATA[<p>Hello Derek.</p><p>No matter how hard I try, I can not understand why and for what it is all done.<br />Perhaps there are other solutions, easier and more logical. <br />But it&#039;s good that you understand the problem. )))</p>]]></description>
			<author><![CDATA[null@example.com (sparrow)]]></author>
			<pubDate>Sun, 04 Jun 2023 15:25:48 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47808#p47808</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47807#p47807</link>
			<description><![CDATA[<p>Hi Sparrow, CDB,<br />Like yourselves, I&#039;d originally tried to find a solution using a calculated field (and also considered using a &#039;tag&#039;) but the scenario that neither appears to fix is if you create a new &#039;computer&#039; record (form &#039;admin&#039;) and then go immediately (without saving) to create a new &#039;maintenance&#039; record (form &#039;maintenance&#039;).&nbsp; In this scenario, I believe the PC Name and ID would be blank.<br />I can&#039;t see any way of avoiding a bit of script to resolve this particular situation.<br />And if you have to write a bit of script, you then need to consider if it is the &#039;gridsearch&#039; tablegrid or the &#039;showmaint&#039; tablegrid that has been clicked (although without knowing more details, I don&#039;t see any advantage from having two tablegrids rather than just one with a &#039;maintenance&#039; filter which obviously then simplifies the script considerably - see attached as a suggestion).<br />Derek.</p>]]></description>
			<author><![CDATA[null@example.com (derek)]]></author>
			<pubDate>Sun, 04 Jun 2023 11:53:31 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47807#p47807</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47806#p47806</link>
			<description><![CDATA[<p>show id</p>]]></description>
			<author><![CDATA[null@example.com (sparrow)]]></author>
			<pubDate>Sun, 04 Jun 2023 08:48:20 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47806#p47806</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47804#p47804</link>
			<description><![CDATA[<p>A trick you could try, is where you can get the ID save it to a &#039;form_name.tag&#039; then it is available to you everywhere while that ID is needed.</p>]]></description>
			<author><![CDATA[null@example.com (CDB)]]></author>
			<pubDate>Sun, 04 Jun 2023 03:15:06 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47804#p47804</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47801#p47801</link>
			<description><![CDATA[<p>Hi,</p><p>thanks for the example but it is not what I am looking for since I also need to get the Id from the showman grid. What I do not understand is that the ID is automatically retrieved and stored somewhere in memory when using the auto search grid to show a record but how is it possible that we cannot access it easily like any other field?</p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Sat, 03 Jun 2023 22:48:22 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47801#p47801</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47794#p47794</link>
			<description><![CDATA[<p>Hi,<br />Perhaps try it something like the attached (I&#039;ve only done it for the &#039;gridsearch&#039; and not for the &#039;showmaint&#039;.<br />I&#039;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.<br />Derek.</p>]]></description>
			<author><![CDATA[null@example.com (derek)]]></author>
			<pubDate>Sat, 03 Jun 2023 00:38:01 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47794#p47794</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47793#p47793</link>
			<description><![CDATA[<p>I have made a quick and dirty version of my huge project with no script for no interference that illustrate my issues. I am open to suggestions but I want to keep the display of form1 to remain in the same way of working.</p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Fri, 02 Jun 2023 21:21:56 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47793#p47793</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47791#p47791</link>
			<description><![CDATA[<p>Hi,<br />Without seeing the actual project (or a version of it where you can see the problem) it&#039;s not easy to suggest what the problem (and solution) might be.<br />Derek.</p>]]></description>
			<author><![CDATA[null@example.com (derek)]]></author>
			<pubDate>Fri, 02 Jun 2023 19:24:49 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47791#p47791</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47790#p47790</link>
			<description><![CDATA[<p>Here is what I mean by the record ID that does not show. I kind of solved my issue with the combobox, but I do not understand why the id does is not displayed in the Admin form while it is on the Maintenance form.</p><p>Both grids on Form1 are using grid search to display the info and the other forms are just called using the &quot;show record&quot; property of a button.</p><p><span class="postimg"><img src="http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&amp;item=9532" alt="http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&amp;amp;item=9532" /></span></p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Fri, 02 Jun 2023 18:45:39 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47790#p47790</guid>
		</item>
		<item>
			<title><![CDATA[Re: Use current record ID within a scripted Sql Query]]></title>
			<link>https://myvisualdatabase.com/forum/viewtopic.php?pid=47786#p47786</link>
			<description><![CDATA[<p>Hi Sparrow, I do agree, I replaced with an Update statement.</p><p>Derek&#039;s answer works like this</p><div class="codebox"><pre><code>sqlexecute(&#039;update computers set id_status = &quot;2&quot; where id = &#039;+form1.gridsearch.row[form1.gridsearch.selectedrow].id&#039;);</code></pre></div><p>Now I am scratching my head to see if I can use an &quot;OR&quot; in the query as I need to use 2 sources for the current ID to change a combobox display.</p><p>Form1 has 2 grids: GridSearch (search all records with filters) <br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ShowMaint (Filters all records with 1 status only)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =&gt; 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</p><p>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.</p>]]></description>
			<author><![CDATA[null@example.com (tcoton)]]></author>
			<pubDate>Thu, 01 Jun 2023 22:38:22 +0000</pubDate>
			<guid>https://myvisualdatabase.com/forum/viewtopic.php?pid=47786#p47786</guid>
		</item>
	</channel>
</rss>
