Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2662

Re: Many-to-Many Relations

Show key headers only | View raw

In article <1mr240e.1sqevakrovpqsN%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:
> 
> I seem to have fixed this (to a degree). I use the simple expedient of
> numbers, an expense number field in the Expenses table and an insurance
> action number field in the Insurance Actions table. These are key fields
> and related to each other in the relation between the tables. The
> expense number is a sequence number field. The insurance action number
> is set to the corresponding expense number at the time the insurance
> action is created. The setting of these values is thus handled
> automatically, which means that the user (me) doesn't have to worry
> about this artifice.
> 
> This seems to work when creating new records. Is there some way to tell
> FileMaker to go back and fill in the values of these fields for existing
> records? I don't see it, so I wrote a script to set proper values into
> the expense number field. I ran into a problem with doing that for the
> insurance action numbers. I'll hold off on describing that problem for a
> while until I see if I'm doing unnecessary work.

*BACKUP* *FIRST* 
and be extremely careful. It can be very easy to turn a database to
useless "mush" with a simple mistake when replacing data in fields
across the whole database, especially key field data.

Since the "insurance action number is set to the corresponding expense
number at the time the insurance action is created", you could make the
Insurance Action Number field a Calculation set to equal the Expense
Number, then close out of the Define Fields window. That will cause
FileMaker to calculate the correct value. Then go back into the Define
Fields window and change the Insurance Action Number a normal Number
field again - it will retain the previously calculated result.

Another option is the Replace function (either in a script or manually
via the menu) can replace the contents of a Field using the result of a
Calculation. The Replace only works on the Found Set, so that can be a
way to also minimise potential damage.

A Script is usually the best way when dealing with sequential
numbering. You can export the data (sorted first if necessary), delete
it all, and then re-import it making sure the Import Options are set to
calculated auto-enter data. That ways FileMaker will perform the
sequential data options as it re-imports.

An alternative could be to export the data to a spreadsheet (Excel,
Numbers), if you're more familiar with those, to add / alter the data
and then re-import it back into FileMaker ... sometimes that can be a
lot quicker than fiddling about in FileMaker Pro.

Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-15 14:06 -0700
  Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-16 14:52 +1200
    Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-16 01:26 -0700
      Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-17 15:30 +1200
        Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-27 18:21 -0700
          Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-28 13:45 +1200
            Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-28 00:30 -0700

csiph-web