Automatically populate field in UNRELATED table

[MichelleGrose]

I have an autogenerated serial number field in a Quote table that I would like to automatically populate in an Invoice table (create a new record and then populate field in Invoice) when the Quote has been accepted (I have a status field I was planning to use as the trigger).

I can't figure out how to populate the value without creating a relationship/join - I would like the Quote number to act as the join (and therefore link all of the quote details to the Invoice), but I don't want to manaully enter the quote number. Any ideas on automatically populating the value? (I tried the copy/paste feature from the script but to no avail)

Any/all advice would be greatly appreciated!

Michelle

[Søren Dyhr]

$variabale might be a method, but what is wrong with relations? If you graphing disincline, might some methodology be in order:

Poke into the Anchor Bouy part of this:

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

--sd

[comment]

Will there ever be more than one invoice per quote, or more than one quote per invoice?

If the answer to either is yes, you should make your script store the current quote's QuoteID in a variable (use the SetVariable[] step), then go to the Invoices table, create a new invoice record, and use Set Field[] to set the invoice's QuoteID field to the variable.

If the answer to both is no, you could set the relationship between Quotes and Invoices to allow creation of records in Invoices. So that:

Set Field [ Invoices::QuoteID ; Quotes::QuoteID ]

would automatically create a related Invoice - if one doesn't exist already.

[Søren Dyhr]

Perhaps it needs to get established, that having separate tables for invoices and quotes, often is a little overkill, since there often only are attributal differences between the documents, which easily can be handled by a different looking layout showing the same stuff, or even narrowed down to a <>that changes what's send to the printer.

I does however raise another question, does all invoices have to have a quotation as well when it never have been send to the customer... and here could this perhaps deal with it?

http://www.fmcollective.com/2006/12/postdesign_cauc.html

...but the problem is to define which is the subset of which, because some quotations will never turn invoice, so perhaps a Activity table is from where both invoice and quotations inherit the informations from?

--sd

Edited January 6, 2007 by Guest

[Søren Dyhr]

I couldn't help being slightly inspired by Corns blog, so I set out to see what takes of scripting to shift wagonloads (pages of itemlines) of data from one apparent table to another utilizing the tunneling of data, when it is so that we would hate two locations for the same data if posible at all.

The above graph is tied together non achor bouy'ish because it better illustates some of the points. But as the various "Allow creation of related..." is et up, does it all boil down to following scriptsteps:

If [ Get ( LayoutNumber ) = 1 ] 

     Set Field [ Invoice::InvoiceDate; Get ( CurrentDate ) ] 

End If

Where the condition is that the script only should work in the quotation layout, and force thru the creation of the invoice with the same content, but with it's own date and invoice number.

What I never ever have given a thought until now is the posibility to turn "Allow creation..." on in both sides of a relational connection.

As a kind unexpected yield here is if the storage senses a invoice exists over the many to many relation will it be abel to adjust storage levels accordingly, while the linkings with only a quote is the storagelevel that still is "optional"!

--sd

[comment]

There are many possibilities, but I don't think anything can be established before Michelle provides some clarifications.

[Søren Dyhr]

Indeed, but can you follow the denounciation of relations here?? Which is what the threads name suggests!

--sd

[MichelleGrose]

I must admit that much of what you're suggesting is beyond me. I will do my best to clarify.

#1 - There will be many Quotes and not all of them will turn into Invoices

#2 - There will only ever be a 1:1 ratio of Quote to Invoice and vice versa.

#3 - I tried using the SetField function as well, but didn't make a connection with how it would know which Quote ID to use if there's no relationship.

#4 - My issue in using a relationship is that I will have manually 'find' the join field value in the Invoice table. Or do I not?

Thanks for all of your help.

Michelle

[comment]

There will only ever be a 1:1 ratio of Quote to Invoice and vice versa.

Then perhaps you don't need any of this, and you could just mark a quote as invoice?

If you do need a separate table for invoices (for example, to maintain a consecutive InvoiceID), then what I suggested earlier should work. The relationship should be defined as:

Quotes::QuoteID = Invoices::QuoteID

and creation of records should be allowed on the Invoices side. Filemaker will know to use the QuoteID from the current record in Quotes (this is assuming you are running the script from a layout of Quotes, and intend to create a new invoice for the current quote). There's no need to find anything.

[MichelleGrose]

OK...I thought I set it up per your instructions but I'm getting the following errors:

when I run the script, it says "This operation cannot be performed because one or more of the relationships between these tables is invalid". I deleted the relationship and recreated it, and got the same error. I've attached images of the relationship and of the script.

Thanks again for all of your help!

MG

Set_Field.zip

[comment]

I can't see how your QuoteID field in POs is defined - seems the problem is there.

Here's a simple demo showing what I had in mind.

P.S. Please use .gif or .png formats for screen shots - I nearly squinted my eyes out...

Quote2Inv.fp7.zip

[MichelleGrose]

Thanks very much-I'll have a look and play around with it. Sorry about the image - I forgot to blow them up larger before I zipped them. :

[MichelleGrose]

No such luck (for some reason I just can't see what I'm doing wrong with my field definitions. I've included screen shots (jpg)and a stripped down version of the db. Please use username Doug and password PurelyPrint.

Thanks again.

MichelleGrose.zip

Edited January 7, 2007 by Guest

[comment]

The field QuoteID in POs needs to be indexable - go to Storage options and turn on "Automatically create indexes as needed".

[Søren Dyhr]

Alright the itemlines reside in repeaters which is a little iffy, but since invoices is a genuine subset of Quotes, would I not put the field more_detail in the related Invoice if what it contains is the itemlines ...since the fields easily can be seen in the related record, instead would I say that more_detail should be the invoicing date.

But perhaps it's already understood?

--sd

[comment]

I haven't noticed the repeating fields. That is more than "a little iffy", that's really bad - but it is a separate issue. In any case, in my example the details are NOT line items. I just wanted to show that you can add some data that is specific to the Invoice. A date is a good example.

[MichelleGrose]

Hi again - sorry for the lack of response (was out of town with no access to a computer). I got the script to work and it creates the record perfectly - however, all of the other fields (related fields I moved into the Invoice layout) say "Index Missing".

That beind said - I also gave a lot of consideration to your previous comments about simply using a different layout and decided to go that route (using an if/then statement to determine when to create an Invoice number).

Thanks to all for a great dialogue. MUCH appreciated.

Michelle