1 2 Previous Next 29 Replies Latest reply on Jan 18, 2012 8:46 AM by PedroMarroquin

    Relationship Problem I think! Urgent Help Please!

    PedroMarroquin

      Title

      Relationship Problem I think! Urgent Help Please!

      Question2.jpg

        • 1. Re: Relationship Problem I think! Urgent Help Please!
          mgores

          I remember this from a previous post.  Can you explain why you would allow Private invoices to use the numbers between 11352-12000 if those are required to government invoices?

          If you ever reach 648 government invoices you will be out of allowable numbers, allowing private invoices to use numbers in that range will make that happen sooner.  Why not have the private invoices start at 13000 or some other number > 12000.  Or you could add a prefix to the private invoices so it would be P11352  while the government invoice would be 11352.  

          • 2. Re: Relationship Problem I think! Urgent Help Please!
            PedroMarroquin

            There's a confussion here, right now the IRS have gave me permission only to use invoices from No. 11352 to No. 12000, This doesn't mean that this numbers are specially for the Govt. Invice! this numbrs are to b reported to the IRS! when does No. are finished when i get to Invoice No. 12000 I will have to issue a requestment to the IRS to give me more Invoice No. ! 

            • 3. Re: Relationship Problem I think! Urgent Help Please!
              mgores

              I'm sorry.  I undersand now.  I think Phil suggested in the earlier thread that you use one table for invoices with fields for both private and gov invoices.  Then have separate layouts that have the appropriate fields.

              • 4. Re: Relationship Problem I think! Urgent Help Please!
                philmodjunk

                Either additional fields or two related tables--one for Govt and one for Private was what I suggested. Both approaches work and both look exactly the same and function exactly the same on your two different layouts so in many ways it's more a matter of developer preference as to which method to use.

                If you set up added "detail" tables, it might look like this:

                GovtDetails-----<Invoices>------PrivateDetails

                Invoices::INvoiceID = GovtDetails::InvoiceID
                Invoices::InvoiceID = PrivateDetails::InvoiceID

                A given Invoices records will link to GovtDetails or PriavateDetails, but not both.

                On your Govt Invoice layout, you include fields from Invoices and GovtDetails.

                On your private invoice layout, you include fields from Invoices and PrivateDetails.

                Both layouts would specify Invoices in "Show Records From" in Layout setup.

                Your invoice number field is then defined as an auto-entered serial number in Invoices and each invoice--whether private or Govt, will get unique sequential numbers.

                If you did not use detail tables, then you'd just have two layouts based on Invoices and you'd put the fields you need for Govt Invoices on one layout and the fields you need for Private invoices on the other. This is a simpler structure but requires a single table for invoices with a larger number of fields--some of which will be empty on any given invoice. Thus, I tend to use a single table if the number of different fields are fairly small and use detail tables if there are many fields different between the two types of invoices.

                Take your pick.

                • 5. Re: Relationship Problem I think! Urgent Help Please!
                  PedroMarroquin

                  Phil I hope you excuse me but can you explain me what to do, Is because I'm kind of new to filemaker but I learned some few things so can you explain what to do? I just understood that in your way I can have the invoice number problem solved and that I can split the record in show records from.... Can you explain me how to do that?!

                  • 6. Re: Relationship Problem I think! Urgent Help Please!
                    philmodjunk

                    I've described two different solutions. Which do you want?

                    Do you want to keep to a single invoices table with fields for both private and govt invoices in the same table?

                    Or do you want to use related "detail" tables for private and govt invoices linked to the invoices table?

                    If you are not sure, please describe what is different for govt and private invoices and I can then make a recommendation.

                    • 7. Re: Relationship Problem I think! Urgent Help Please!
                      PedroMarroquin

                      Yes Please! The Prvate Invoice is simpel just Name, Adress, City and State, Tax No. and Postal Code.... and the Govt. has all that plus, No. of Equipment, Area, Notice, Requestment and No. of Unit, as I said is more especific... I would like a method in which I have the No. of invoice as I said in one and consecutive and that I can distinguish if the Invoice is Govt or Private and even if it's possible a 3rd Invoice Layout for Govt but different company of Govt, why another layout and not use the one I already have? beacuse again it has different fields and the client would like to differentiate the Govt. company's invoices...

                      • 8. Re: Relationship Problem I think! Urgent Help Please!
                        philmodjunk

                        Adding 5 more fields for Govt invoices could be done with a related detail table (you don't seem to need one for private  here), but I really don't see the need.

                        Use one Invoices table with these fields defined:

                        InvoiceNumber (Auto-entered serial number )

                        Name, Adress, City and State, Tax No. and Postal Code

                        InvoiceType (use this field to identify the invoice type, Govt or Private)

                        No. of Equipment, Area, Notice, Requestment and No. of Unit

                        Thus, you have one table, but some fields (5 by your last post) will be left unused when this is a private invoice as opposed to a government invoice.

                        Create an invoice layout for Private customers and leave off the 5 fields that are not applicable.

                        create a second invoice layout for Govt customers and include the 5 additional fields.

                        Both layouts refer to the same table so each will get a unique, sequential invoice number from the same series of numbers.

                        Now for some hints to make this work more smoothly.

                        1 ) If your Two layouts are named "Pvt Invoice" and "Govt Invoice"

                        Then you can set up an auto-enter calculation on InvoiceType like this:

                        Case ( Get ( LayoutName ) = "Pvt Invoice" ; "Private" ;
                                  Get ( LayoutName ) = "Govt Invoice" ; "Government" ;
                                  "Unknown" )

                        This way, when you create a new record on the Govt Invoice layout, it is automatically identified as a Government Invoice. If you create a new invoice record on teh Pvt Invoice layout, it's automatically identified as a Private invoice.

                        2) InvoiceType can also be set up with a value list so that the user selects the invoice type. A script trigger on this field can switch layouts if the user is not on the correct layout for that invoice type.

                        3) You can set up scripts that perform finds or constrain found sets and use them with your two layouts so that going to the Pvt Invoice layout only shows you invoices where InvoiceType = "Private". The same method can be used with the Govt Invoice layout to limit visible records to just Government Invoices.

                        • 9. Re: Relationship Problem I think! Urgent Help Please!
                          PedroMarroquin

                          So I unified all layouts in one table, now I have tree layouts, Private, Water Government and Road Government and they all related to one table, and yes when i create a new in private is 1152 and the i go to water government it appears No. Invoice 11352 because I think was the last one and when i hit new it's 11353 I think the problem is the Invoice type field I just created the filed but I mean my idea is make it a calculation filed with this: Case ( Get ( LayoutName ) = "Pvt Invoice" ; "Private" ;
                                    Get ( LayoutName ) = "Govt Invoice" ; "Government" ;
                                    "Unknown" )          but I need for 3 layouts Private, Water Government and Road Government and i thought o making it like a rain check kind of field so if I'm making Private the calculation knows it's private and it only show me private records because that is another problem if I create one in private and I go to water government it shows that there is one record already but is the private record.... how can I fix it please....

                          • 10. Re: Relationship Problem I think! Urgent Help Please!
                            philmodjunk

                            Don't use such a calculation as your invoice will appear to change types just because it is viewed on a different layout.

                            Make InvoiceType a field that is either specified by the user by selecting from a value list or an auto-entered calculation that is entered when the record is created. (The same field can be set up to be modified both ways.)

                            Scripts associated with each layout can limit the records shown to just the existing records of the type specified for that layout. Thus, changing from Private to "water government" will not show invoice 1152--a private invoice as the script will find only records of the type consistent with that layout.

                            Here's a script you can use with all three layouts to only show records of the type appropriate for that invoice:

                            Enter Find Mode [] //clear pause check box
                            Set field [Invoices::InvoiceType ; Get ( ScriptParameter ) ]
                            Set Error Capture [on]
                            Perform Find[]
                            Go to record/Request/Page [last]

                            This script can be performed with the OnLayoutEnter trigger or from a navigation button set up to take you to that layout. Either way you pass the name of the desired invoice type as the script parameter. For the Private layout, you pass "Private" as the parameter, then pass different and distinct names for the two types of government invoices such as "Water Govt", "Road Govt". (Use any text for this label you want as long as you set up the InvoiceType field to use the same values.

                            If you want, you can also use this script to constrain the current found Set:

                            Enter Find Mode [] //clear pause check box
                            Set field [Invoices::InvoiceType ; Get ( ScriptParameter ) ]
                            Constrain Found Set[]
                            Go to record/Request/Page [last]

                            You can use this with the onModeChange trigger to filter the results of a user's find to omit all records not of the specified InvoiceType whenever the layout exits Find Mode.

                            • 11. Re: Relationship Problem I think! Urgent Help Please!
                              PedroMarroquin

                              Can you walk me step throug step please, I mean the "button" that takes me to the Private layout instead of button format shall i make it a script?

                              • 12. Re: Relationship Problem I think! Urgent Help Please!
                                philmodjunk

                                Buttons perform script.

                                Put a button on your layout with the button tool. When you specify the perform script option, put "Private" in the options script parameter box.

                                Set it to run this script:

                                Go To layout [PrivateInvoices]
                                Enter Find Mode [] //clear pause check box
                                Set field [Invoices::InvoiceType ; Get ( ScriptParameter ) ]
                                Set Error Capture [on]
                                Perform Find[]
                                Go to record/Request/Page [last]

                                • 13. Re: Relationship Problem I think! Urgent Help Please!
                                  PedroMarroquin

                                  i don't find that private thing you say and if i go and do the script it doens't let me worte it just let me choose options...

                                  • 14. Re: Relationship Problem I think! Urgent Help Please!
                                    philmodjunk

                                    Click the specify button.That will bring up the dialog box where you specify a script parameter.

                                    You are using FileMaker 11? If not, you'll need to use Manage | Scripts to create the script first.

                                    1 2 Previous Next