12 Replies Latest reply on Dec 7, 2011 5:24 PM by PedroMarroquin

    Help with Relationship, Urgent!

    PedroMarroquin

      Title

      Help with Relationship, Urgent!

      Post

      So i have two tables with two layouts, those layouts are invoice that are printed 1 is for common clients and the other one is for government, why two different layouts? because government ask for specifics that the common client invoice can't have... The thing is this the government allow certain No. of ID for invoices mine is from 11352 to 12000, an those number can't be splitted into government and common what I need is that if invoice No. 11352 is made in common invoice is ok but when I go and do a government I need the number to be 11353... One batch of serial numbers for two different tables... There's another thing it can't be serialized because there are times when one invoice is two long and we use the same No. of invoice for example  Invoice for government No. 11360 is two pages long so the two pages are 11360 the next invoice must be 11361... Maybe just a portal to remember what was the las invoice No. used, but I don't know how to do that!

       

       

      I tried making one table with 1 field that is serialized but it doesn't work, what can  I do?!

      Filemaker_question.jpg

        • 1. Re: Help with Relationship, Urgent!
          philmodjunk

          there are times when one invoice is two long and we use the same No. of invoice for example  Invoice for government No. 11360 is two pages long so the two pages are 11360 the next invoice must be 11361...

          If you use this table structure:

          Invoices---<lineItems>----ProductsOrServices

          You can print invoices from a layout based on the LineItems table and you can print invoices of any length that you need without having to split anything.

          See this demo file for an example: http://fmforums.com/forum/showpost.php?post/309136/

          I suggest you use an invoiceID number that is an auto-entered serial number for your relationships to other tables. Keep this separate from your Invoice Number field and don't put it on your invoice layouts.

          Here's a way to generate different Invoice Series "on demand" and it works both in single user and multi-user systems:

          Define two new tables: PrivateInvoiceNumbers, GovtInvoiceNumbers

          Define two fields in both of these tables:

          Invoice Number (to be used on your print layouts for each type of invoice, define this as an auto-entered serial number field)
          InvoiceID (to link records in these tables to specific invoice records. define this as a number field.)

          Link them up in relationships:

          Invoices::InvoiceID = PrivateInvoiceNumbers::InvoiceID
          Invoices::InvoiceID = GovtInvoiceNumbers::InvoiceID

          In both of these relationships, enable "allow creation of records via this relationship" for the two invoice numbers tables.

          In the two new tables, set the next serial number settings on Invoice Numbers to the appropriate next value for that type of invoice.

          Use this script to assign the appropriate invoice number to an invoice record:

          If [Invoices::InvoiceType = "Government"]
             Set Field [GovtInvoiceNumbers::InvoiceID ; Invoices::InvoiceID ]
          Else
             Set Field [PrivateInvoiceNumbers::InvoiceID ; Invoices::InvoiceID ]
          End If

          On your layout for Government Invoices, add the GovtInvoiceNumbers::Invoice Number field. Add the PrivateInvoiceNumbers::Invoice Number field to the Private Invoices layout.

          With "allow create..." enabled in the relationships. The two set field script steps creates a new record in their respective tables if one does not already exist. The new records then auto-enter the appropriate invoice number for that invoice type.

          • 2. Re: Help with Relationship, Urgent!
            PedroMarroquin

            I don't understand, I mean I create two new tables, PrivateIdNumber and GovtIdNumber the I create two fields: invoice number which is serail number and the invoice id then in relationships I relatethe invoiceid of each of the two tables and then what? I don't understand the thing of: Invoices::InvoiceType = "Government", why is Invoices then the subcategory of it which would be what? and government i assume is the table I newly created...

            • 3. Re: Help with Relationship, Urgent!
              philmodjunk

              If you have all invoice records in the same table, then you need a field to distinguish invoice types. This could be a text field with a value list of two values: Government and Private. When completing the invoice you select an invoice type. To assign the needed invoice number, you perform the script I posted. This could be from a button or a script trigger associated with the invoice type field so that selecting the type also performs the script and assigns an invoice number to the invoice record.

              • 4. Re: Help with Relationship, Urgent!
                PedroMarroquin

                But I mean from 0, I have the two type of invoices table already with layouts in the picture that I uploaded they're "Comun_Facturacion"  and "Gobierno_AguaYDrenaje_Facturacion" in those tables I have the Name, the description field, the prices etc. now I have created the two additional tables  the Private and the Govt with the two additional fields but I don't understand in the script how will I write it? can you just replace the names please...

                If [Invoices::InvoiceType = "Government"]
                   Set Field [GovtInvoiceNumbers::InvoiceID ; Invoices::InvoiceID ]
                Else
                   Set Field [PrivateInvoiceNumbers::InvoiceID ; Invoices::InvoiceID ]
                End If

                • 5. Re: Help with Relationship, Urgent!
                  philmodjunk

                  We have a language problem as I could not read the names of the boxes in your screen shot. I have assumed that you have one table for all invoices. If you are using separate tables for private and government invoices, then I do not see why you would have an issue with invoice #'s as this can be controlled in the two auto-entered serial number fields where you can specify different "next values".

                  • 6. Re: Help with Relationship, Urgent!
                    PedroMarroquin

                    Yes indeed I have two different tables one for Private and another for Government, the thing is i don't know how if a invoice in the Private gets the No. 11360, what can I do for the next invoice in the Government to be 11361, 

                     

                    now I have a field in the private invoice table which is serial number set from 11377 and in the government tabe the same another field  which is serial number set from 11377. so if I create a new invoice in private it'll be 11277 and if I'll create a new invoice in government it'll be 11377, the thing is that they can't repeat! How can i create a serial number field that is global that when I create a new invoice in the private it'll be 11377 and when i create a new invoice in the government it'll be 11378 automatically!?

                    • 7. Re: Help with Relationship, Urgent!
                      philmodjunk

                      I'm confused by that last post as it doesn't seem to match your first post.

                      The thing is this the government allow certain No. of ID for invoices mine is from 11352 to 12000, an those number can't be splitted into government and common

                      Don't you need one number series for government invoices and a different number series for private that overlaps?

                      That would not be the case if: "when I create a new invoice in the private it'll be 11377 and when i create a new invoice in the government it'll be 11378"

                      If both government and private invoices have the same series, but with unique values for each invoice, I'd put them all in the same table and use a single serial number field, but with a "type" field to distinguish government from private invoices.

                      Please clarify what you need here.

                      • 8. Re: Help with Relationship, Urgent!
                        PedroMarroquin

                        Yes the government allow me from No. 11352 to No. 12000, I have the the serial number fields in each table, private and government! but I can do as you say to make a single table and put a text field, because the layout of government invoices have specifics that the private invoices layout can't have! that's why I have two tables... with two different serial number field

                        • 9. Re: Help with Relationship, Urgent!
                          philmodjunk

                          Which do you need?

                          This sequence: (a single series of invoice numbers)

                          11377 Gov.
                          11378 Priv
                          11379 Priv
                          11380 Gov

                          or: (a separate series of invoice numbers but with no common values)

                          11377 Gov
                          11378 Gov
                          11379 Gov
                          10000 Priv
                          10001 Priv
                          10002 Priv

                          Originally, I thought you needed the second option, but now it seems you need the first. You can use two layouts that refer to the same table, they do not need to be separate tables. You can use extra fields only used for one type or the other invoice or you can use a related "detail" layout to record the details that are specific to a given type of invoice. Either approach works much like the other once you have separate layouts for each type of invoice.

                          • 10. Re: Help with Relationship, Urgent!
                            mgores

                            You could even have it go automatically to the governent layout or private layout based on the contents of the type field.  The governent specific fields would not show on the private layout and vice versa as Phil suggested.

                            Although if as you originally stated "the government allow certain No. of ID for invoices mine is from 11352 to 12000" it seems that you would want to reserve those numbers for government and not use them for private invoices.

                            • 11. Re: Help with Relationship, Urgent!
                              philmodjunk

                              Although if as you originally stated "the government allow certain No. of ID for invoices mine is from 11352 to 12000" it seems that you would want to reserve those numbers for government and not use them for private invoices.

                              And that is the source of my confusion here...

                              • 12. Re: Help with Relationship, Urgent!
                                PedroMarroquin

                                Is because I'm creating a huge mistake! is not the government that permits me to use certaing Invoices No.s is the equivalent to the IRS! is not that those number of invoices are specially to the government! no that is a confusiion