Hi all,

Anyone have this similar experience?

I have customer address table related to customer name related to purchase
order table, where the purchase order table holds the shipping address. For
convenience, I wrote a script to automatically fill out the address in a
purchase order.

table & relationship:
address ---- customer name-----purchase order
many ---to-------one-------to-----many

To accomplish this, the script does a Replace Field Content on the purchase
order records found set:
Replace Field Contents [No Dialog; purchaseorder::street;
address::street]
etc - many more field replacements

What I am seeing is that it doesn't matter if I have one purchase order
record or many in the found set, Replace Field Content does a field
replacement on all records belonging to the same customer regardless of how
many records I have in a found set. What I intend to do is to fill out just
one order.

Anyone know if this is a bug in filemaker pro 7 v3 or user error?

Thanks in advance,
Mario

Mario Rosario wrote on 7/8/2005 - 12:25:34 PM


I an still trying to fully understand the new FMO7 relationship model
but I think the situation here is a context problem.

If the script is performed from the context of the address table then
the relationship will pick the related customer and then all related
purchase orders for that customer.

If the script is written from the context of the customer table then
you will have a similar result except that the relationship does not
define a unique address so I guess the first related customer address
will be used.

If the script is in the context of the purchase order table then the
relationship will again choose the first related customer address via
the indirect relationship from purchase order to address but at least
you will only get the records in the found set of the purchase order
table being replaced.

In other words, and I can easily be wrong, I don't think that script
will work to your specification whatever you do if your relationships
are genuinely many-one-many.

I assume you are trying to do this for one address into a found set of
purchase records. Perhaps you could set up a collection of global
fields - pickup the address from the address table. Then go to the
purchase order table and perform a Replace Field Contents using the
Globals.

--
Dan Fretwell
Using FMP7.03 on WinXP SP2

"Martin Trautmann" <t-use (AT) gmx (DOT) net> wrote


I restricted the found set to one record, but it's acting on all related
records.

According to the manual, the replacement is supposed to act on the found
set, I found the behaviour of Filemaker Pro very difficult to understant. I
tried to use SetField instead as a work around but could not make it work
either. Let me explain below:

It's simpler than that, it's a simple task I want to do, I have two tables
with records:

Purchase order records with primary key 1 and 2.
Address records with primary key a and b.

I want the user to be able to choose which address to copy to a purchase
order, there are 4 possibility; 1-a, 1-b, 2-a, 2-b. What the script needs to
do is to point to the appropriate records and copy the fields. But I can
never get it right.

My layout is a window which displays a purchase order, the user clicks on a
button, it brings up another window with a list of addresses belonging to
the customer. The user clicks on an address and the information is copied
into the purchase order.

What I need to do a very simple task (at least in any other database). For
example:
--------------------sql
script----------------------------------------------------------
set id1 = 4; // user gets to choose this record using a mouse click
set id2 = 23; // user gets to choose this record using a mouse click

SELECT name,street,city,state,zip FROM address WHERE id = @id1;

UPDATE purchaseorder
SET poname=name, postreet=street, pocity=city, postate=state, pozip=zip
WHERE id1=@id2
----------------------------------------------------------------------------
-----------


"Martin Trautmann" <t-use (AT) gmx (DOT) net> wrote

oops sorry:
UPDATE purchaseorder
SET poname=name, postreet=street, pocity=city, postate=state, pozip=zip
WHERE id=@id2


"Mario Rosario" <mario (AT) webacre (DOT) com> wrote

Hmmm.

ok... so ...

id1 is the customeraddress-id ? (the id address record that has been
selected?)

id2 is the purchaseorder-id ? (ie the id of the currently displayed
purchase order record that you want to copy the address to?)

In FM, you don't actually need an 'id2' because you'll be running the
script from the 'context' of the purchase order you want updated.

id1 should be defined as a global.

Then:

define a relationship between id1 and address::id. Call it
'selectedAddress'

That relationship gives us access to the results of your select
statement.

then write a script (which would be run from purchase orders with the
record you want updated as the 'current record'.

setfield( postreet, selectedAddress::street)
setfield( pocity, selectedAddress::city)
setfield( postate, selectedAddress::state)
setfield( pozip, selectedAddress::zip)

That covers your update statement.

So the user clicks a button to set id1 (select the address) - perhaps
via a drop down list?

And then pushes a button to run the script to copy the values over.


In article <9lpAe.214$Rv7.155 (AT) newssvr21 (DOT) news.prodigy.com>,
mario (AT) webacre (DOT) com says...