Quote from farhad2008 on October 15, 2020, 5:12 pm

Hi,

Please give an example file

 

Quote from joferar333 on October 15, 2020, 6:23 pm

Hi send you example files

Thanks!!!!

Quote from farhad2008 on October 15, 2020, 7:12 pm

replace

dbpOpenAccessDatabase "test" "example.mdb" ""

with this

dbpOpenAccessDatabase "test" "[PubDir]example.mdb" ""

 

Quote from joferar333 on October 15, 2020, 7:20 pm

Yes I did that but it does not affect the problem I have, because even so on my pc everything works, it is clear that having the base route is better but that does not affect the issue that concerns me, if you see the step in detail Following the example of the source that I send you, you will see that the problem lies in how to display and view the table with the assigned table relationship.

Quote from Gaev on October 15, 2020, 9:58 pm

@joferar333

I have examined the contents of the .zip file you uploaded.

I have also read the Help file information related to the dbpDefineRelationship command here ... https://neodbprohelp.visualneo.com/Tables.html#dbpDefineRelationship

Even for me (whose primary language is English), the description of how it works is a bit confusing.

Once a relationship is defined, navigational changes to the master table will automatically display matching records in the detail table.

What it implies is that you need to show BOTH Tables, each in its own Grid View ... then, as you navigate the records in the first Table, the Grid View for the second Table will display "specified fields/columns in related records"

So, try and add this ...

a) a Rectangle named vistabase2

b) a command ... dbpShowGrid "test" "2" "vistabase2"

Now, when you run the project, as you move up/down in the first Table, the matching records of the second Table will be automatically displayed in vistabase2

Note that ...

a) you could have more than one matching record in the second Table.

b) you do not need the first Grid in order to navigate that Table ... it can be done via sript (dbpFirst, dbpNext etc.).

Quote from joferar333 on October 16, 2020, 12:05 am

Friend thank you very much now if you understood it and it works perfect. The problem was that, as you say, it is very confusing to interpret how it works. I thought that everything should be shown in the same view and hence the error, with your comment I could finally realize that I tried and if it is as you say you are right.

Now suppose that the related table in detail contains more than one record, is there a way to show them all in a list? without having to navigate one by one an idea

Note: Sorry for my english this translate to google i only speek spanish.

Quote from Gaev on October 16, 2020, 3:53 am

@joferar333

Now suppose that the related table in detail contains more than one record, is there a way to show them all in a list? without having to navigate one by one an idea

I do not understand what you mean by "show them all in a list" ... do you want (one of the fields/columns) in the related records to be displayed as items in a ListBox ? ... or as lines (one per record) in a TextBox ?

Both are possible ... take a look at dbpFieldToVar ... https://neodbprohelp.visualneo.com/ImportExport.html#dbpFieldToVar ... then, the contents of the variable can be shown in either type of Box.

Note that if all you want to do is list a field in matching records from another Table, you do not need to do dbpDefineRelationship ... it is easier to just use dbpQuery on the second Table ... then dbpFieldToVar like before.

Quote from joferar333 on October 17, 2020, 12:17 pm

The idea is to be able to list all the content of a base field and show it in a list so that it can be selected. You mention using dbpQuery  in the second table, the truth is I don't know how to use this, could you give me an example of how the command would be. thank you in advance

Quote from Gaev on October 17, 2020, 8:08 pm

@joferar333

1) Create a ListBox named (say) ListBox10 ...

a) set List items to [myItems]
b) set Variable (to store selected item) to [ListBox10]

2) open the database table

dbpOpenTable "test" "2" ""

3) say you want to only see records where field id is greater than 7

dbpQuery "test" "2 "id > 7"

4) in order to populate the ListBox with the contents of field Inmueble

dbpFieldToVar "test" "2" "Inmueble" "[myItems]" "Delimiter=[#13];SkipBlanks=Yes;NoDuplicates=Yes"

Quote from joferar333 on October 18, 2020, 10:44 am

Friend you have really given me a great hand and I am eternally grateful. Only one final concern that is blocking my project, applied what you indicated to me, I can not figure out how to do so that it shows me all the records that belong to a certain person
I need to put in a variable all the records that belong to a certain surname ...

 

3) say you want to only see records where field id is greater than 7

dbpQuery  "inmobiliaria" "inmuebles."id > 7"  i need all record to "apellido"

The table "inmuebles"  registers the houses that each owner has, but an owner can have more than one property, this is why I need the owner xxxxx to bring me and show me in a grid all the properties that this xxxxx has.

Thanks - BesT regards

 

 

Quote from Gaev on October 18, 2020, 2:05 pm

@joferar333

i need all record to "apellido"

The format of the "filter" parameter in dbpQuery (see https://neodbprohelp.visualneo.com/SearchQuery.html#dbpQuery for more details) is ...

dbpQuery "inmobiliaria" "inmuebles" "appellido = [#34]Garcia[#34]"

However, if the surname (appellido) value to be matched is in a variable (e.g. [inputAppellido]), then you can ...

dbpQuery "inmobiliaria" "inmuebles" "appellido = [#34][inputAppellido][#34]"

 

 

 

Quote from joferar333 on October 19, 2020, 12:01 am

Perfect! work  bad  I got stuck.
You see, I have two tables, one owners and the other real estate
both tables have a field "Surname" and another "Name"
Between the two tables I established a relationship and both tables show them
in a different view.

When opening everything perfectly the problem appears to me that when
I modify in table 1 the name or surname does not modify it in table 2 really on the verge of an attack I tried everything but I did not get any ideas?

 

code page

 

dbpOpenDatabase "inmobiliaria" "[PubDir]inmoparana.mdb"
dbpOpenTable "inmobiliaria" "propietarios" "traduccion"
dbpOpenTable "inmobiliaria" "inmuebles" "traduccion"

dbpSetGridProperties "inmobiliaria" "propietarios" "ShowScrollBars=Vertical;ShowNavigationBar=No"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Apellido" "TitleAlignment=Center"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Nombre" "TitleAlignment=Center"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Apellido" "Visible=Yes"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Nombre" "Visible=Yes"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Direccion" "Visible=No"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Telefono" "Visible=No"
dbpSetFieldProperties "inmobiliaria" "propietarios" "Email" "Visible=No"

dbpShowGrid "inmobiliaria" "propietarios" "vistapropietarios"
dbpDefineRelationship "inmobiliaria" "propietarios" "Apellido" "inmuebles" "Apellido" "Ubicacion"

dbpQuery "inmobiliaria" "inmuebles" "Apellido = [#34][inmobiliaria.propietarios.Apellido][#34]"

dbpSetFieldProperties "inmobiliaria" "inmuebles" "Ubicacion" "TitleAlignment=Center;ReadOnly=No"
dbpSetFieldProperties "inmobiliaria" "inmuebles" "Apellido" "TitleAlignment=Center;ReadOnly=No;Visible=No"
dbpSetFieldProperties "inmobiliaria" "inmuebles" "Nombre" "TitleAlignment=Center;ReadOnly=No;Visible=No"
dbpSetFieldProperties "inmobiliaria" "inmuebles" "Importe" "TitleAlignment=Center;ReadOnly=No;Visible=Yes"
dbpSetFieldProperties "inmobiliaria" "inmuebles" "Honorarios" "TitleAlignment=Center;ReadOnly=No;Visible=Yes"
dbpSetFieldProperties "inmobiliaria" "inmuebles" "Liquidar" "TitleAlignment=Center;ReadOnly=No;Visible=Yes"

dbpShowGrid "inmobiliaria" "inmuebles" "listadodepropiedades"

and code buton update:

.dbpOpenTable "inmobiliaria" "propietarios" "traduccion"

dbpSaveEdits "inmobiliaria" "propietarios"
dbpSaveEdits "inmobiliaria" "inmuebles"

.dbpOpenTable "inmobiliaria" "inmuebles" "traduccion"

MessageBox "EDITAR DATOS PROPIETARIOS" "El registro de [inmobiliaria.propietarios.Apellido],[inmobiliaria.propietarios.Nombre] |ha sido actualizado. Desea editar otro registro?" "Si|No" "[accion_alta]"

if "[accion_alta]" "=" "1"
ClearVariables ""
endif

If "[accion_alta]" "=" "2"
GotoPage "inicio"
endif

Quote from Gaev on October 19, 2020, 2:58 am

@joferar333

You are making it unnecessarily complicated by using the dbpDefineRelationship command ... the benefits are minute (meant for howing simple master-detail relationships)  vs. the loss of fine grained control (like the stuff you need).

In any case, if dbpDefineRelationship shows matching records, you don't need dbpQuery commands to do the same thing.

Also, dbpQuery only acts on the current values you specify ... it does not automatically re-apply the query every time the parameter values change.

Here is YOUR relevant code ...

dbpOpenDatabase "inmobiliaria" "[PubDir]inmoparana.mdb"
dbpOpenTable "inmobiliaria" "propietarios" "traduccion"
dbpOpenTable "inmobiliaria" "inmuebles" "traduccion"

dbpShowGrid "inmobiliaria" "propietarios" "vistapropietarios"
dbpDefineRelationship "inmobiliaria" "propietarios" "Apellido" "inmuebles" "Apellido" "Ubicacion"

dbpQuery "inmobiliaria" "inmuebles" "Apellido = [#34][inmobiliaria.propietarios.Apellido][#34]"

dbpShowGrid "inmobiliaria" "inmuebles" "listadodepropiedades"

... I am not sure what code gets invoked in subroutine named traduccion ... which appears to be called whenever a record changes in either table.

Try this code ...

dbpOpenDatabase "inmobiliaria" "[PubDir]inmoparana.mdb"

... open this table first; no subroutine
dbpOpenTable "inmobiliaria" "inmuebles" ""
... this will momentarily show all records
dbpShowGrid "inmobiliaria" "inmuebles" "listadodepropiedades"

... now open table propietarios; see code for subroutine below ...
dbpOpenTable "inmobiliaria" "propietarios" "cambioPropietarios"
... now show all records in grid; this should trigger subroutine cambioPropietarios
dbpShowGrid "inmobiliaria" "propietarios" "vistapropietarios"

Here is the code for the subroutine that will be invoked every time the current record in propietarios is changed or navigated to another record.

:cambioPropietarios
... you want to invoke the query, so records in table inmuebles are ONLY those that match the Apellido field in the table propietarios
SetVar "[thisMatch]" "[inmobiliaria.propietarios.Apellido]"
dbpQuery "inmobiliaria" "inmuebles" "Apellido = [#34][thisMatch][#34]"
Return

Try this to confirm that the second grid shows matching records as you navigate the first grids.

If you need to extract content (of matching records) from the second table, insert that code in this subroutine (after the dbpQuery command)

I am not sure why you are doing the dbpSaveEdits ... you can let the plugin do it automatically whenever a change is made to a record ... read the Help info for dbpSetAutoEdit.

Verify that the logic correctly shows the matching records before moving on to the User Interface commands (like dbpSetFieldProperties)

 

Quote from joferar333 on October 21, 2020, 4:31 pm

Everything worked wonders for me, but I have a small problem that I cannot solve, you will see after I add or modify data in any table and I try to close it before leaving the page, it gives me the error "IT IS NOT POSSIBLE TO INSERT AN EMPTY ROW. IT MUST BE HAVE AT LEAST ONE ESTABLISHED COLUMN VALUE "

Here's an example ...

any idea how to fix this so that it closes the table without errors?

dbpOpenTable "inmobiliaria" "propietarios" ""

SetVar "[inmobiliaria.propietarios.Apellido]" "[apellido]"
SetVar "[inmobiliaria.propietarios.Nombre]" "[nombre]"
SetVar "[inmobiliaria.propietarios.Direccion]" "[direccion]"
SetVar "[inmobiliaria.propietarios.Telefono]" "[telefono]"
SetVar "[inmobiliaria.propietarios.Email]" "[email]"

dbpAddRecord "inmobiliaria" "propietarios"

MessageBox "ALTA DE PROPIETARIOS" "El registro y alta de [apellido],[nombre] |ha sido exitoso. Desea hacer un nuevo registro?" "Si|No" "[accion_alta]"

if "[accion_alta]" "=" "1"

SetVar "[apellido]" ""
SetVar "[nombre]" ""
SetVar "[direccion]" ""
SetVar "[telefono]" ""
SetVar "[email]" ""

DisableObject "boton_propietario_alta"
FocusObject "apellido_propietario"

endif

If "[accion_alta]" "=" "2"
dbpCloseTable "inmobiliaria" "propietarios"
GotoPage "inicio"
endif

 

Quote from Gaev on October 21, 2020, 7:20 pm

@joferar333

after I add or modify data in any table and I try to close it before leaving the page, it gives me the error "IT IS NOT POSSIBLE TO INSERT AN EMPTY ROW. IT MUST BE HAVE AT LEAST ONE ESTABLISHED COLUMN VALUE "

1) I can not tell when your script is invoked ? ... is it attached to a PushButton ? ... elsewhere ?

