I need help with a strategy. I'm converting a ski trip pricing program
from PHP/web to FM. Problem is, the old program did the complex pricing
in PHP code, and I need to figure out the best strategy to do it in FM.

The schema is like:

resorts
+ resort_pricing (by date, number of trip days; adult, student, &
child pricing)
+ properties (lodging)
+ property_pricing (by date)
+ ski_shops
+ ski_pricing (by date; ski, board, blade pricing)

profit (by nights & days; adult, student, & child, with and without
rentals)


The contextual starting point is after the user chooses a Resort,
starting date, and number of days and nights in the trip. I need to
loop through all valid combinations of property & ski shop for the
selected resort, and display in a portal prices for student, adult,
child, student non-rental, adult non-rental, child non-rental, and
nonskier. There are also a couple of other factors, but I'll save those
for another question.

If I could just use SQL to query the FM database, it would be easy to
accomplish. Since I'm accustomed to working with SQL databases and
programming, perhaps this is easy and I just can't see how to get it
done. I have FM Developer, so I can write C code to do the pricing if I
need to (and if it can be done that way!)

TIA,
--doug

dkirk <dbkirk (AT) gmail (DOT) com> wrote:

You'll need a bit of a refurbishing to get your head around the
Filemaker way to do things. While it may, at first, seem backassword and
downright stupid, it does work. With your background, you have a lot to
unlearn...or at least hold in abeyance while you get into your Filemaker
Head. Neither is better or worse, in actuality, they are just
different. Really really different.

Although you can indeed do all you need to inside FM, you do NOT do it
with queries or loops.

Think of relationships on the Relationship graph AS queries, and you'll
be closer to what you need. However, portals display dynamic data based
on the relationship and are not the static presentation of queries.

I'm going to assume you have a base parent table called something like
Trips or Reservations or something. Whatever it is you're basing your
resort/price selection activity on.

To display related Resort data, I would create a reference table that
contains Resort Name (and/or Resort ID), and the appropriate pricing
records, with fields for Type (Student, Adult, Etc) , Effective Date,
and End Date, with price for each.

Then when a resort is selected by the user and the start/end dates in
the parent table, the records will appear in a portal based on a
relationship between Resort and date fields in the parent table and the
Resort Name/ID and the date fields in the reference table.

With FM 7, you can use dates and greater than/less than operators to
create multi-predicate relationships, so your start and end dates will
also pull in the correct records.

To get the chosen data into the parent records, you can use scripts that
sets data from the selected portal row into fields in the parent record.

Another table (or the same table, depending) can show the property & ski
shop info for the chosen resort.

(this is a very brief and incomplete explanation, of course we use all
sorts of IDs behind the scenes to pipe data hither and yon)

If you need to select several criteria in order to see the appropriage
pricing, you can do this with several portals, you can do it with
cascading value lists (where one choice restricts the choices in the
following value lists, such as choosing Food, then Fruit, then
Strawberry), or you can do it other ways.

BTW, this is NOT what I would consider a beginner-level FM developer
task. Of course you're not a beginner at other kinds of databases, but
as noted, FM is Different. Multi-predicate date range relationships are
complex and can be difficult to set up.

Might be worth your time to hunt up some FM training wherever you are
based...a couple of days spent now might save you weeks of struggle and
thousands of dollars worth of time. Or search out a FM developer near
you and hire him or her for a day to help you set up a logical
file/table/entity schema and point you in the right direction.

Lynn Allen
--
Allen & Allen Semiotics www.semiotics.com
FSA Associate Filemaker Design & Consulting

Lynn allen wrote:

Thanks Lynn for the information. I've put a PDF of the relationship
graph at <http://dkirk.com/tmp/schema.pdf>. I'm not a complete newbie
with regards to FM, but I'm pretty basic, and it's been about a year
since I read the Kubica book [which doesn't cover 7 anyway ] I'm
hoping you'll see some immediate places where I can make changes which
will make it easier to get this part going. Right now it's based upon
the MySQL schema which is, as you'd expect, wholly different than the
FM way of doing things.

Thanks again!

dkirk <dbkirk (AT) gmail (DOT) com> wrote:

Took a look. Good so far as it goes, but it looks like you've got
primarily "functional" relationships on it. Relationships with creation
& deletion rights, in other words.

I don't see any interface or filtering relationships (though functional
relationships can also serve in some cases for interface) which serve to
present the user with data in ways they can handle, except for those
secondary & tertiary contact TOs (table occurances).

For instance, if you create a global field in Properties and relate it
to Property Name in another TO of Property Pricing, when a user on the
Contracts Screen selects the property name they want in that global
(from a pulldown of all property names), they can see the relevant
property pricing records in a portal or a related value list based on
the global::name relationship. Then selecting a line in the portal can
transfer the appropriate Pricing ID to the Contracts record.

There are a lot of more subtle issues with your graph, ones that are
under active discussion among developers concerned with creating new
"best practices" for organizing and utilitizing FM 7 Relationship
graphs. Nothing that's going to impede proper functioning of your
solution, but issues that may impact your future development and the
growing complexity that happens in any successful FM solution.

Sorry I can't go into more detail at the moment. Got to go earn a
living.

Lynn Allen
--
Allen & Allen Semiotics www.semiotics.com
FSA Associate Filemaker Design & Consulting

Thanks. That comment about the global field and the relation has
sparked some memory about how to do things like this in FM. I keep
forgetting that the relationships are essentially projections of the
core database -- that's so not "normal" in the relational world.