relationship based on a repeating field

[csbalogh]

Hi,

I have two related databases, DB A and B with the relationship rlA_AND_B. DB A contains a field with 6 repetitions (Field A6), which is related to a Field B in DB B, this latter is not a repeating field. How can I refer in a calculation in DB A to the related data of Field B DB B, which appears in the repetitions 2-6 of Field A6? In other words, What is the substitute for rlA_AND_B::Field B when I mean the related data in DB B of repetitions 2-6 of Field A6?

The problem is quite simple, but I cannot figure out any solution for it.

Thanks.

csb.

[bruceR]

Yup, the problem is simple. Ditch the repeating fields completely.

[QuinTech]

Right on, Bruce. csb, repeating fields have very limited usefulness, and this is definitely not one of their uses. What, exactly, do you seek to do? Maybe we can suggest a better structure for you.

If you absolutely insist on having repeating fields, you could make a calc field (non-repeating) which is equal to ( GetRepetition ( FieldA6 , 2 ) ). Now a relationship from this field to your file B will be keyed off the second repetition in FieldA6.

But you better have a good reason for the repeating field. Start talkin', buster.

Jerry

[csbalogh]

Right!

This is my problem in more detail.

I have a db with journal articles and essays (Articles database). Each title can have more then one author. For the authors I have a repeating field: e.g.

Weide, Jan van der

Seters, John van

Davidson, Peter

All for the title, say, "Burial customs of Ancient Egypt during the 18th Dynasty", from the journal "Journal of Egyptian Archaeology", pp. 155-169.

I have a db which contains detailed background information about these authors (Author database). In this second db. the authors are entered with First Name, Last Name etc. in separate fields.

{Fields: Last Name - First Name - Middle Name - Abbreviated First Name - Abbrev. Middle Name - Last Name Completum}

My intention is to be able to copy n articles (i.e. Author, Title, Source Title, pages etc.) to the clipboard, but also to be able to format the end result with the help of checkboxes, like various formats for the author names:

Seters, John van - Van Seters (Last Name only) - John van Seters (Firs Name First) - J. van Seters (Abbreviated First Name). At the same time I must be able to display 1, 2 or all the authors in the format, e.g.

J. van der Weide, J. van Seters & P. Davidson, TITLE ARTICLE, in: SOURCE TITLE, pages. or

Weide, J. van der et al., Title, ...

I know that I can retrieve the necessary information with the help of calculations from my repeating field (i.e. how to retrieve First Name from the original Last Name, First Name) and with GetRepetition I can access anyone of these names. But with the options I mentioned above (variations of first name, last name and variations of how many authors I wish to include) the resulting calculation is VERY complex, and VERY long, some six A4's in a Word doc.

I thought I should perhaps get the information concerning the first name, abbreviated first name from my Author database. In this case I would avoid at least half of the calculations (namely those which retrieve the First Name and Last Name, not to mention Middle Name and an eventual last name completum like "van der" above).

If there is only one author for one article title that is no problem. But when I want to get all this for a second, third etc. author of the repeating field, I cannot access the related record of the Author database to get my First Name, Last Name etc. It is here I have asked you to help me.

One last note. I have formerly used a portal in my database, instead of the repeating field, which I now want to implement. But there are three reasons I wish to abandon the portal. 1. The number of authors is rather limited (max. 4 authors per title). 2. The portal needs an additional join file for a many-to-many relationship. 3. When I want to copy my records to the clipbord with the formatting mentioned above, it is similarly difficult to get several authors from the portal in one field. There is some possibility to do it with the Troi Text Plug-in, but that also only when I want to display ALL authors and in the form they appear in the portal (Last Name, First Name). If I change the format, like first name first, abbreviated first name, or only want the first two authors to appear (out of four) etc., I similarly get stucked. And 4th. I would rather do my job without plug-ins if I can.

Sorry for being so wordy, I hope it is more clear now. Thanks for any suggestion!

csb

[bruceR]

1. So your portal is short. So what?

2. Build the join file. You'll be much better off in the long run. Every need to find which authors are involved with which publication?

3.a Where is this clipboard data going ultimately?

3.b You do not need to put the data into a single field to copy it. You can copy the data from all fields on a layout with the copy command when no field is selected. This produces tab-delimited text, and you can do further substitution if you need to.

4. You're not doing anything that requires a plugin.

You are really doing things the hard way, unfortunately. It will be a whole lot easier if you ditch the repeats.

SEE ATTACHMENT

Articles.zip

[csbalogh]

Thanks a lot Bruce!

Indeed, your solution seems to be more attractive than the one I have been thinking of. Though I need to look more closely at how your db in the attachment is functioning, and how I can implement it into my own database.

The copy to clipboard function I mentioned I need to be able to paste e.g. several selected (and preformated) records in a Word doc, e.g. in a bibliography.

Much appreciated for your help.

csb

p.s. The plug-in I mentioned can sum up texts from the records of a portal as a calculation, without needing to use a script like your BuildList and SetField for the Output. As far as I know, there is no such calc. function in FM, and the only other way to do this is indeed by scripting.

