Variations of auto_enter Serial

[merkaba22]

I looking for a variant for "auto_enter serial" function.

Invoice set up for auto enter ...

Inv1

Inv2

Inv3, etc. is normal....

I need a way to allow

Inv1

Inv2 -1

Inv2 - 2

Inv3, etc.

For the life of me I can not see how to do this .... Any ideas out there?

Thanks

[merkaba22]

Username: Inky Phil

I got an email from you but there is attachement and nothing in this thread ....

Thanks though ....

[Søren Dyhr]

It seems pretty irregulary the way it progresses, Inv1 has no subs, while inv2 has 2, how man subs holds inv3?? If there however is a pattern, should the field instead be a calc'field with base calcualtion, for the display purpose ...while the number behind is a straight forward autoenters incrementations.

This CF comes to mind:

http://www.briandunning.com/cf/158

...alright with some tinkering though!

--sd

[Inky Phil]

Hi Merkaba

I posted a sample file for you but I realised that it didn't do what you wanted so I deleted it

Sorry for the confusion

Phil

[merkaba22]

Perhaps this will make it clearer:

Inv1

Inv2, Inv2 - variant a, Inv2 - variant b

Inv3

Inv4

Inv5, Inv5 - variant a,

Inv6,

etc.

where any sequenced Invx can have up to three additional variants, a and b, before continuing the sequence....

Its just for an invoice numbering system that either:

1) allows for same invoice number variants

or

2) allows for auto-gererated serial numbers when a new record is created.

Is there a simple way to accomlish this?

Thanks:)

[Søren Dyhr]

I'm not getting it why is it Inv2 and Inv5 which break the regularity??

--sd

[Inky Phil]

Geoffrey, there is no built in method of doing what you want but I think that you are possibly coming into this problem from slightly the wrong direction.

Before you get to the point of generating a new invoice record, your user should have decided that the invoice he wants to generate is either a brand new one or an appendix of an existing one.

This can be determined by the user consciously making that decision or the decision might be made for him by the context from where he is trying to generate the invoice.

Either way you will have to design the choice into the system because just like Soren and I, the system has no way of knowing what is required.

Once you have determined how to make that choice, and should that choice be an appendix of an existing invoice, then there is a simple method (via script) of checking which version it is and numbering accordingly.

HTH

Phil

[merkaba22]

Thank you -- I was pretty sure there was no built in feature I could draw upon.

Assuming the user did know whether he wanted the next serial value or an appendix to an existing one, what script would you recommend that could be triggered by a button, of course, and perhaps, provide a dialog box for the choice?

[Genx]

Lol, i always think your display picture is a cheesecake for some reason Phil.. it weirds me out.

[Inky Phil]

OK Geoffrey

I have prepared a small file that demonstrates how I would generate the numbers you have specified.

It is fairly self explanatory. All that you have to be aware of is that there are only 3 existing invoices numbered 1, 2 and 3.

Inv No.1 has already had 2 copies, the original plus 1 appendix.

Inv No.2 has 3 copies, the original plus two others.

Inv No.3 has just one copy on file

I have built in a check for 3 existing copies because I believe that earlier you mentioned that this would be the max.

Hope it helps

Regards

Phil

invoice_appendix_-_or_not.zip

[Inky Phil]

Wish it was a cheesecake mate - on a pre Christmas diet - Grrrr!

Phil

[merkaba22]

That's actually quite nice -- thank you; I just finished another approach where I used a seperate table/layout for the Appendix's and by using a common identifier can tally each.

Now I want to integrate your solution simply because it is more eloquent:)

Thanks again

[Inky Phil]

Glad I could help

Phil

[LaRetta]

This can break in mutli-user mode; both on the invoice number creation and on the append number creation. Any time you are creating a record in one table and looking to another table for the LAST number, two Users can create the same number.

From an accounting aspect, this will also be a nightmare to work with. Why are you appending invoices? If you wish to track changes made to an invoice (before it is a TRUE invoice), the invoice archive idea is fine (to copy an invoice to it before a change is made to the original). Or even use an audit track process on your invoices table (simpler, takes less space as well). But keeping multiple copies of an invoice is highly problematic. Which table is your TRUE Invoices table? Central will always contain only one. So your Invoice Archive is your true Invoices table? Be prepared to filter out all prior versions of each invoice so they are never included in your sales, summaries, portals, searches - everything will need to be filtered. This filtering means that you will have to MARK an invoice as the final at moment of mailing/posting because you can't display only the LAST correct invoice in a portal unless it's marked.

Simply, an idea such as this makes my anal sensibilities shudder bigtime. Phil's idea DOES solve your problem. But just because FileMaker CAN do something doesn't mean we should. Just my two cents ...

Oh, and I LIKE Phil's avatar - doesn't look like a cheesecake at all! It's obviously a stuffed doggie with it's tongue out.

LaRetta :wink2:

[merkaba22]

Hey LaRhetta!

Great to hear from you again:)

I just did a work around: created a seperate table and layout called Change Orders, called up the Invx by VL and added a suffix to create a calc, "Invx & COx", etc.

