You are not logged in. Please login or register.
My Visual Database → Posts by tcoton
Yeah but that is not a good practice database design, having redundant data!!
The best would be, in case of "not null" constraint, to set the value to 0 or whatever value is set by default for "not null" instead of "null" when removing a manager from an employee hierarchy. That cannot be done without a script since the default action of Myvisualdatabase is to set a field to "null" when deleting.
Never mind, that was my tired eyes, I was missing a 0 when typing. What is the benefit to use an edit counter for a search?
Hi Derek, I do not know why but your example does not find anything using the search.
Thanks a lot K245!! That is a trick worth knowing and it saves a lot of headaches.
I agree that with this method, the "not null" constraint must be removed, it was set to work with my workaround, but if you remove a manager from someone at some point, you get an error because of the "not null" constraint.
Well... after a brain storm with myself, I managed to do something that does the job, if someone has a better idea, by any mean, you are welcome.
Hi all, I am struggling with a very simple case that basically MyVisualdatabase was not built to handle: self referencing relationship: using already existing data in the same table to be used as an entry.
In my attached example, I would like to select the manager (who is supposed to be an existing employee) as "name firstname" and save it as an "id" from the employee table in the "manager" column without writing an SQL query.
Is this possible?
It looks like when using a self referencing auto query, it uses the table name as a column name (?)
The picture did not go through 
I have been working for a very long time with very curious people who could not help but try to look how software are working. Even at a very high technical level, I have seen people trying to understand how something was working without reading the documentation and BREAKING everything by altering one file to see what it would do. So, yes, it would be great to be able to encrypt the sqlite.db but few people are going to be able to alter the .db file. However, screwing the app by changing anything in the .dcu file cannot be easier.
I have seen "solutions" here to embed everything in a "virtual" container but I am still working on how to distribute updates afterwards...
when you say editing, are you perhaps put the calc field on a component(e.g. TEdit) in a form and add it on the save or make it editable on a grid? That won't work. If you are familiar with SQL, then that calculated field is only an alias, so it is really not in database. It's like SELECT trim(ifnull(info1||'§','')||ifnull(info2||'§','')) AS calcfield FROM yourtable. calcfield is not in database but is only in the result of your query
Yes I did try that to hide my search query from being in the .dcu file and while the search was working very well, editing via the tablegrid did exactly that!!
Beware, if you are using 2 calculated fields using a trim on different fields, you will be able to filter the grids with a text field but you won't be able to edit due to a SQL error telling you that one of the column does not exist. IT looks like the calculated fields are all charged in memory during the application initialization and there can be some mix up. I had to rethink some of my design where I could make a lot of scripts disappear ... but had to redo it.
This project looks promising if it is an evolution of MyVisual Database, would you implement the form auto-resizing depending on user screen resolution? It really is a bummer to have a fixed resolution in modern apps.
Thanks Sparrow, it does make sense to avoid false positives.
Thanks for the explanation Derek, you saved my day.
I implemented your solution using an invisible text box replicating via script the text box containing the read only value to be used as a filter, pointing to the calculated field with incremental search using an invisible button search (auto click on show via script) to retrieve my filtered data. The table grid has also a filter and all I have in my script is limited to the strict minimum. No more complicated SQL code in the script and dates are displayed according to the user system preferences.Win - win.
@Sparrow - What could cause such a behavior?
Wow!! This is very elegant. I am getting less dumb by the hour
Would you mind explaining the logic behind this clever calculated field? All I understand is that it removes and replace null characters found by some space but I do not get how it looks for the value of the text field in whichever column it would be.
I had to add a button1.click in the script for it to work the way I want, my text field cannot be edited but it will change value after selecting an object from another form.
OK. Looks like I have no other choice but to keep it in the script... The one in attachment is working as intended.
I am not trying to run a search per se, I want to filter an automatic query with multiple arguments that most work with the filter but one of them has to come from a text field in the form to try to match 2 different columns in the database that could contain the value of the text field. After playing around with the search function I understood how it was working but how do you search in 2 or more columns at once without scripts?
As per attachment.
The search button function does not work since you cannot set a condition, just select components involved in the search. I get error with the SQL Query generated looking like this:
select distinct columns, id from table where ""."" like 'correct_component_value'Something is missing with the function search or there is a trick to use it.
Hi,
As I understand it, using a filter in the way you describe will only work with a fixed condition (color = 'red' or sex = 'male' etc).
To filter according to the contents of a text field, I would use the 'search' option.
Derek.
That would work if only I did not have another condition for my filter that cannot be set with the search, ideally I would need a mix of both the auto query and the search button all in one place
I currently use scripts but I want to actually remove as much SQL scripts as possible since they can easily be read in plain text in the .dcu file.
No, Actually, I do not use incremental search by script on this one.
I am using the table grid auto query with a filter at the bottom but I want to use a text field as one of the filters. See attachment.
I know I have seen it on this forum but I cannot find it anymore, I am looking for the correct syntax to use in the filter of a table grid if we want to use one of the existing form text field as a condition.
In my case, I want to use the field "form1.EditPCName.txt" as one of the condition to dig into a log table:
old_pc_name="{EditPCName}" or New_PC_Name="{EditPCName}" and src="Computers"This does not return any results as it does not retrieve any value for {EditPCName}
Hi Derek,
thank you so much, it is so much easier than what I was doing, this solution allows to keep the automatic date format instead of having to manipulate it with complex SQL Queries!! I love simplicity!!
Does anyone know how to implement incremental search via script. I have no choice but to use a script to replace a button that was working fine and I am stuck with a rather complex query in script that has to look for entries in form1.globalsearch field.
up to now, I got a button with a on click procedure for a sql query :
select blahblahblah from table where conditionA like "%'+form1.GlobalSearch.text+'%" or conditionB like "%'+form1.GlobalSearch.text+'%"The query works but I don't get how to trigger an incremental search from the form1.Globalsearch text field via script.
My bad, I was confused by the use of "procedure" instead of "stored procedure".
I was referring to this post where Dmitry was saying that you can call a procedure using a button with SQL Query: https://myvisualdatabase.com/forum/view … hp?id=5481
My Visual Database → Posts by tcoton
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi