Database Relationships

#1 · January 24, 2021, 9:31 pm

Hi,

forgive me for the dumb question but I am having a brain freeze on database relationships.

I have an access database with a main table and a subsequent table called "information1" with detailed information.

I thought that I would be able to create a relationship between the two tables and have two data grids one showing the info from the main table using the IDs as a relationship but no that didn't work.

I also tried to have access fill a field automatically from the main table into the information1 table - that didn't work.

All I want to do is have a datagrid displaying items from the information table and above the datagrid I wish to display fields from the Main table. The problem comes when I want to have a button that navigates through the records having the both the fields and datagrid in perfect harmony.

Forgive my ramblings but I have been at this for five hours and getting nowhere fast and I have to admit defeat!

Uploaded files:

You need to login to have access to uploads.

#2 · January 25, 2021, 1:11 pm

OK I have found the solution:

In access you can create a query and choose which fields appear in the query across all tables

Then in VisualNeo Win you can perform the following actions:

open the Access Database
Open the Table (But instead of an individual table you can opt to open the query)

This will allow you to create all the fields on your page and as you step through the records all the fields will be in sync.

I hope this helps someone else with the same issue.

#3 · January 25, 2021, 1:35 pm

@mark-waples

Not really sure what your intension is here and I don't know if you posted this in the plugins section but I'll give you a brief overview of how a relationship works.

First you need both tables open and then you define your relationship. Master (Main) table-field "ID" and detail (information1) table-field "ID".

Now, for every record you go to in the master table/grid, all of and only the records in the detail table with the same "ID" will show in your detail table/grid.
Let's say your master (main) table has a product inventory list...
ID 101 Widget 1
ID 102 Widget 2
and so on...

For each item in the list you have so many of different colors. So in your detail table...
ID 101 COLOR Blue QUANTITY 5
ID 101 COLOR Red QUANTITY 7
ID 101 COLOR Green QUANTITY 12
ID 102 COLOR Pink QUANTITY 1
ID 102 COLOR Yellow QUANTITY 10
ID 102 COLOR Purple QUANTITY 111
And so on...

When you click on Widget 1 in your Main table only ID 101 records will show in your information table.

As for automatically filling in fields with data from your main into your information table, I don't know why you'd want to duplicate the data. Really the only duplicated data should be the "ID" fields.

I hope this is what you were looking for.

#4 · January 25, 2021, 6:43 pm

Hi and thank you for the reply.

I created a relationship between each ID field in the access database and then in VisualNeo Win I opened the main Table and the second table and it auto generated the objects for each table for which I placed onto the page.

When I run the application I can see the fields from the first (Main) table and the Second (Information Table)

If I add data into the first table by using the autogenerated "Add" button it only enters data into the first table but does not update the second table ID to match the first so they do not sync.

I require the ID's in all tables to be the same when adding a record into the main table so in theory they should sync across the tables.

Is that possible?

TIA

#5 · January 26, 2021, 11:19 am

If you want to automatically add a record in the second table as you do with the first table using the "Add" button, you have to tell it to do so. In the "Add" button action section, copy the dbpAddRecord action but change the table name. This will simply add a record. If you have a relationship between fields you also want to populate that particular field.

For example:

dbpAddRecord "dbName" "main"
SetVar "[dbName.main.id]" "[RecordNumber]+1"
dbpAddRecord "dbName" "information"
SetVar "[dbName.information.id]" "[dbName.main.id]"

Both tables now have a record with their related fields having corresponding record numbers or however you want to identify them.

#6 · January 26, 2021, 12:04 pm

Hi Paul,

thank you that makes more sense now :)

Just one last question, if I may - How could I perform an export and import of 1 record as the data is across more than one table?

I would like users to choose a record and then export the record and send it to someone else who also has the application and they could perform an import.

Thanks again I appreciate the help

Mark

#7 · January 26, 2021, 5:00 pm

Well, there are several way you can achieve that. If you're wanting to "export" one records worth of data, you could use dbpRecordToVar and write that as one line in a file. Then on the other end dbpVarToRecord. Something like:

dbpRecordToVar "dbName" "main" "[ReVarMain]" "[#34]Delimiter=;[#34];IncludeHidden=No;ForceQuotes=No"
dbpRecordToVar "dbName" "information" "[RecVarInfo]" "[#34]Delimiter=;[#34];IncludeHidden=No;ForceQuotes=No"
FileWrite "[PubDir]Record.csv" "1" "[RecVarMain]"
FileWrite "[PubDir]Record.csv" "2" "[RecVarInfo]"

Then on the other end: FileRead and VarToRecord

dbpAddRecord "dbName" "main"
FileRead "[PubDir]Record.csv" "1" "[RecVarMain]"
dbpVarToRecord "dbName" "main" "RecVarMain" "[#34]Delimiter=;[#34];IncludeHidden=No"
dbpAddRecord "dbName" "info"
FileRead "[PubDir]Record.csv" "2" "[RecVarInfo]"
dbpVarToRecord "dbName" "info" "RecVarMain" "[#34]Delimiter=;[#34];IncludeHidden=No"

I've never actually needed to use this method so you may want to experiment a bit.

#8 · January 27, 2021, 5:51 pm

Thank you

Just the job

:)

Database Relationships - Forum

Database Relationships