Multiple Linking Fields

[ChristopherG]

Hi All,

Thank you for a great list! It has been an invaluable reference for a newbie like me.

I have a problem with my first A5V9 app. I traditionally work in Oracle or MySQL with tables that have multiple fields as the primary key. See two examples in the attachment.

I have now created the active link tabs in A5 but cannot seem to find a clear reference to how to create the linking field expression for either of these sets. If I simply say 'Field1+Field2' (probably dum, but worth a shot!), the link does not work in the child browse on the sets default form.

I have tested a restructured version that uses a unique id field in each table and this works well, but I dont particularly like working with such extremely normalised (?) data on a relatively simple app.

Any insights appreciated.

Regards
Chris

[ChristopherG]

Re: Multiple Linking Fields

Hi,

A followup on my own post.

I note from the A5 Help that sets of the type I am trying to make are not recommended.

The recommendation leans toward a single linking field, of autoincrement type independant of data. If data field linking is required, then multiple sets are recommended.

I presume this means the only option is to introduce an auto increment id and unique contraints on the other field combinations.

Cheers
Chris

[Keith Hubert]

Re: Multiple Linking Fields

Hi Chris,

Welcome to Alpha.

The best advice all newcomers are given is have a good look at AlphaSports.

It does take some rooting about to get to grips with whats going on, but you sound like the kind of guy that can get the picture with relative ease.

To use an expression in a field that is going to be used as a link to a child table, I have found that it is best to make a new field that is calculated ie 'Field3=Field1+Field2'. Field3 will now be the linking field.

Please bear in mind that most users of Alpha do not come from the same background as yourself, and are therefore not always able to pick up on what you are having difficulty with when you make references to it.

I suggest first doing a search and then asking for help to your actual problem.

Good luck with your Alpha development.

[ChristopherG]

Re: Multiple Linking Fields

Hi Keith,

Thank you for the assistance. Sorry if I was a bit cryptic!

I did study several of the sample apps including AlphaSports, but found all of the sets to be single field links. I was hoping more was possible!

I get your solution but unless I miss my guess, I can only do this with A5 tables. This would leave 2 options:

1. Retructure the external DB to a unique ID and datafields separated into separate tables and then use MySQL views or A5 Mapped Tables for the combined data view.

2. Emulate your solution in MySQL by keeping the existing table structure, but adding a field to each table concatenating the data values to be used in the link. I would then need to write insert triggers in MySQL to do the concatenation each time there is a source field change.

I must admit that as much I have been impressed with A5 so far, their non-traditional approach to database structures seems, although workable, rather odd for someone more used to Oracles or MySQLs powerful simplicity. I guess its the price paid for an efficient codeless development environment, which is a BIG plus.

Thank you again.

Regards
Chris

[Keith Hubert]

Re: Multiple Linking Fields

Hi Chris,

Yes you are correct about only being able to link by a single field.

However, there is a neat little trick you can use. Link the same child table more than once on different fields in the same set.

I'm sure you have also seen that you can also build separate sets, with the same tables but in a different order and with different link settings.

[ChristopherG]

Re: Multiple Linking Fields

Hi Chris,

Yes you are correct about only being able to link by a single field.

However, there is a neat little trick you can use. Link the same child table more than once on different fields in the same set.

I'm sure you have also seen that you can also build separate sets, with the same tables but in a different order and with different link settings.

Hi Keith,

I have linked the same table twice in the same set with each of the key fields in turn, but so far I get some unacceptable side effects:

1. The child data is not always filtered correctly.
2. Because a single field (i.e. only one of the two primary keys) is used for the link, you cannot use these child browses to add records to the active link tables. Doing so violates the integrity constraints in MySQL.

I have tried reversing the set, however one immediately strikes the same problem i.e. there are two primary key fields in the linked database. The only thing reversing achieves is to go from a one-to-many to a one-to-one.

I still seem to be left with the two previously mentioned options.

Cheers
Chris

[Stan Mathews]

Re: Multiple Linking Fields

I am unclear about the imagined constraint as to linking on only one field. The first choice in any set definition genie for the linking key, parent or child, is <expression>. That expression can involve multiple fields and explicit values. If Alpha can determine a likely linking key based on pairs of same named fields it will suggest that as the linking key bu you can still up arrow to the top of the possible fields to use and find <expression>.

This may not satisfy your sql requirements but is doable. If memory serves it is recommended that if multiple fields are used in the link they should be contiguous in the table structure.

From the help on Create and Restructure sets

Linking fields do not have to have the same name, but they do have to have the same type and length. For example, the Cust_ID field in the Invoice table may match the Cust_NO field in the Customer table.

Note : Linking fields do not have to be a single field. For example, the invoice number in the Invoice table consists of two fields. The first field, Tran_Type, indicates whether the transaction is a sale or refund, and contains the value "S: or "R." The second field, Slip_Num stores an incrementing number (e.g., 001, 002, 003, etc.). The invoice number consists of the first field plus the second field, resulting in invoice numbers that look like: S001, R002, S003, etc. When you link the Invoice table to the Items table, the Items table has just one field to store the invoice number, Inv_NO. To link Invoice to Items, you specify Tran_Type+Slip_Num as the Parent Key and Inv_NO as the Child Key.

