Search options (Page 2 of 3)

what you have in the picture seem to be on the right track; you just have to make sur that the raw data that the suer enters is as complete as possible and make sure the data flows (is saved) in the right tables with the correct linkages (primary and foreign keys).

as to your last question, the basic idea is as follows (the actual code/mechanics may not be correct and you will have to determine the actual correct syntax):

each grid/table/query should have a primary key associated with the data.
Grid 1                       Grid 2
key1  value              key2  fkey   value    <--- source table/query
1       A                       1        2        Blue
2       B                       2        4        Green
3       C                       3        3        Yellow  <-- this is a an example record that would be displayed in the Grid 2
4       D                       4        2        Red             based on the selection of "C" from Grid 1.  Other rows are shown
5       E                       5        4        Cyan           as examples of other possibilities that will not be included.

so that when the user select a row from Grid 1, say "C", then the key (or index) can be found, i.e. 3 and then using this key, we looks this up in the other Grid (2) using this key as the foreign key (fkey) to get the key/index of this data.

so after a selection ("C") is made in Grid 1, we have key1 = 3; Grid 2 data is dynamically generated using a query similar to:

select * from Table 2 where fkey = 3

I have used only simple Tables above as an illustration, but the data can come from other queries as well, however in a normalised database where each type of data (e.g. patient, results, test, etc.) should be in it's own table and if it is required elsewhere then it is referenced by it's table and key (like we were discussing in the above earlier posts).

I'm not sure what the problem you are having actually is...you have basically already given the solution you want already yourself.
you just need to create a calculated field in your table and make the result (Total) equal to Quantity + (Came in) - (Sold out).
Please have a look at this thread, http://myvisualdatabase.com/forum/viewtopic.php?id=1110, as it looks to be very SIMILAR to what you want to do.

the problem as you suspect is that the values stored in your table column are text and not integers; so to get the max value of the number starting at the 5th digit, you have to convert the text into an integer and perform the max function on that instead of the text (and the same in your check):
so it would look something like the following:

select max(strtoint(substr(t1.TelNr,-5 5))) from Telephony t1 where strtoint(substr(t1.TelNr,-5 5)) < 5560

and depending on what you are doing with the result, you may have to convert it back to a string.

@timlitw
from your error message and the variable definition, it looks like the variable trfcontid is defined as a string, but is used to look up information in the table that expects it to be an integer.
the simplest way to overcome the error is either redefine the variabe as in integer (along with ContID), or to check the value before before passing it to the SQL statement:

if (trfcontid = '') then trfcontid = '0';

this would only solve the erro part of the problem and does not help to add it into the database if it is missing; you should be able to do this after the SQL is run and a checking the result of ContID, and actioning as appropriate.

I can find the following from the functions list under string functions:

leftstr == Copy(s, String; from, count: Integer): String
length == Length(s: Variant): Integer

Looking at the help file, if the field/column of the wanted value (A, B, or C) is already selected on the current record, then you should use the sqlValue property:

e.g. SQLExecute ('INSERT INTO tablename (id_groups) VALUES ('+Form1.TableGrid1.sqlValue+')');

If only the row is selected, but the field is not, or you want other fields, then you would use a combination of the function Cells and the function SelectedRow:

e.g. ShowMessage('value of column A: ' + Cells(1,Form1.TableGrid1.SelectedRow));

The above assumes that column A had index 1.

Wow! Again I am amazed at your responsiveness, both in terms of replying and in implementing requested features.
This is very much appreciated. Thank you.

i always check what the current official version is by going to the homepage

Hi Dmitry, any chance you could replace the key combination of the replace function from Shift+Ctrl+F to just Ctrl+H (as is standard in at least Notepad and Wordpad)?  I have a hard time finding this until I did a search on forum topics.
Cheers

i had already started an example when Dmitry replied, but completed it as i didn't want it to go to waste.
so please have a look at this example as well as the one posted above by the developer.

the compiled version and sqlite.dll file has been removed to reduce the archive size.

isn't his what the http://myvisualdatabase.com/help_en/script/memo.html component is for?

using this component you do not need to add line breaks as each line is added using the Lines.Add function.

this is not exactly true, or more correctly, it is true but not very effective.
the reason is that with the current implementation of your scripting ability, it is all visible (and modifiable) as plain text files under the scripts sub-folder.
what is needed, is that the custom scripts be encrypted when stored/written (if kept as separate files), or the custom scripts to be compiled and linked directly into the main executable, so only a single program file is needed to run a developed database application (assuming that it doesn't have any custom reports as well).
even if we try to implement some sort of security or protection into the programs we make, as long as the scripts are unprotected themselves, then it would be easy for them to be reversed engineered to defeat any protection scripts that have been created.