Page 1 of 1

Relationships question

PostPosted: Sun Jul 07, 2013 5:34 pm
by djpilot
Hi all,
I am creating an app that uses various tables from an access database.
Some of the tables are use purely for populating some combo boxes.
I also have some tables that are used for manual 'relationship' storage.
I was hoping there may be a easier way to implement what I want to do, and was hoping that the 'dbpDefineRelationship' command would help me.

First scenario;
A table contains a two fields, a unique auto-increment primary key and a string field. I use the following code to populate a variable;
Code: Select all
dbpOpenTable "[mydb]" "[tablename]" ""
dbpSort "[mydb]" "[tablename]" "fimtype=ASC"
dbpFieldToVar "[mydb]" "[tablename]" "[fieldname]" "[fieldlist]" "Delimiter=[#13];SkipBlanks=Yes;NoDuplicates=Yes;DelimiterInContext=Leave"
dbpCloseTable "[mydb]" "[tablename]"

I then put the variable [fieldlist] into the 'List Items' section of a ComboBox. The ComboBox variable is set to use the item text for the variable as I need to sort the data from the table and cant assume the items are in alphabetic order anyway meaning I cant just used the ComboBox item number. When someone changes the ComboBox, I have code that searches the Table for the string, then copies the PK value into the other table as a numeric field. Surely I can do this automatically using a relationship of some type, but I am unsure how to do that? Please note there are NO grids being used.

Second scenario;
I have 3 tables, All tables contains a unique numeric PK.
The 1st and 3rd tables contain various string and numeric fields.
The 2nd (middle) table contains only 3 numeric fields (including the numeric PK).
Essentially what I use this table for it to link any number of records in the first table with any number of records in the 3rd table by storing the PK of each related record from table 1 and table 3 in a single record of the 2nd table.
I think this could be called a 'many-to-one-to-many' relationship.
The problem I have is the amount of code required to display the results.
As an example (and this is always the case) I would normally be viewing a record in the 1st table, but also wish to view any related records from the 3rd table, and also vice-versa.
Is there some way I can use the 'dbpDefineRelationship' command to do this?

As always, any help is greatly appreciated.

Re: Relationships question

PostPosted: Mon Jul 08, 2013 11:17 am
by Neosoft Support
...The ComboBox variable is set to use the item text for the variable as I need to sort the data from the table and cant assume the items are in alphabetic order anyway meaning I cant just used the ComboBox item number. When someone changes the ComboBox, I have code that searches the Table for the string, then copies the PK value into the other table as a numeric field. Surely I can do this automatically using a relationship of some type, but I am unsure how to do that? Please note there are NO grids being used.


It's not possible to define a relationship between a ComboBox and a table, so the method you're using is probably the best option. However, you may want to look at DBPro's dbpPopupValueList action which you can use to display a popup window containing a list of lookup items derived from a field in a table. dbpPopupValueList does not require a grid.

...Essentially what I use this table for it to link any number of records in the first table with any number of records in the 3rd table by storing the PK of each related record from table 1 and table 3 in a single record of the 2nd table. I think this could be called a 'many-to-one-to-many' relationship. The problem I have is the amount of code required to display the results. As an example (and this is always the case) I would normally be viewing a record in the 1st table, but also wish to view any related records from the 3rd table, and also vice-versa. Is there some way I can use the 'dbpDefineRelationship' command to do this?


You should be able to do this with dbpDefineRelationship, but you will have to experiment to see if it produces the desired results. The action basically works like an automatic query. When the record in the specified master table changes, a search is performed on the detail table based on a common field. If you need multiple relationships, you can call dbpDefineRelationship multiple times.