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

On Fri, 08 Jul 2005 11:25:34 GMT, Mario Rosario wrote:
>  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.

It's a valid relationship to follow the one-to-many branch. 

Although your layout may show the first one of the related only, the
others are still within the background. If you had used a portal around
the field, you'd see the others, too.

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

User error, although not an obvious behavior.

Workaround: Use a loop around a pasteResult or setField command: This
will edit the visible record only, while walking through your set of
records.

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

> 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
=======

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@gmx.net> wrote in message
news:slrndcstog.kt4.t-use@ID-685.user.individual.de...

>
> >  Anyone know if this is a bug in filemaker pro 7 v3 or user error?
>
> User error, although not an obvious behavior.

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:

>
> Workaround: Use a loop around a pasteResult or setField command: This
> will edit the visible record only, while walking through your set of
> records.

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.

On Fri, 08 Jul 2005 17:15:06 GMT, Mario Rosario wrote:
> > User error, although not an obvious behavior.
> 
>  I restricted the found set to one record, but it's acting on all related
>  records.

Yes - I guess you found 'one' - but this still relates 'to many',
regardless of displaying just one of those many.

>  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:
> 
> >
> > Workaround: Use a loop around a pasteResult or setField command: This
> > will edit the visible record only, while walking through your set of
> > records.
> 
>  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.

I don't understand your example. What's on your layout?

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@gmx.net> wrote in message
news:slrndd0es1.5ca.t-use@ID-685.user.individual.de...
>
<much deleted>
>
> I don't understand your example. What's on your layout?

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


"Mario Rosario" <mario@webacre.com> wrote in message
news:SbpAe.210$Rv7.194@newssvr21.news.prodigy.com...
> 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@gmx.net> wrote in message
> news:slrndd0es1.5ca.t-use@ID-685.user.individual.de...
> >
> <much deleted>
> >
> > I don't understand your example. What's on your layout?
>
>

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@newssvr21.news.prodigy.com>, 
mario@webacre.com says...
> oops sorry:
> UPDATE purchaseorder
> SET poname=name, postreet=street, pocity=city, postate=state, pozip=zip
> WHERE id=@id2
> 
> 
> "Mario Rosario" <mario@webacre.com> wrote in message
> news:SbpAe.210$Rv7.194@newssvr21.news.prodigy.com...
> > 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@gmx.net> wrote in message
> > news:slrndd0es1.5ca.t-use@ID-685.user.individual.de...
> > >
> > <much deleted>
> > >
> > > I don't understand your example. What's on your layout?
> >
> >
> 
> 
>