Relationship script - setting key field

[Anuviel]

Ok I have a LineItem table for Order system, posted about it before, different question tho, I need a help with a script that will set a f_key field form me in Invoices table.

To see the other post (it might help to show what the script needs to do)(http://www.fmforums.com/forum/showtopic.php?tid/194255/)

Fields Are like.

PO_Number (autoenter serial)

Vendor_Code (autoenter serial)

f_key (Po_Number & Vendor_Code)

Item

Cost

and so on

So records in LineItems will look like this

PO 001

Vendor V1

f_key 001V1

Item1

25

PO 001

Vendor V2

f_key 001V2

PO 001

Vendor V1

f_key 001V1

Item5

500

I need a script that will go into LineItems and pickup f_key and create a record in Invoices table and set f_key.

The problem with the script I made is that if someone adds a record later on after the script was run first time - it will not pick it up. I believe it is fairly simple but it eludes me.

Here is the script:

[Søren Dyhr]

Ehm! Why isn't GTRR(SO) used here, why involve $variables as well as jumping in and out of findmode??

http://www.filemaker.com/help/Script-Steps19.html

Then am I confused by your use of concatenated fieldvalues - what are they doing .... is it for the matter you needed help to here!

http://www.fmforums.com/forum/showtopic.php?tid/194255/fromsearch/1/tp/0/all/1/

Hardly an approach I would support!

--sd

[Anuviel]

Kind of. Basically my Order system is one to many. One PO and many LineItems. So in one order you will find many different vendors and items can be ordered more then once. When ordering is done, there is a print button which will sort LineItems with current PO number and separate them by Vendors, break before each Vendor occurrence and print them out. That is what I want in Browse mode - a table that will sort them by Vendor. I cannot use a self-join as it results in duplicate records henceforth I made a new table called Invoices and a script which will relate LineItems to Invoice table. My concatenated field creates a unique ID which will serve as a f key between LineItems and Invoices. It does VendorID & PO number so when it is set in invoice table it nicely completes the relationship cycle.

I can explain it again but it will not be clear I believe. Here is the file. Please see Purchases layout. After seeing it click on Create Invoices button and see what it does and how it will make Invoice table relate to Purchase LineItems table.

Password and username for the file: Forum Forum

OnLine.zip

[Søren Dyhr]

That is what I want in Browse mode

Then use Ugo's method instead, but why is there an urge to keep things in browse-mode, why not distinguish between nice to know and need to know ...aren't you bloating your layouts to the bursting point with matters which needs to be rendered. Perhaps you should need read this:

http://www.smallco.net/RestrainYourself.pdf

...and in particular the Microsoft joke!

--sd

BTW - couldn't you supply a password to your template?

[Anuviel]

I did in the same post I attached the file to:

Password and username for the file: Forum Forum

The file has only the things that were requested by the future users (my boss) to point out - I am self thought and have had no training in FM whatsoever so even if saying so myself I think the file is pretty ok - have to have it done in two weeks.

[Søren Dyhr]

Sorry!

--sd

[Anuviel]

Np, so UGO's method - ok will try to analyze it and apply it to my file. I am kind of good at reverse-engineering....

Thanks.,

[Søren Dyhr]

Perhaps you can see what your graph does, but to does it looks very similar to the above.

(for those a little rusty on danish, is it an invention to "blow" out a burning candle, without getting out of bed)

Make one TOG for each layout, there's no penalty for cutting it up in digestible chunks!

--sd

[Anuviel]

Well, I've searched the forums and found a method (I might have found wrong one, not sure) what that does when I implement it (might be implementing it wrong to) is this:

Order entry form, one record (creates line items)

PO 001

Item 01

Vendor 1

Cost 1

Item 02

Vendor 2

Cost 2

Item 03

Vendor 1

Cost 2

The method sorts the above as 3 records (self join UGO's method I believe*)

PO 001

Item 01

Vendor 1

Cost 1

PO 001

Item 02

Vendor 2

Cost 2

PO 001

Item 03

Vendor 1

Cost 2

I need it to sort it as 2 records, like this (notice that item 01 and 03 have same vendor)

PO 001

Item 01

Vendor 1

Cost 1

Item 03

Cost 2

PO 001

Item 02

Vendor 2

Cost 2

Still working on it...

[Søren Dyhr]

Take a look at this:

http://www.kevinfrank.com/download/repeating-lookups.zip

--sd

[Søren Dyhr]

I need a script that will go into LineItems and pickup f_key and create a record in Invoices table and set f_key.

I suppose you talk about the two line-items tables. Why not relate the two tables, and set the new relation to "...allow creation" ... there is actually a problem here why are there two tables in the first place, they seem pretty similar ... I would merge them into one and the break their differences out in a separate table.

Similar are there also very few attributal, differences between invoices over purchases and Sales Orders.

Then for upkeep of sanity use one single TOG for each layout instead of the unbroken interconnection of everything moving in your solution.

By and large do I think the lengthiness of your scripts is caused by an inadequate structure - if only we were able to digest it, as well as the key concatenation for me seems pointless, since both values should be available from both sides of the joins, enough to sort by at least.

--sd

[Anuviel]

Yes, they are similar. Actually my PO table is better called Order table or order form. the whole thing is designed for the user and to ease, speed up their work. As I said on order form just the items are entered regardless of vendors and such. After the orders are entered they are separated by vendor and printed out. The problem is when a user wants to see an order for a particular vendor. I could make one lookup table and have them enter date and vendor but they want to be able to browse the orders. I guess what I am looking for is many ot one relationship. Where many LineItems records are related to one record in Invoice table by Vendor and date and PO number. I checked that link but am still not getting. I guess my skill level is way low... I do appreciate all the help and will not give up until I do get it.

[Søren Dyhr]

The problem is when a user wants to see an order for a particular vendor

And having to syncronize to similar tables are definatly not going to make it - you're making provisions for Boyce and Codd to rotate endless in their graves!

http://en.wikipedia.org/wiki/Raymond_F._Boyce

http://en.wikipedia.org/wiki/Edgar_F._Codd

You have to study this template:

http://www.kevinfrank.com/download/multi-gtrr.zip

...while bearing in mind that it's made for fm5 which:

While you can do a GTRR ("Go To Related Record") on a single record, and view related entries that are one relationship step away, versions of FileMaker Pro prior to FM8 do not offer an obvious way to do either of the following: a) View related entries that are more than one relationship step away (e.g., for a given customer, locate all items they have purchased), or : Start from a found set rather than a single record (e.g., in a found set of all September 2000 invoices, go to the found set of all related customers). Using multiline-key GTRRs, you can easily do both of the preceding.

...and THEN bear the fm8 introduction, in mind! Which is what the image above is about.

--sd