data tunneling?

[rikardlassenius]

hi, i'm having a major fm project on my hands (it seems) since i'm quite new to the software. the problem is complex, but i'll try to explain it as clearly as possible. my fm file is such a mess at the moment, that i prefer not to upload it. if needed, i will, of course.

basically my client manufactures and sells design objects: rings, boxes etc.

- there is one table for product types (example "square ring"), which includes general info, prices, designer, etc

- another table is for the single products (ex. "square ring / size 17 / blue", square ring /size 18 / blue" ...)

- there is one table for contacts

- an order form was constructed using a order line items table. you choose the client (adress etc is filled out automatically) and fill out the products that are ordered, line by line in a expanding portal. it then calculates everything needed, taxes etc.

everything was going smoothly until here. then this client realizes that they need to send invoices etc to different countries and want to have different prices for the products in different regions (not just calculated according to currency exchange rates). in the end they also want a system that calculates royalties for the designers, in local currency.

basically, i tried to solve this new problem by having a "prize zone" field in the client table. but what i can't manage it to do is to change the prizes in the line items with a script that says something like "if the prize region of the order is USA, then pick the the US prize from the product table". if i can solve this, i think everything else will follow.

do i need data tunneling or something for this. i've been reading the fm missing manual book but just can't figure it out. any help is appreciated.

[Fenton]

This is not a scripted operation. It may be a Relookup operation only to populate existing data. But, for new entries, both the price zone and the price would come in by Lookup or auto-enter by calculation (different ways to do much the same thing).

You would need, as you say, a price zone field in clients. It would be looked up (or auto-enter by calculation) into both the Invoices, then the Invoice LIne Item; upon entering the Client ID in Invoices, then the Invoice ID into the Invoice Line Items. In other words, bring the price zone into the line item. Disallow invoice creation for clients not yet having a price zone (scripted).

You could then have a Product Prices table between the Invoice Line Items and the Products table. The lookup (or auto-enter by calculation) relationship would be based on ProductID AND PriceZone.

They would need to get serious about entering this data, for each client, and for each product. You could give them a simple FileMaker file to do the Product Prices. You cannot implement this immediately in the existing solution as it will finally be, unless you did so "alongside" of the current direct connection to Products from Invoice Line Items. I think that's how I'd do it; because it's going to need a nice user interface anyway, for editing prices. Or, if they are Excel addicts, do it there. So all you have to do is Import into your final table.

[rikardlassenius]

fenton, thanks a million for an fast and thought-out answer. i've now been fiddling around on my database even more, and feel that i've previously made several improvements. i've totally missed the lookup function – now i have it almost everywhere. there's a few problems left, though.

i've come so far that the price zone is brought into the order form after you choose the client. the prize zone then is imported to the line items, when you start adding products.

but what i can't make it do, is to choose different prices from the product types depending on the price zone. we'd like to keep the prizes in the 'product types' table (one price for europe, one for the us, one for japan etc).

it can look up one of the prices without problem, but when trying to write a simple case-script to make it choose, say the us price if the client is in the states, it doesn't work.

[Fenton]

You say "we'd like to keep the prizes in the 'product types' table (one price for europe, one for the us, one for japan etc)."

Imagine instead a tiny table (with lots of records however), ProductPrices, in between Products Types and OrderLineItems. It would have 1 record for each unique combo of product-zone-price. Fields:

ProductID

Zone

Price

So, rather than separate fields, each price would be in its own record. Then it is a simple matter of a relationship to that table, and a Lookup based on it; no calculation needed.

The only difference to your interface (in Products) is that you'd need to either choose the "zone", then enter the price. Of, if every produce gets prices in most of the zones, just create those records when you click a button for New Product. If one of those created does not need a price for a zone, it doesn't matter, blanks are simply ignored.

But your way is fine and should also work. (I'm a bit of a relational nut -). Lookup also works as a calculation function. In OrderLineItems:

Case (

not IsEmpty ( ProductID );

Case (

Zone = "Europe"; Lookup ( ProductTypes::Price_Europe);

Zone = "US"; Lookup ( ProductTypes::Price_US);

Zone = "Japan"; Lookup ( ProductTypes::Price_Europe)

)

)

[The ProductID bit is to cause the Lookups to trigger.]

BTW, Lookup is useful. Auto-enter by calculation, with [ ] "Replace existing contents" unchecked is much the same.

But they should only be used in cases where you want the data to be fixed into the table. If you go and change the data in the original table they came from, the looked up data in other tables will not change. This is appropriate for prices, which you certainly do not want to change. It may not be so much for names, especially not for phone numbers, likely not for descriptions (which are bulky).