In the POs layout (as yet unmentioned) I created a Change Order field and a value list for all Change Orders which would have as a lookup the Invx and an invoice field. If there was no Change Order, then the user could use a VL for the Invoice field.

In both the Invoices and Change Order table there was a relationship to a data table -- by using either VL, I got the data so show up in the portals!

Now if there was a slick way to have just one VL in Change Order that returned the values for both "Invx & COx" and "Invx"...., I could go home!

[LaRetta]

I have no idea what you are doing or why. As Soren asks, "I'm not getting it why is it Inv2 and Inv5 which break the regularity??" Neither do I.

So now your 'changes' reside in another table? Simply, without a clear view of your existing structure and without knowing what you want to accomplish, I wouldn't make a suggestion at all. A standard Invoices/LineItems structure is adhered to for reasons (far beyond a Sales Rep's needs); Accounting and Management need access as well. I would carefully consider this structure; we haven't even seen your file to know WHAT you have. Changing your base later would be more than simply 'difficult;' it will throw your AR balance off as well.

LaRetta :wink2:

[merkaba22]

Sorry for just relating what I am doing conceptually -- it works fine and there are no problems with the design.

I think explaining in the detail that would justify a profound discussion would be a large undertaking and so I defer.

However, if there was a way to combine two VLs, each from a different table, would be great technique to know about:)

[Søren Dyhr]

However, if there was a way to combine two VLs, each from a different table, would be great technique to know about:)

By having these two tables merged into one, this do at first sight look like a FAQ in normalization ...again are you asking in riddles, where we usually stumble over the way you economize with information:

without a clear view of your existing structure and without knowing what you want to accomplish, I wouldn't make a suggestion at all.

it works fine and there are no problems with the design

This was exactly what expirienced in this thread:

http://www.fmforums.com/forum/showtopic.php?tid/182086/post/229323/#229323

--sd

[LaRetta]

I think explaining in the detail that would justify a profound discussion would be a large undertaking and so I defer.

So, not even provide a file of what you are attempting? No problems with the design? Reading back through your prior threads, I disagree with you and your current post also indicates otherwise. Or how about telling us how it is currently connected? Then how in heavens, and why in heavens, do you propose that we answer structural questions (and that IS what you are asking) based upon no information? And why should we waste our time providing answers which may or may not even fit the need if you won't spend the time giving us what we need?

Answer? I won't. Maybe someone else will.

LaRetta

[merkaba22]

Sorry LaRheta, I had reconsidered my last post and was in the middle of this detail when you replied .... Overall, I am sorry that my last post seemed to waste your time -- I was trying not to, actually.

To try to answer: the decision to have a variant of the invoice is arbitrary and solely based on the needs of the user; ie. there is no inherent pattern.

The variants are now created in a separate table and there are no issues in the design -- I have not requested how to merge the tables (at least as I would understand the term); sorry for the confusion -- I thought these were easy questions; I realize they are not.

Invoice_table comprises:

InvoiceField

FieldOne

Inv_table_ID – auto-generated, serial where:

record1: InvoiceField= " Inv1" -- FieldOne = "Blue"

record2: InvoiceField= " Inv.2" -- FieldOne = "Red"

record3: InvoiceField= " Inv3" -- FieldOne = "Green"

ChangeOrder_table comprises:

InvoiceField1D,

FieldTwo

CO_table_ID – auto-generated, serial

COversionNofield -- choice of numbers 1-4, for example, and

COField1 which is a calcfield "InvoiceField1" & " - " &COversionNofield

where, and for example:

record1: COField1= "Inv1 - 1" -- FieldTwo = "Red"

record2: COField1= "Inv3 - 1" -- FieldTwo = "Yellow",etc.

Following this example:

PO_table (related to both Invoice_table and ChangeOrder_table)

FieldThree

FieldFour

Field_calc = (FieldThree) &(FieldFour)

Invoicefield – VL1 returning values, "Inv1", "Inv2", "Inv3", etc. from Invoice_table:InvoiceField to trigger lookup in FieldThree for associated FieldOne values.

COfield – VL2 returning values, "Inv1-1", "Inv3 - 1", etc. from ChangeOrder_table:COField1 to trigger lookup in FieldFour for associated FieldTwo values.

Inv_CO_field_calc = (Invoicefield) & (COfield)

Accordingly, the user can choose values the VL1 from Invoicefield to trigger a lookup of values from related records in FieldOne

or

can choose values the VL2 from COfield to trigger a lookup of values from related records in FieldTwo.

The result is displayed in Field_calc and in Inv_CO_field_calc.

The above description assumes that the correct relationships between Invoice_table, ChangeOrder_table and PO_table – at least because in my real-life project the design I am working on works….and in at least modesty or ignorance I do not see the relevance in this example – although I would be happy to further articulate, if need beJ

Accordingly, from PO_table the choice from the VL1 from COfield, "Inv1-1", triggers a lookup and returns "Red" in Field_calc and "Inv1-1", in Inv_CO_field_calc

or the user could select

