Calculation Field To Get Related Field(s)

[Brian C]

I am attempting to find a more elegant way of displaying data of related records by using a calculation field. The purpose of this is to produce a snapshot of a record and all its related data for archival purposes where the format would be a text dump essentially.

Use of calculation field(s) to do this will solve 2 problems:

1) Because the calculation is based on related data, it will not waste space because it will be unstored.

2) It will allow me to build the text dumps without having to rely on additional tables and scripts to transfer and build the data.

Is it possible to use a calculation field to display data from specific related field(s) of a specific related record? The only functions I can find to even attempt this are:

Status(currentportalrow)

GetField(field)

Count(related field)

If anyone has a clue as to how this could be accomplished please respond!

Thanks!

[Noah Miller]

One way to do this would be to define a value list that uses values from the related field, and includes only related values. Then use the ValueListItems function in your calc.

For example, I have a People table, and a related Phone Numbers table. A record in the People table may be related to multiple records in the Phone Numbers table. If I want to dump all the phone numbers for a person, I define a value list ("Person's Phone Numbers") using values from the "Number" field in the Phone Number table, showing only related records starting from the People table. Then in the People table, I define a calculation field with the following calc:

ValueListItems ( Get ( FileName ) ; "Person's Phone Numbers" )

That will work in FM7, not sure about earlier versions.

[Brian C]

Due to the limitations of value lists I find that I am unable to use this idea though it is very clever.

1) Value lists require the data to be indexed and therefore stored. I am attempting to avoid storing this data in the calculation.

2) Return characters used in formatting cause each line to be considered another separate value and actually break up the data in to separate parts.

3) If any of the records are considered by the value list to be a duplicate entry, it is omitted as a result.

In order for me to pull the needed data into the parent database I have to create a complex calculation field to contain the required data. I require a great deal of formatting control over the data since I am combining a large number of fields together and formating it into a fixed length format for later reporting purposes and extraction of data.

I suppose in the end, what I would like to be possible and what is possible do not always mesh.

[Ugo DI LUCA]

Hi,

How many fields from the related file do you like to compile in this single calculated field ?

1. You have other options as the CopyAllRecords.

2. If your field has "

[Brian C]

I am currently using version 6 - I have plans to begin conversion to 7 this April.

The number of fields I am combining depends greatly on the database.

The end result will be a file containing a dump of people and all their related payments which will also have all the amounts belonging to each payment. Outside of this there are also links to other types of data for each person which will also need to be brought in.

The purpose of this is to essentially provide a snapshot of what occurred during a particular payroll period in a global sense. Anything and everthing that changed or did not change needs to be recorded.

This process will essentially allow me to build pdf reports for archival purposes which will become the more modern version of microfiche.

[Brian C]

The number of fields I'm bringing together in the largest database is just around 40 I believe.

Copy All Records script step is not such a bad idea for smaller databases, but one of my databases is over a gigabyte in size.

Due to the size of the output, the destination of the paste command will not be easy automate since FM 6 has a limit on the text fields of 64000 characters max.

Copy All Records, does not make it very easy to determine which fields are which. it just dumps the data. Considering I have about 200 fields in one of my tables for interface reasons, relationships, calculations, or summaries, It does not make it easy to figure out which fields are which just from looking at the dumped data...

I do not prefer to use anything outside of filemaker since it only complicates the solution when i bring it over to other platforms.

It is not a huge deal to me to have to resort to creating processes to build my output in a somewhat more lengthly fashion, I am just looking to see if there is a more efficient way of going about it is all.