[bruceR]

That could be done as a custom function in FM7.

[csbalogh]

Can you please tell me which that custom function is in FM7?

thnx

[ESpringer]

Not an answer to how to make a custom function, csb, but a warning about FM7 for biblio cites: formatting won't paste to/from Word anymore. I have a complex biblio db with citation calc fields in FM7, and am struggling, because the cite calcs look great *within* FM7 (you can get a text calc result to apply italics to selected bits, for example), but FM7's formatting doesn't seem to have any way of cooperating with *anyone* else... perhaps through export functions some say, but I haven't had luck there yet. So, I'm pasting into email, Word, etc., and having to select major titles and apply italics manually! There's got to be a better way! (On the other hand, the relational joins are so much more elegant!!)

OK, while I'm thinking of your task:

Do set up serial numbers for your article Titles and unique ID#s for your authors (very important given multiple Jane Smiths) and a join file or join table (FM7) to capture who wrote what (which won't need to be in the way, since your portal to the join file pulls data from the authors list for drop-downs, if that's your data entry method)... Call this the "WhoWhat" file or whatever. Note your join file/table allows neat things like tagging as translator, tagging as editor, etc., no more generating new repetitions the first time something has two editors, say... there are multiple roles people can play in any biblio item, and join file is a good place to keep track.

Now: Play with calcs that manipulate the relational value lists generated off Title-WhoWhat relation. The relation will facilitate value lists which can be "sucked into" a calc and will look like a return-delimited text field. Generate your various variations on the name string with calcs over in the WhoWhat db -- though "authors" would seem like a logical place, it won't "port" over in FM6 without replication of fields, so might as well put it in join file. (Sounds like you were trying to make the calc work on whole strings of authors' names right there in the titles file, hence the pages' worth of calc?) Then, tap the relation from Titles to WhoWhat with multiple value-lists on that relation (one list of authors in FN LN format, another list in LN, FN (1954-) format, etc.) You can then (from the Titles file/table) manipulate the value-lists with substitution function to turn carriage return into commas, semi-colons, whatever, and can use calc to to include "and" at the right spots, etc.

I'll be curious to hear how it works for you -- sorry if any of this is unclear. Doing citations has been quite an adventure for me! (read: headache with occasional rewards) Good luck.

[bruceR]

Custom functions are just that - custom functions. That is, you write them yourself. But FM7 custom functions can do recursive processing, like you need.

[The Shadow]

From another thread:

A custom function is a named calculation that is not connected to a field, and it uses parameters rather than fields for its inputs, and thus it can be easily reused in other circumstances.

http://www.fmforums.com/threads/showflat.php/Cat/0/Number/104358/page/0/view/collapsed/sb/5/o/all/fpart/1

The thread also contains a simple example file that contains a custom function, so you can see what you're missing if you only have FM7.

[csbalogh]

There might be numerous reasons to step over to FM 7 (Dev) but that is not my immediate concern now. Though the custom functions you guys mentioned is quite an attractive one (like the many other new features and functions of FM 7).

Thanks for your posting, ESpringer, indeed, most of what you suggested I have already implemented in my db. The valuelist idea was particularly useful. But I still remain with one big problem. The value list which will display my related records (i.e. one or more authors related to one article) can only be based on a normal field, and not a calculation field. Though I would like to get a preformatted record in my value list (i.e. a calculation field that gives either First Name Last Name, Last Name, First Name, Abrev. First name, Last Name, or Last Name only). Otherwise, if I will try to retreave this information only later from the valuelist items with the help of the functions Left, Right, Middle, Position etc. my database will be very slow (note that there can be up to 4 authors for one title). Do I have another choice? How can I base my value list containing the authors on a field that can at least have four differeny forms (LN; FN LN; LN, FN; F. LN)?

csaba

[bruceR]

What's wrong the solution you've already got - scripted results from a proper relational structure?

Also- the value list approach will put authors in sorted order by the full formatted author name, maybe this meets your requirments but I suspect it doesn't. So you'd get A. Smith; Adams, J.K; B. Adams; Bliss, Henry.

[csbalogh]

You're right, Bruce. I forgot that the valulist will always appear in alphabetical order, even if I wanted a different sorting order.

There is nothing wrong with the enscripted solution you proposed. It is only that I prefer calculations above scripts. I even thought perhaps the calculation fields worked faster than the scripting method, esp. when I wished to parse through hundreds of records in order to copy them to the clipboard. But after I experimented with both solutions (calculation and script) I was surprised to realise that calculations (esp. text-extraction-functions like Position, Middle, Right etc., and esp. in calculations based upon calculations) are very slow, indeed, scripting is some 10 times faster - in my case at least.

So, it seems you were right that this was the best solution in my case.

thanks for your suggestions.

csb

PS> Does anyone know why the total amount of data I can copy to my clipboard is only about 24 kb? (My P4 laptop has 256 MB RAM and 30 GB hd)