Cruisers Forum
 


Join CruisersForum Today

Reply
 
Thread Tools Rate Thread Display Modes
Old 18-06-2014, 16:51   #1
cruiser

Join Date: Dec 2012
Location: Seattle
Posts: 1,130
Filemaker Folks

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.
__________________

__________________
Jammer Six is offline   Reply With Quote
Old 18-06-2014, 18:32   #2
Registered User

Join Date: Jun 2012
Posts: 20
Re: Filemaker Folks

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

__________________
Sthompson is offline   Reply With Quote
Old 18-06-2014, 18:46   #3
cruiser

Join Date: Dec 2012
Location: Seattle
Posts: 1,130
Re: Filemaker Folks

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.
__________________
Jammer Six is offline   Reply With Quote
Old 18-06-2014, 18:53   #4
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 6,718
Re: Filemaker Folks

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)"
__________________
StuM is online now   Reply With Quote
Old 18-06-2014, 18:53   #5
Registered User

Join Date: Jun 2012
Posts: 20
Re: Filemaker Folks

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.
__________________
Sthompson is offline   Reply With Quote
Old 18-06-2014, 19:12   #6
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 6,718
Re: Filemaker Folks

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))
__________________
StuM is online now   Reply With Quote
Old 18-06-2014, 23:31   #7
cruiser

Join Date: Dec 2012
Location: Seattle
Posts: 1,130
Re: Filemaker Folks

Quote:
Originally Posted by Sthompson View Post
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.
Attached Thumbnails
Click image for larger version

Name:	FMP.jpg
Views:	81
Size:	316.6 KB
ID:	83408  
__________________
Jammer Six is offline   Reply With Quote
Old 19-06-2014, 01:16   #8
Senior Cruiser
 
StuM's Avatar

Cruisers Forum Supporter

Join Date: Nov 2013
Location: Port Moresby,Papua New Guinea
Boat: FP Belize Maestro 43
Posts: 6,718
Re: Filemaker Folks

Quote:
Originally Posted by Jammer Six View Post
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.
__________________

__________________
StuM is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
free calendars for folks in NZ brian and clare Classifieds Archive 2 26-02-2008 16:23
Greetings Folks kevball Meets & Greets 2 04-02-2008 16:01
I'm New! Hi Folks! GregoryS Meets & Greets 6 19-06-2007 18:21
Hello folks whiskywizard Meets & Greets 8 24-11-2006 10:11
Thought you folks might be interested in this article 29cascadefixer General Sailing Forum 8 26-12-2003 21:13



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -7. The time now is 15:35.


Google+
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Social Knowledge Networks
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.

ShowCase vBulletin Plugins by Drive Thru Online, Inc.