2) When you do a dbpOpenTable, the current record is the first record in the Table ... so when you follow it up with a bunch of SetVar commands, the first record will be updated ... I don't think this is what you want to do.

3) Next, you invoke the dbpAddRecord ... which will append an empty record in the Table !!! ... and if one of the fields is specified as "required", you will experience that message when trying to close everything.

Perhaps the dbpAddrecord should come BEFORE the SetVar commands ?

If things still don't work, please upload your pub with your next post (just make sure the data is fictitious).

You will learn about how things work much faster if you take it one step at a time ... after each step (command), examine the Grid and the Debugger to educate yourself on what changed.

Quote from joferar333 on October 22, 2020, 9:15 am

Friend, the truth is, I don't know where I'm missing you ...
I made you a small script so that you can see the problem I cannot find the solution.

Quote from Gaev on October 22, 2020, 10:40 pm

@joferar333

I have reviewed your sample app.

There were 2 problems ...

1) your references to the variables associated with the TextEntry Boxes (i.e. file1 and file2) ...

a) not a good idea to name variables [1] and [2] ... safer to have names be alphanumeric
b) in the SetVar commands, you referenced these variables as "1" and "2" instead of "[1]" and "[2]"

2) as mentioned in my earlier post, your script for the Add Button was ...

setvar "[basedemo.test.file1]" "1"
SetVar "[basedemo.test.file2]" "2"
dbpAddRecord "basedemo" "test"

... which is incorrect ... the setvar commands will UPDATE the current record ... and then an empty record will be created ... the correct script should be ...

dbpAddRecord "basedemo" "test"
setvar "[basedemo.test.file1]" "[1]"
SetVar "[basedemo.test.file2]" "[2]"

... this way, and empty new record is created first, and then it is populated with the variables [1] and [2].

Also, I found the design to be somewhat confusing for the user ... it would be less confusing if there were two buttons ...

- the ADD button with the above commands
- the END button which would close everything and navigate to the other page.

 

 

Quote from joferar333 on November 14, 2020, 1:09 am

Everything worked wonders for me a thousand thanks for your guidance.

I have only one final problem, you will see I already have the database with data and I realized that a field was short when setting the maximum length of characters I put it in 40 when in reality I should have put 80 is there any way to edit and correct this without having to do the whole table again since you would lose all the loaded data?

 

Best Regards

 

Quote from juancarlos on December 15, 2020, 6:38 am

ya lo tienes solucionado, pero creo que habia una forma , tendria que testearlo pero si lo haces por sql , select y un inner join y creo que salia en la misma tabla los campos que unieras de la consulta de mysql , y tambien otra opcion crear view con dos tablas y tambien te salian