Multiple Linking Fields

[John Gamble]

Re: Multiple Linking Fields

The link Dan has provided is the method I have used for years.
I can confirm it works for three fields and see no reason why you could not use 4-5......

A4 handled this transparantly and usually suggested the correct combination of fields.

I remember discussing this with Alpha when A5 came out. The answer at the time was "why would you want to". My answer of course was that A4 did it, why could'nt A5. Other DB's support this, and it is a standard industry practice. I believe this was a misstep by Alpha.

While the solution does work, it comes at a cost.

A change in size or character to any of the concatinated fields means a :( job affecting your entire app. This negates the significant DB advantage of field independance.

John

[ChristopherG]

Re: Multiple Linking Fields

Other DB's support this, and it is a standard industry practice. I believe this was a misstep by Alpha.

John

Hi John,

I agree you that the lack of support for such fundamental relational concept (at least for users coming from a dbms background) is a bit odd. I guess it is more annoying than fatal though.

Access always fell short for me because it could not do views, triggers or stored procedures. Once you hit the limits of a query, you have to make a fairly large leap into VBA programming to get the effect of a stored procedure. This is where the RDBMSs shine, in the sense that database entities are generated through procedural SQL, which is so close to standard query sql, that it is relatively simple to write and implement views, procedures, triggers, stored procedures etc ... powerful stuff.

I suppose though that in order for Alpha to give us a codeless GUI development environment (which is in itself very powerful) the outcome of entity creation and structure has to be quite predictable. Most Oracle procedures that I have written (more hack than write reallly :o) could never be 'genie-fied'.

But for me this would be the holy grail. A rapid development environment like A5, running an internal db with industry standard entities. One can only dream... :)! Oracle has an Application designer that effectively does this, but its web only (i think) and more expensive to implement. If Microsoft decides to move MS Access and MS Sql Express closer together in terms of the way the Access DB functions, while maintaining Access's other strengths, then Alpha will have some serious competition.

Hopefully, as linking to external DB becomes more popular, A5 will find itself having to adapt its approach to mimic and better integrate with Oracle/MSSQL etc features.

In the mean time, I am thoroughly impressed with Alpha and can see from the reponses on the list that virtually anything is possible if I just apply the grey matter and read the help!

Cheers
Chris

[ChristopherG]

Re: Multiple Linking Fields

Hi All,

Have finally sat and worked in Dans solution into the attached sample.
The sample has four levels, project->district->area->block.

The database contains both an 'a' and a 'b' group of tables, sets and forms.

Group 'a' uses traditional multiple field linking (as described by Tim, Stan and Mike). There are two forms:

FORM: a_project_to_block_f1
Default form which requires linking fields to be displayed in child browses so that data entry can work.

FORM: a_project_to_block_f2
Modified form in which buttons open subforms for new record entry. These forms are linked using the 'form.viewlinked()' function recommended by Tim.

Group 'b' uses calculated fields linking to the tables lower down in the heirachy (as described by Dan). There is one form to illustrate:

FORM: b_project_to_block_f1

The calculated field option is easy set up and the links work well. Unfortunately I seem to have broken the 'Delete Record' functionality in both form types record deletion works intermittently. On most occasions there is no response from the right click->delete record, even on the bottom most child. Any suggestions appreciated.

I have yet to try the LOOKUP solution but will post when done.

Cheers
Chris

[ChristopherG]

Re: Multiple Linking Fields

An once again ... with attachment.:o

[Keith Hubert]

Re: Multiple Linking Fields

Hi Chris,

That looks and works great. Pity about the crappy title in the dialog box. Is there an option to have your own title?

[ChristopherG]

Re: Multiple Linking Fields

Hi Keith,

Quite right. It can be set with the Caption parameter of the form properties. Will clean up after I put in the last Lookup option.

By the way, does record deletion work?

It is still very inconsistent for me. I am going to try and clean out all the old data and insert from scratch. It may be that some fields are not populated properly.

Cheers
Chris

[John Gamble]

Re: Multiple Linking Fields

Chris

You may just have ID'd a problem I also have had with deletes since day one.

On certain screens I get a "INEXACT MATCH" when trying to delete. a child record. Have to resort to a single table browse to delete.

Interesting, bug?!

John

[ChristopherG]

Re: Multiple Linking Fields

Hi John,

Even odder (if there is such a thing!), is that deletion will also not work consistnently in the standard browse for the 'b_block' table in the sample db.

It sometimes just refuses to delete with the cursor going into the record and settling on one of the fields.

I still think it may have something to do with the data in the sample. For instance, I have spaces in the linked fields of the 'b' table set (i.e. calculated field linking). Still checking though.

Cheers
Chris