Filemaker Folks

[Jammer Six]

I'm developing a database for my own use, on a Mac, in Filemaker Pro.

I've tried explaining the difference between a chart correction and a discrepancy to Filemaker people, and got exactly nowhere.

So if there are folks who use Filemaker here, I figure I'll have an easier time.

Surely I can't be the only cruiser who uses Filemaker or Macs.

[Sthompson]

Telling us what exactly you're trying to do with FM would be a start!

[Jammer Six]

I didn't want to get all involved in trying to spell it out unless there were others here...

Briefly, I've created a database that is designed to be a single entry point for charts, corrections and lights.

That is, by entering the boundaries of a given chart, the database will know which light and which corrections are on that chart.

In that way, entering a correction once will tell you which charts it applies to.

I'm having logic problems with types of corrections.

Entering a correction (or light) works fine-- the database figures out which charts the corrections are on and generates a list for each chart.

Entering a new chart also works, it goes and gets the right corrections for that chart.

The problem is discrepancies, which I had figured were just another type of correction, but they're not. They're temporary, and they're persistent.

That is, you enter a discrepancy on a chart, and you're not done.

In the case of a chart correction, if you enter a correction on a chart, you're finished with that correction. If you then replace that chart with a newer version of the same chart, the new publication date of the new chart will overrule any correction that falls before that date. (As it should. The new chart presumably includes all corrections up to the noted date on the new chart.)

However, in the case of a discrepancy, even though the date of the discrepancy is before the date of the chart, the discrepancy is still in force until it has been corrected-- a new chart doesn't correct discrepancies.

So the short version is that discrepancies need to be treated as a special sub-category of chart corrections, and I'm having trouble figuring out what method would be best to implement that in Filemaker.

I tried explaining it to Filemaker people, and all I did was confuse them. So now I'm hoping there's a Filemaker person here who understands correcting charts-- surely I can't be the only one.

[StuM]

That's not a Filemaker problem, that's a database design problem which will be the same no matter what DBMS or OS you are using.

Off the top of my head - one simple way to do it would be:

A boolean or whatever field that flags specific "Corrections" as "IsDiscrepancy". Then for each chart, you "Select All Corrections where (CorrectionDate > ChartDate OR IsDiscrepancy = True)"

[Sthompson]

Jammer,

What fields and calcs are you using?

Seems like you could create a category field (correction, discrepancy) and condition your calcs on the selection.

Another method, depending on your schematic, is to use conditional formatting.

Just a couple guesses, because you'll still only skimmed over your DB solution.

[StuM]

Further to my last. If you want to still keep the discrepancy data after it has been rectified (rather than using the word "corrected" in two different ways ), a second flag "IsRectified" would do this.

"Select All Corrections where (CorrectionDate > ChartDate OR (IsDiscrepancy = True and ISRectified = False))

[Jammer Six]

Originally Posted by Sthompson

What fields and calcs are you using?

A brief view attached.

My main question is how to implement the difference between a Correction and a Discrepancy. I have a successful script that runs using the On Commit event, for Lights, Corrections and Charts. Anytime one of those is committed, the appropriate script runs.

It was an extremely simple script, all it did was remove any existing join records then re-populate the join table with records that matched the new record, checking to see what chart the new record belongs on.

Identifying Discrepancies is easy, just a new field. A Radio Button. But making Discrepancies persistent is going to be a pain in the ass-- they have to be linked to every applicable chart regardless of date, until the user says they're not valid anymore.

So Corrections are valid until checked off (happens in the join table, that's easy, too) but Discrepancies aren't valid until they're checked off in the Discrepancy table. And once they're removed by the user, another checklist needs to be created in the join table to remove the notations from the charts.

So I'm thinking that I have at least one too many tables there, but I've been thinking about it too much and can't see it.

[StuM]

Originally Posted by Jammer Six

A brief view attached.

My main question is how to implement the difference between a Correction and a Discrepancy. I have a successful script that runs using the On Commit event, for Lights, Corrections and Charts. Anytime one of those is committed, the appropriate script runs.

It was an extremely simple script, all it did was remove any existing join records then re-populate the join table with records that matched the new record, checking to see what chart the new record belongs on.

Identifying Discrepancies is easy, just a new field. A Radio Button. But making Discrepancies persistent is going to be a pain in the ass-- they have to be linked to every applicable chart regardless of date, until the user says they're not valid anymore.

So Corrections are valid until checked off (happens in the join table, that's easy, too) but Discrepancies aren't valid until they're checked off in the Discrepancy table. And once they're removed by the user, another checklist needs to be created in the join table to remove the notations from the charts.

So I'm thinking that I have at least one too many tables there, but I've been thinking about it too much and can't see it.

You're making your link tables too complex. They only need to contain the Keys to the Chart and the Correction/Light.

You don't need to delete all links in the link table every time. Just delete and rebuild the relevant links i.e if a chart has been updated, delete all links for that chart and rebuild them with something like the pseudo-SQL I showed above. Similarly when a correction or light record is modified.