Previous record

In article , Skizzo
wrote:

> Hi!
> I'm new here, I'm looking for some help with FileMaker Pro 4 on a Mac....
> I know it's ooooold :-D but I'm into publishing and I need it every now and
> then to manage some data list.
>
> Anyway, this is my situation: I have a file of events with dates, and I
> export them to import into XPress.
> When exporting, data is formatted using some calculated fields
> (e.g.: if field "Tel" isn't empty, calculated field "ExportTel" contains
> string "Tel. " plus "Tel" field)
>
> Problem: During export, I would like to compare the date of the actual
> record with the date of the previous record, to export a string that signs a
> change of date.
> But I don't know how! When defining fields, there's no way (AFAIK) to point
> to a different record, so I can't get the previous record value to compare.
>
> I hope I have been clear... :-?
>
> Thanks in advance,
> Valter

Usually what I do is Export the records from FileMaker and then open
the exported file in Excel (or AppleWorks spreadsheet) to create a new
column that checks the previous row's date and the re-save the text
file. It's much easier and quicker this way for one-off / occassional
needs, but it sounds like you may need to do this regularly so setting
up FileMaker to do it probably makes more sense.

FileMaker can't see data in previous records in the way you need, so to
achieve this within the database you will need to create a Script that
loops through the date-sorted records testing and setting the date
change text. The same Script could then Export the records, so the
whole process could be done in one go.

First perform a Sort and Export manually so that FileMaker knows what
you are wanting, then the Script can store the Sort Order and Export
Order you specify to perform by itself in future. Obviously the records
would need to be sorted by the date field somewhere in the Sort Order,
but can be sorted by other fields above or below this as needed (eg.
you might want sort records so that all the "School" events are
together and all the "Work" events are together, but within these
groupings the records are sorted by the event's date).

You will also need a Global field that can temporarily store the date
to be able to compare it with each record through the loop.
eg.
g_LoopDate Global, Date

Plus of course a new field to store the date changed text.
eg.
DateChanged Text

Then you can create a Script something like:

Enter Browse Mode []
If [Status(CurrentFoundCount) = 0]
Beep
Show Message ["There are no records in the Found Set"]
Exit Script
End If
Sort Records [Restore, No Dialog]
Go To Record [First]
Set Field [DateChanged, "NEW DATE"]
Set Field [g_LoopDate, EventDate]
If [Status(CurrentFoundCount) > 1]
Loop
Go To Record [Next, Exit After Last]
If [EventDate = g_LoopDate]
Set Field [DateChanged, ""]
Else
Set Field [DateChanged, "NEW DATE"]
Set Field [g_LoopDate, EventDate]
End If
End Loop
End If
Export Records [Restore, No Dialog]

The Script first checks to see if there are actually any records in the
current Found Set. If the number of records is 0, then there is nothing
to export and the script exits with an error message.

If there are records in the Found Set, then the Script first Sorts them
(using the same Sort Order as you performed manually). Then it goes to
the first record and sets that record as a "NEW DATE" and stores its
date in the temporary g_LoopDate field to compare with the next record.

Next the Script checks that there is more than 1 record in the Found
Set. If there is only 1, then the loop is skipped.

For more than 1 record, the Script begins the loop. The Loop goes to
the next record and compares that records date (EventDate) to the one
stored in g_LoopDate. If they are the same date the DateChanged field
is set to 'nothing' / empty, otherwise the DateChanged is set to "NEW
DATE" and the date stored in the g_LoopDate field is updated to the new
date. Then the Loop goes around again until all the records in the
Found Set have been tested.

After the loop has finished, or been skipped with only 1 record, the
records are exported (using the same Export Order as you performed
manually). If you want to you can use the option in the Export command
to specify a filename and directory to export to and FileMaker will
automatically use that each time, BUT you will NOT be asked if you want
to overwrite the existing file each time the Script is run. Leaving
that option turned off means FileMaker will ask you each time where to
save the exported file and what to call it.


Now whenever you want the exported list of records, all you need to do
is Find the appropriate records and then run the Script. You should end
up with an exported file that is roughly something like:

NEW DATE 15 June Teddy Bears Picnic
15 June Dentist appointment
15 June 'Lost' new season starts
NEW DATE 21 June School 'Meet Teachers' night
21 June George's birthday
NEW DATE 23 June Visa bill due date
NEW DATE 24 June School holidays start
etc.

You could put a Button on the data entry Layout that performs the
Script, or you can run it from the Script menu and / or set it to use
one of the 10 keyboard shortcuts.

If the Found Set contains LOTS of records (or you're using a slow
computer), then the loop may take a little while to run through and
then export them.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Helpful Harry, il 16-07-2008 3:06 ha scritto:

> Usually what I do is Export the records from FileMaker and then open
> the exported file in Excel (or AppleWorks spreadsheet) to create a new
> column that checks the previous row's date and the re-save the text
> file. It's much easier and quicker this way for one-off / occassional
> needs, but it sounds like you may need to do this regularly so setting
> up FileMaker to do it probably makes more sense.

Wow Harry, you're more than helpful... you're precious!!! :-D

Thank you very much for your accurate annswer. :-)
Valter Prahlad

