I have an FM 8 database with two tables:

Courses
Books

The course and book information is imported from an excel spreadsheet this
is the result of an export from another system used to manage the bookstore.
Some columns are imported into Courses and some into Books.

Courses is a list of courses being taught at a university. There are
approximately 2,200 courses in this table.

Books are the books being used in each course. There are approximately 4,000
books in this table.

The FM application prints Shelf Labels containing Course information and a
portal containing the books required. There may be as many as 16 books used
for a single course.

Professors sometimes add or change books after our first printing of shelf
labels so I will need to re-print the shelf labels when a new book is added
or the book list changes for a particular course.

I am having trouble figuring out how to find (and print) just the courses in
which the books for that course have changed since the last printing.
"Changed" means books added or deleted.

Any help would be appreciated.

Thanks to the group.

Scott C

In article <BF95657D.C89D%WaitingFor.SCC (AT) GMail (DOT) com>,
WaitingFor.SCC (AT) GMail (DOT) com says...
This is usually handled by creating a field to track whether the record
is 'dirty' (modified) or 'clean' (unmodified). In practice, in the
courses table, you create a field that will contain either 0 or 1 (where
0 = clean, and 1 = dirty)

When you print you find all the dirty records (field value =1), print
them, and then replace on the field to 0 (clean).

"Changes" to the course must be scripted, and the scripts to add or
delete a book all set the dirty field back to 1, ensuring it will get
printed next time you run a print.

To reset the database to print everything; just set all records to
dirty, print, and all records will be printed and marked clean.

Then, you change a few records, they get marked dirty, you print, those
records are found, printed, and marked clean.

etc.

Hope that gives you the general drift...

If he uses a "datetime modified" field in connection with a "datetime
printed" field then this simplifies matters considerably. Data entry can
proceed without the use of scripts. The only time a script is necessary is
when printing records (to update the "datetime printed" entry for the
appropriate records).

Bill

"42" <nospam (AT) nospam (DOT) com> wrote

Thanks for the help ...

I didn't mention in my initial post that I had tried (and failed!) with the
date modified technique.

I created a Date_Modified field in the Courses table. Initially, I filled
this field with 1/1/05 in all records.

I added a record in the Books table.

The new book showed up in the portal of the proper course but the date
modified field did not reflect the change.

I assumed this is because the record in the Course table didn't actually
change. Instead, the contents of the portal in that record changed which I
assumed didn't trigger the Date_Modified field.

Am I looking at this the wrong way?

Thanks again,

Scott C.



On 11/7/05 8:36 PM, in article
MPG.1dd9a1115a45a66e989db1 (AT) shaw....shawcable.net, "42"
<nospam (AT) nospam (DOT) com> wrote:

In article <BF989D27.C9D8%WaitingFor.SCC (AT) GMail (DOT) com>,
WaitingFor.SCC (AT) GMail (DOT) com says...
No, your analysis is dead on. It didn't change the date modified because
the record itself wasn't modified.

I suggested the technique of using a dirty field, with scripts to set
its value because it is simple and it will work. You *can* use a 'date
modified' field as your clean/dirty field, but then you have to keep
track of *when* you last printed so you know which changes are "new
since last print", and you'll still have to script some sort of change
to the parent record in order to update the date modified.

Its up to you. Either will work.

Yes, well I consider the "dirty/clean" approach far more work and prone to
error because you have to track /control edits to the books records manually
whereas FileMaker will automatically do the timestamp modified thing. You
only need to write one script -- print updated courses -- with a simple
replace to update the "last printed" field in the appropriate records. I
almost always include the timestamp created and timestamp modified in tables
of any significance because of the incredible utility they afford. In the
relationships table, you can set up one occurance with a relationship
Book::TimeStampModified > Course::TimeStampPrinted and you instantly have a
list of revisions to a course. For all the current course books, you just
have a "regular" relationship set up. Very lean and clean.

Bill

"42" <nospam (AT) nospam (DOT) com> wrote

Bill,

In article <5M-dna_75Iu7KO7enZ2dnUVZ_tidnZ2d (AT) comcast (DOT) com>, wjm (AT) wjm (DOT) org
says...
Be that as it may, I consider it the better solution because its the one
that works.

If I delete a related book from a course there is no record that the
course was changed using just timestamps.

The objective was to determine which courses had their 'book list'
revised (books added or removed) since the last printing. Clearly
deletions must be tracked. If you want to track book deletions you MUST
track & control edits.

-regards,
Dave

Dave (and group)

New challenge ... it turns out that several data elements can change
triggering the need to print a new Shelf Tag.

For example, not only can a book be added or deleted but Instructors might
change, Course Sections might also change due to administrative changes or
simple data entry errors on the source data. (Remember I am importing data
from a spreadsheet that is exported from another system.)

When I import new records, I need a unique way to know if ANY of the
important data elements have changed since the previous printing session. If
different, flag it to be re-printed.

I think this could be done by creating a field that concatenates data from
both the Course and Book tables into one field and storing that field in a
separate table when the Shelf Tag is printed. For example: Course & Section
& Instructor & all ISBN's.

When new records are imported, I'd run the same calculation on the new data
set for each Shelf Label and use the EXACT function to compare the two
fields. If they were different, I'd set a flag to reprint that label.

So far, I haven't been able to figure out how to concatenate ALL the ISBN's
for a specific course into a single string. Can't do this from the Book
table and haven't been able to figure out how to do from the Course table
yet.

Any ideas?

Thanks in advance ...

Scott

On 11/10/05 4:23 PM, in article
MPG.1ddd5a2f433f81d3989db8 (AT) shaw....shawcable.net, "42"
<nospam (AT) nospam (DOT) com> wrote:

Actually, if your 'edits' are coming in through imports, then this is
really little more than a typical database synchronization problem.

Database synchronization is when you have two identical databases, and
then they get cloned, worked on, and ultimately need to be merged back
together.

In the typical scenario, you need to detect records added, removed, and
modified, and apply those changes to the other database. (If both
databases have been modified the changes need to be propogated in both
directions with conflict resolution, but we can disregard that for now.)

In your case, you need to do a synchronization, and add the trivial step
of flagging which records were affected by the process.

Your strategy of concatenating all the fields (essentially creating a
hash for the record data) and comparing them is not a bad idea.

Pursuing the hash approach, it appears all you need is a way of grabbing
the child records to a relationship in a string - a solution for that is
to define a value list on the relationship, and then using the design
function which returns the contents of that value list as text.

You might also find it simpler to take a more direct approach. Import
the 'new data' into a clone of the schema, and just script a
"comparison" loop that iterates through, marks the records that have
changed, and then apply those changes. (by copying the records over from
the clone to the original, replacing the originals.)

That script could also make a list of any courses that were outright
deleted since the last merge, which is information you'll surely want to
know.

-regards,
dave





In article <BF9A1363.CA80%WaitingFor.SCC (AT) GMail (DOT) com>,
WaitingFor.SCC (AT) GMail (DOT) com says...