from PO_table the choice from the VL2 from Invoicefield, "Inv1", triggers a lookup and returns "Blue" in in Field_calc (perhaps this is what by the term “merging tables”) and "Inv1" in Inv_CO_field_calc

In PO_table is possible to have, say, VL3, returning values from VL1 and VL2:

"No.1", "No.2", "No.3"

AND

"Inv1-1", "Inv3 - 1"

The object of the selection of any one value in the V3L is to trigger a lookup to display the associated value in Field_calc.

Edited November 19, 2006 by Guest

[Inky Phil]

Hey Laretta,

If I go back a few posts of yours I wonder if you can help me.

You say that 2 users could end up with the same number.

Now making sure this NEVER EVER can happen is something that I have been struggling with for years and I have followed a few discussions (2 of which I started myself).

The bottom line advice, usually provided by comment, was that the only way of ensuring a unique number was to use FM's own auto enter feature. I thought that by getting my number from a table with 1 record and 1 auto enter serial field I had done that but it would appear not.

Any help on this one would be really appreciated cos this is one issue that I would LOVE to resolve.

Oh and by the way, that isn't a fluffy dog. It's a picture of ME. Don't I cut a dash in my Sunday best eh?

As ever, Thanks in Advance

Phil

[comment]

Ahem... I believe I have always tried to make it clear that it is the only way I know of.

[Inky Phil]

:oops: I stand corrected. (Hell, I could sure do with a sit down now and again!)

Phil

[LaRetta]

Good morning, Phil.

This can break in mutli-user mode; both on the invoice number creation and on the append number creation. Any time you are creating a record in one table and looking to another table for the LAST number, two Users can create the same number.

My wording was poor. I am NOT concerned about the Invoice Number Generator table. A User is standing in this table when they create a new Invoice and you are using FM's auto-enter serial. It will never duplicate. My concern was on the other end when you create the invoice number (or appended a number) in the archive. Part of the confusion has to do with not properly priming your demo (been there, done that). Your Invoice Number Generator table has TWO Inv 4’s and auto-enter serial indicates the next number should be 4. Also, your archive shows the following records:

1

1-2

2-1

2-2 etc.

I mention these inconsistencies because it can confuse the logic as I explain why it can break. When I viewed the demo and saw the single Inv 1, it appeared that you realized that ALL invoices should be added to the append table; otherwise, how will you know what the original invoice contained before its first append? But then again, I still can’t make sense out of merkaba22’s request, ie, I have no idea what a variant means nor when/why it would happen nor do I understand the structure. But if similar to audit track, the original would need to be captured before the append process begins.

Where is Inv 2? Is 2-1 the original Inv 2? Nope, it appears to be the first append to it. Nonetheless, your demo has no contingency for appending NEW invoices. Create a new invoice (Inv 4). Then try to append it. It won't happen because Inv 4 didn’t write to the archive. So both the original and each append must appear in the archive (that’s what I meant by new invoices and appends). If invoices are writing to Archive (and you were multi-user) then you could see the breaks happening. One example:

User 1 creates an append to Inv 3. There is already 3-1 so script creates 3-2. User 1’s cursor is still in the field (they are making changes to the invoice) so it has not been COMMITTED thus not yet recognized by FM. Your script can (and should) add a Commit Records/Requests after creation but there is STILL the potential for failure here (in that nanosecond)! User 2 creates an append to Inv 3. Script counts, doesn’t recognize User 1’s record creation and creates 3-2. You now have a duplicate.

And stand back up, sir. Your demo was well-constructed and commented ... it’s the archive PROCESS that I was concerned about, not the original invoice generation.

LaRetta :wink2:

[Inky Phil]

Thanks for that LaRetta, appreciate the explanation.

Like you I had wondered how this system was going to be maintained but I went ahead on an 'ours is not to reason why' basis.

Take Care

Phil

[Søren Dyhr]

but I went ahead on a 'ours is not to reason why'

You expression, have been unknown to me until now - where I traced it to Tennyson and the Crimean War, thereforeis it probably canonized learning in the anglo saxon part of the world ...but for us outside, is it "gefundenes Fressen" ...thanks Phil, it precisely express my reservations against some developers line of replying....

--sd

[Inky Phil]

Delighted to have been the source of some "gefundenes Fressen" for you.....because until your post, and the subsequent googling, I didn't know what that meant

Every day you learn a little something.

Phil

[merkaba22]

I have created a model with a table for Invoices, Change Order and POs.

Invoices has an auto generated Invoice No.

In Change Order, there is a field with a VL for Invoices which when an Invoice number is selected brings over a copy of the related data via lookups. The user can add an appendix number and create a Change Order Number and then modify the data with out disturbing the orignal Invoice.

In PO's, there is an Invoice and a Change Order field, each with VLs so that the user can call either an Invoice or Change Order for creating a PO. If the user calls a Change Order the orginal Invoice No is referenced -- in either case the related data appears for the PO via lookups....

The question I ask: Is there a way to replace the Invoice and Change Order fields (and their associated VLs) in the PO with a single field with a single VL (that comprises the Invoice and Change Order numbers) that triggers the appropriate lookups?

Value_lists_in_PO.fp7.zip