On Wed, 16 Jul 2008 13:06:59 +1200, Helpful Harry wrote:
> Usually what I do is Export the records from FileMaker and then open
> the exported file in Excel (or AppleWorks spreadsheet) to create a new
> column that checks the previous row's date and the re-save the text
> file. It's much easier and quicker this way for one-off / occassional
> needs, but it sounds like you may need to do this regularly so setting
> up FileMaker to do it probably makes more sense.
>
> FileMaker can't see data in previous records in the way you need, so to
> achieve this within the database you will need to create a Script that
> loops through the date-sorted records testing and setting the date
> change text. The same Script could then Export the records, so the
> whole process could be done in one go.

I frequently have to work with prev and next records. I usually do
create a field "zs" which is my auto-entered serial number. I use a
calculated field zs_1 (I use option+"-" instead of "_") and build a
relationship "prev" from zs_1 to zs and, ir required, the other way
round for next.

For higher fmp versions I do use the autoenter calculated result for
zs_1. For FMP4 it might be useful to use a script such as
/* replace zs by a new serial number */
replace(zs, serial number, update)
replace(zs_1, calculated value "zs - 1")


This script will apply NEW serial numbers to zs, thus retriggering
lookups/relations for the previous record. You may use it in order do
(re-)lookup a field to take the values from the previous record. Thus
you may do a calculated comparsion within the current record and a
looked up value from the previous record.

This did even work within FMP 2.1, AFAIR.
For FMP3/4 you do not need lookups, but may use related data directly.

HTH,
Martin

In article , Martin
Trautmann wrote:

> On Wed, 16 Jul 2008 13:06:59 +1200, Helpful Harry wrote:
> > Usually what I do is Export the records from FileMaker and then open
> > the exported file in Excel (or AppleWorks spreadsheet) to create a new
> > column that checks the previous row's date and the re-save the text
> > file. It's much easier and quicker this way for one-off / occassional
> > needs, but it sounds like you may need to do this regularly so setting
> > up FileMaker to do it probably makes more sense.
> >
> > FileMaker can't see data in previous records in the way you need, so to
> > achieve this within the database you will need to create a Script that
> > loops through the date-sorted records testing and setting the date
> > change text. The same Script could then Export the records, so the
> > whole process could be done in one go.
>
> I frequently have to work with prev and next records. I usually do
> create a field "zs" which is my auto-entered serial number. I use a
> calculated field zs_1 (I use option+"-" instead of "_") and build a
> relationship "prev" from zs_1 to zs and, ir required, the other way
> round for next.

True, this is another approach and was the one I first thought of too,
but it only works if your records are being created in the same order
that you need them to access / export them.

For an "Events" database I was assuming that the event records would be
created basically in random order (as notices came in) and so wouldn't
always be in date-sorted order - the previously created record may not
necessarily be the previous one in date order.




> For higher fmp versions I do use the autoenter calculated result for
> zs_1. For FMP4 it might be useful to use a script such as
> /* replace zs by a new serial number */
> replace(zs, serial number, update)
> replace(zs_1, calculated value "zs - 1")
>
> This script will apply NEW serial numbers to zs, thus retriggering
> lookups/relations for the previous record. You may use it in order do
> (re-)lookup a field to take the values from the previous record. Thus
> you may do a calculated comparsion within the current record and a
> looked up value from the previous record.
>
> This did even work within FMP 2.1, AFAIR.
> For FMP3/4 you do not need lookups, but may use related data directly.

Having a scripted "Sort and the Replace serial numbers" would get
around this creation order problem.

You could also just Import the date sorted records into a new database
with the auto-serial numbers, and then export the file for XPress from
there ... which again could all be scripted to function automatically.

A simple looping Script is probably the easiest approach to understand
and troubleshoot / update though. )


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Thu, 17 Jul 2008 19:20:42 +1200, Helpful Harry wrote:
> True, this is another approach and was the one I first thought of too,
> but it only works if your records are being created in the same order
> that you need them to access / export them.

No, I do a resort and reassing of serial numbers many times.

> A simple looping Script is probably the easiest approach to understand
> and troubleshoot / update though. )

I feel that loops are much slower - but you are right, they may be more
obvious to understand.

- Martin