Note : Linking fields in parent or child tables cannot be calculated fields.

Note : Linking fields in child tables cannot be auto-sequence fields.

[Keith Hubert]

Re: Multiple Linking Fields

Hi Stan,

Thanks for straitening me out about using the expression as a link.

I have tried to use this in the past and failed, since then I have never used the expression value as a link.

On the other hand I'm sure that I have used a calculated field as a link, but I could be wrong about that too.

Back to Alpha school for me.

[MikeC]

Re: Multiple Linking Fields

In addition to what Stan stated, a linking field can only be a total length of 240 characters. If you run up against this (which I do quite often in Quickbooks!), a simple remedy usually is just to use maybe the first 100 characters per field (in a two field link) or first 50 (in a 4 field link), etc.--whatever is required-- the substr(), left(), word() functions work well for this.

[Tim Kiebert]

Re: Multiple Linking Fields

Stan,
I have tried this before and although the set does link properly when the link values are provided Alpha does not automatically fill in the child linking value when an expression is used for the parent.

I made a little test to demonstrate. Inv_set is linked as per the instruction you posted. Inv_set2 is linked on a calculated field in the parent.

When you open up the default form for Inv_set you get the first clue that some thing is different. Normally by default the child linking field is not included in the embedded browse. Try entering some records by supplying only a value for product and you should see that the child key is not supplied. In fact the record 'disappears' on save because the child key is wrong. If you now go into the inv_line table and enter the child key the records will show up fine.

Entering records in the Inv_set2 default form behaves as expected. Which shows that the note about calculated fields not being allowed for the parent linking key is not accurate.

I have found though that you can not use a calculated field in the child table. But you can use an expression using two fields for the child table link (Inv_set4). Hhmmmm, I figured I might as well test that and it seems a calculated field in both tables does work. (at least in this simple test)(Inv_set3)


Ok this may confuse the issue but. In Inv_set6 I have a calculated field as parent link and an expression for the child link. I made the field names the same in the child (inv_line2) as the parent. However, now the records seem to only get linked on the the first field of the parent calculation and the first field of the child expression, (ie Tran_type). Change the name (in any way) of Tran_type in inv_line2 and then the link is forced back to both fileds of the calc/expression.

Don't know what all this proves. Just be careful in what you do I guess.

The fact that Alpha does not automatically provide the linking key when multiple fields are used can be worked around. If you force new child records to be entered in forms rather than in embedded browses you can use the new to v8 function of form.viewlinked(). This allows you to specify multiple fields on which to base the link and the linking values are enforced and not editable in the child form. An added benefit of this function seems to be a much better synchronization with the browse on the parent form. You can see the browse update even before the child form is closed.

[Tim Kiebert]

Re: Multiple Linking Fields

Chris,
On a general note. I am about to go the other direction from you. Having learned to use dbf tables and Alpha these last 4 years, I am now looking at getting into SQL in order to use it as a backend for Alpha. One thing I have found in my research to date is that the whole concept of foreign key constraints is very different in the two systems. As you have surmised, in Alpha you use a combination of: linking keys in sets and table validation rules for constraints, both for uniqueness and for validation against a second table.

[Stan Mathews]

Re: Multiple Linking Fields

Tim,

I look forward to investigating your example as time permits. Thanks for your efforts to stretch the boundaries.

I really have no need for dual field expression linking values and have never attempted the same other than a simple test to make sure that they work in some practical form. I just like to clear up misconceptions when they arise

[Dan Blank]

Re: Multiple Linking Fields

Ah, the good ole linking multiple fields in a set issue. There is a good workable solution at http://www.learn alpha.com/LinkTwoFi...kTwoFields.htm. I use it all the time, and to date, it has not failed me yet! It does handle two fields, and I have never had the need to link a third field, so sorry don't have an answer for that.

Although I have created lookups to automatically fill in other fields based on the parent. There is an article on my web site at http://www.danblankdatabases.com/PCfields.htm

Have Fun,

[ChristopherG]

Re: Multiple Linking Fields

Hi All,

It seems that I now have quite a few potential solutions!

Thank you for all the input. I am travelling at the mo and will try the suggestions tommorrow and post back.

Cheers
Chris

[ChristopherG]

Re: Multiple Linking Fields

Hi All,

Attached is a database that I am developing as a personal reference. When finished, I hope to have a demo of each of your suggested solutions.

So far I have created a standard set form (f1) that reflects the undesirable effect in which all fields have to be displayed in the child browses for 'new record' creation to work.

The other set form (f2) is modified to use Tims 'form.ViewLinked' method. This is works well, but my ambition is to figure out how to key new records directly into the child browse, without having to display all fields, i.e. the multiple link field values are inherited.

Next I will try Stans Lookup trick and post again later.

Cheers
Chris