Copy data from table through a relationship - set field

#1

Prelim
As many know, in a business one has Quotes and Orders. Quotes might
become Orders but not necessarily. Quotes have many of the same data
fields as Orders, however, creating Quotes on the Orders Table is
problematic. Depending how much quoting you do you might find your
Order database over populated by quotes, dead or otherwise. When you
use an auto-entered serial or numbering system then you end up with
many redundant Order/Quote numbers.

Consequently I am trying to configure my database such that I can
move
Quote data from a Quote Table to an Order Table at that point when a
Quote becomes an Order.

My thought, through a button on Quote Form, is:

Open New Window
Create New Record: ORDER, layout Order Form
Set Field - target (ORDERS field name), calc (QUOTE field name).
.... and Set Field for all the field data to transfer.

It does not work.
I am not sure whether the New Record is referencing the current QUOTE
record.

My relationship tables look something like this:

CONTACTS_kp_ContactID - ORDERS_kf_ContactID
ORDERS_kp_OrderID - LINES_kf_OrdersID
PRODUCT_kp_ProductID - LINES_kf_ProductID

and

CONTACTS_kp_ContactID - QUOTE_kf_ContactID
QUOTE_kp_QuoteID - LINES_QUOTE_kf_QuoteID
PRODUCT_kp_ProductID - LINES_QUOTE_kf_ProductID

Since there is a common denominator in both - CONTACT_kp_ContactID,
is
this possible?

Thanks
Matthew

#2

On Jan 24, 12:17*am, Martin Ôrautmann <t-use... (AT) gmx (DOT) net> wrote:

Quote:

On Mon, 23 Jan 2012 22:54:50 -0800 (PST), Buckbuck wrote:
* Prelim
*As many know, in a business one has Quotes and Orders. Quotes might
*become Orders but not necessarily. Quotes have many of the same data
*fields as Orders, however, creating Quotes on the Orders Table is
*problematic. Depending how much quoting you do you might find your
*Order database over populated by quotes, dead or otherwise. When you
*use an auto-entered serial or numbering system then you end up with
*many redundant Order/Quote numbers.

*Consequently I am trying to configure my database such that I can
*move
*Quote data from a Quote Table to an Order Table at that point when a
*Quote becomes an Order.

I'd keep both together, just separated by a checkbox whether it's a
quote or an order.

Thus all you have to do is to duplicate a record and make it an order by
changing the checkbox.

*My thought, through a button on Quote Form, is:

*Open New Window
*Create New Record: ORDER, layout Order Form
*Set Field - target (ORDERS field name), calc (QUOTE field name).
*... and Set Field for all the field data to transfer.

*It does not work.
*I am not sure whether the New Record is referencing the current QUOTE
*record.

Obviously it doesn't. You do have a relation from the quote id to the
order id? Then copy the quote id to the order id first.

Then you may walk through your fields and do a set field (from quote-id
field).

- Martin

What relation? Through CONTACTS? Are you suggesting

QUOTE_kp Quote ID = ORDER_kf_OrderID ?

#3

On Jan 24, 7:28*am, Martin Ôrautmann <t-use... (AT) gmx (DOT) net> wrote:

Quote:

On Tue, 24 Jan 2012 07:10:28 -0800 (PST), Buckbuck wrote:
Obviously it doesn't. You do have a relation from the quote id to the
order id? Then copy the quote id to the order id first.

Then you may walk through your fields and do a set field (from quote-id
field).

- Martin

*What relation? Through CONTACTS? Are you suggesting

*QUOTE_kp Quote ID = ORDER_kf_OrderID ?

Eactly - that's the way to copy anything from the quote table to to
order table.

If you do want to copy from the contacts table, you may use the contacts
relations.

- Martin

Thanks for your feedback.
So far so good. One part of the undertaking I am having difficulty
with concerns the Lines portal. When I Set Field, Lines_Order, Sku;
Lines Proforma, Sku , only the first record is copied. I kind of
understand why but do not understand a work around. Can you give
advise me?

Matthew

#4

On Jan 25, 2:26*pm, Martin Ôrautmann <t-use... (AT) gmx (DOT) net> wrote:

Quote:

On Wed, 25 Jan 2012 14:23:18 -0800 (PST), Buckbuck wrote:
*So far so good. One part of the undertaking I am having difficulty
*with concerns the Lines portal. When I Set Field, Lines_Order, Sku;
*Lines Proforma, Sku , only the first record is copied. I kind of
*understand why but do not understand a work around. Can you give
*advise me?

Set Field does work on the current record only - which would be your
first, usually.

Choices:
- use "Replace Field Contents" which does work on all active (found)
* records

- use a loop construct
* *goto first record
* *loop
* * *set field
* * *goto next record (exit on last)
* *end loop

Martin

When you say 'go to first record' how do I indicate to the script
where the first record starts, ie; it is the first record on the lines
portal on Proforma, PF12-00001? My script button is on my Order form
(ORDER).

#5

On Jan 25, 3:05*pm, Martin Ôrautmann <t-use... (AT) gmx (DOT) net> wrote:

Quote:

On Wed, 25 Jan 2012 14:50:08 -0800 (PST), Buckbuck wrote:
*When you say 'go to first record' how do I indicate to the script
*where the first record starts, ie; it is the first record on the lines
*portal on Proforma, PF12-00001? My script button is on my Order form
*(ORDER).

do you talk filemaker script or do you talk about something very
different?

The first record is the first one in a found set of records, in all
records when no subset is selected, after a sort or the first unsorted.

Yes a filemaker script. Many of the steps look something like the
following:

Set Field [ORDERS:: field name; PROFORMA; field name]

I think you are suggesting inserting the step:

Go to Record/Request/Page []

I think I need to make the script understand which record is first.
No? I could do it by calculation or is it inferred through the
relationship, PROFORMA:: kp_Proforma ID = LINES PROFORMA ::
kf_Proforma ID

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Copy data from table through a relationship - set field

comp.databases.filemaker comp.databases.filemaker

Copy data from table through a relationship - set field - 01-24-2012 , 12:54 AM

Re: Copy data from table through a relationship - set field - 01-24-2012 , 09:10 AM

Re: Copy data from table through a relationship - set field - 01-25-2012 , 04:23 PM

Re: Copy data from table through a relationship - set field - 01-25-2012 , 04:50 PM

Re: Copy data from table through a relationship - set field - 01-25-2012 , 05:24 PM