Storing and Retrieving Portal Row Order

[oilcan]

I have a situation where the order of portal rows on a given parent record is important, but I'm having problems retaining information about their order in related tables. It's complicated to put to words, but let's see if I can explain this as abstractly as possible.

Table A is related many to one to Table B, and Table B is related many to many to table C. Table BC is a join table between B and C.

Table B has a portal of join table BC, where the user can form a list of related items from C. the order of this formed list is important. A calculation in Table A forms a concatenated list of the values in the portal in table B using the List function (with Substitute etc to remove pilcrows), and the sequence of these values should be retained in the order that they appear sequentially in the portal rows.

However, the sequence in the calculation on Table A becomes scrambled. It does not retain the order of portal rows as they exist in Table B, it seems to be acquiring its order by the foreign key in BC pointing to C. Since there are no active sorts either in the RG or in the portal itself I don't precisely understand this behavior; but given that behavior such as it is I've been trying to figure out some way to retain the extant order of portal rows such that this calculation maintains the proper sequence. Whatever is conceived to address this of course needs to be immune to potential deletions and additions of portal rows (which I initially though might be the culprit here, but that turned out to be unrelated). I can't seem to get my head around how to approach this.

Hopefully my explanation makes sense. Has anybody ever come across some similar situation?

[bruceR]

It will probably be much easier to understand and to help if you can provide a copy of the file or simplified example.

[oilcan]

I figured out part of my problem. It was my calculation using List rather than any portal confusion that was generating the error. As such this post probably more appropriately belongs in the calculations forum. and the subject now seems dreadfully off key. but i do want to continue talking about the problem, because I think it's worthwhile to present how I approached this and see if it agrees with other's senses of sound database design. be warned though, incoming wall of text.

I am attaching a small sample file demonstrating what I'm trying to do here. This is to be a part of a database that is used for a biological research and production facility. This particular segment applies to the construction of a 'vector' using 'peptides' inserted into various 'linkage methods'. The vector represents an amino acid sequence aggregate of the amino acids found in 'peptides' and those found in a given 'linkage method'.

the 'linkage methods' are built in a separate layout, and are related many to many to 'linkage elements' which are a series of discreet amino acid sequences relevant to the construction of various 'linkage methods'. in 'linkage methods' there is a portal to a join table between 'linkage methods' and 'linkage elements' called 'linkage construction', allowing a user to construct a 'linkage method' with discreet pieces of amino acid sequences.

'peptides' also being discrete amino acid sequences, does not logically fit with these 'linkage elements' because they are involved in many other aspects of the database at large, and have different fields etc. so what is being done here is:

from 'vector' you select a 'peptide' and a 'linkage method'. in 'linkage elements' there is a special linkage element called 'Peptide' to denote where in the linkage method the peptide goes. A calculation in vector then aggregates all the amino acid sequences of the various linkage elements, with the amino acid sequence of the peptide inserted in at the proper location.

I created a custom function to perform this task. The initial error was I was performing a List () from the 'linkage elements' table, which was causing the scrambling of the order based on the primary key from that table. Now I'm appropriately forming a list of the join table, and this gets my elements in proper order, but the only way I could find to get an aggregate of amino acid sequences instead of an aggregate of key fields was to insert a calculation into the join table which was just pulling the sequence from the 'linkage elements' table, and use that in my custom function instead.

the custom function looks like:

Linkage_Sequence (field ; separator ; peptideflag ; peptidefield)

Let ( [

$sequence_list = List (field);

$no_pilcrows = Substitute ($sequence_list ; ¶ ; separator);

$insert_peptide = Substitute ($no_pilcrows ; peptideflag ; peptidefield);

$final_sequence = TextFormatRemove ($insert_peptide)

];

$final_sequence

)

so this works, I plug in the calculation in the join table into 'field' above instead of the key field, but it was irking me that I had to create calculation fields in the join table to do this. There those values sit, one table over, and I don't know how to get to them without making this extra calculation field. I have been hearing from various places that I should avoid superfluous calculation fields when I can to avoid slowing down the database...but I couldn't think of another way to compose this aggregating calculation.

If you're interested, have a look at this file and see how I did this, and if you have any better ideas about how to approach it.

linkage_sequence.fp7.zip

Edited May 26, 2010 by Guest

[bruceR]

I would not use $variables in the custom function calc. They can be stepped on by scripts. Probably not going to happen; but there just isn't any need for them. And the custom function does not appear to be necessary at all. It's just a standard calculation.

Edited May 26, 2010 by Guest

[bruceR]

Something like this? Step Number allows positive control over the ordering of the items.

linkage_sequence.fp7.zip

[oilcan]

thanks for the advice about $variables. somehow I missed in my learning of variables that they don't require the $ designation in the calculation setting unless you want to pass it back into a script. after reading your post i read up some on it and it's all clearer to me now

as for making a custom function, I use the same formula in more than one field. When I went back to correct the $variables I only had to do it once instead of twice

it is possible I might extend that same formula to other parts of the database as well, so it seemed like a good idea to make it a custom function now, if need be I can 'abstractify' it more later depending on my needs.

The extra field you added in your demo definitely does what I was originally asking for, giving a defined number for its position in the portal row. Good to know about this functionality, though I'm not entirely sure I need it at this point, the creation order nicely reflects the literal order of linkage elements since as far as i know you can't rearrange portal rows, and if you made the wrong ones you would instead delete all the rows up to the offending row and recreate all that came after to maintain your order.

thanks much for your insight! learning more every day...

[bruceR]

"Delete all the rows..." That sounds like a lot of bother. You can just edit them in place, it seems to me; or go ahead with the numbers. There are ways to automate the generations of of numbers, and also way for instance to create a new entry, number it 2.5, and have it drop into place and all the rows auto-renumber themselves to integers in the correct order. And there are also ways to do drag and drop.

[oilcan]

I forgot to explain that a linkage method is going to be maybe 6 linkage elements max. this is why I suggested just deleting the rows and making them in the correct order. really wouldn't be that bad for such a small set of portal rows. but you're probably right, I should give numeration control of these things consideration just to be as precise as possible. thanks for your thoughts.

Edited May 29, 2010 by Guest