1 2 Previous Next 19 Replies Latest reply on Apr 15, 2014 10:58 AM by philmodjunk

    Invoice template, i need to add account number fields

    evansj2

      Title

      Invoice template, i need to add account number fields

      Post

           hello all

           I used the Invoice template as a starting point and because we rarely take cash/credit I need to add space for account numbers. One client may have as many as 15-20 different accounts they use throughout the year and they are not shared with anyone else.

           When I create an Invoice I would like to be able to pick from a list or Add one. So I guess my question is what's the best way to do that?

           I created a text field named Account Num and have it showing up in the right spot when I print.

           Jim

        • 1. Re: Invoice template, i need to add account number fields
          philmodjunk

               Sounds like you will need to add a new table for account numbers and link it in a relationship to the customers table by the CUSTOMER ID MATCH FIELD.

               You can then add a field for account number to the Invoices table so that you can then add it to the appropriate Invoices layout(s). You can format this field with a conditional value list of only the account numbers for the customer you have selected for that invoice.

               If you do not know how to create a conditional value list, let me know and I'll back up the "info dump truck" to Post a new Answer and share a number of links on how to set up conditional value lists in my next comment posted here.

          • 2. Re: Invoice template, i need to add account number fields
            evansj2

                 I guess the easiest way to say it is Beep Beep Beep (imagine a truck backing up)..

            • 3. Re: Invoice template, i need to add account number fields
              philmodjunk

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Invoice template, i need to add account number fields
                evansj2

                     Making progress, created another layout called "accounts". have two fields 1)Account Num and 2)CUSTOMER ID MATCH FIELD.

                     In "Customer" I added another slide to the "Address home/shipping" bar called "Accounts".

                     On it I created a portal to the other "accounts" layout with both fields showing up for now.

                     On the portal setup I selected Filter portal records and specified

                     accounts::CUSTOMER ID MATCH FIELD = Customers::CUSTOMER ID MATCH FIELD

                     In the accounts file I have

                     CIMF- 3168 ACT #123456789

                     CIMF- 3168 Act # abc def

                     CIMF- 3297 Act # 987654321

                     CMIF- 3297 Act # 1212121212

                      

                     When I look at my record, 3168, I see the correct ones, however when I go back to the Customers tab and select my wife, 3297, I don't see her account numbers, its blank.

                     I played with it for the last couple of hours with no luck. I am sure its something easy, just haven't tripped over it yet.. Any ideas?

                     Also, on the Accounts slide I created I would like to have an Add button so I can add an account number to their record. is there a quick way to do it?

                      

                • 5. Re: Invoice template, i need to add account number fields
                  philmodjunk
                       

                            On the portal setup I selected Filter portal records and specified

                       

                            accounts::CUSTOMER ID MATCH FIELD = Customers::CUSTOMER ID MATCH FIELD

                       Don't filter this portal. Link accounts to customers in Manage | Database | relationships by this match field. No filter is needed.

                       If you enable "allow creation of records via this relationship" for accounts in this relationship, you can add new account records directly in the portal by entering an account number. The new record will be automatically linked to the current customer record.

                  • 6. Re: Invoice template, i need to add account number fields
                    evansj2

                         think I have it they way you describe, see attached.

                         I can add and delete account numbers from from my record, 3168. 

                         But when I go to another customer, 3297 (which I know has entries) the portal is empty.

                         When I try to enter a number from 3297 and hit enter I get the following error prompt "this operation cannot be performed because one or more required related records are not available and cannot be created."

                         just as an fyi - the file is on a FMP12 server, but I am working in 13.

                    • 7. Re: Invoice template, i need to add account number fields
                      evansj2

                           ok sometimes you just have to look at it one more time. wrong relationship (how often have I said that?) Was linking the invoice nto the client. works now!!

                            

                           thanks so much for your help. have a good weekend.

                           Jim 

                      • 8. Re: Invoice template, i need to add account number fields
                        philmodjunk

                             The invoice should be linked to customers, but accounts should be linked to customers directly, not to Invoices. Perhaps that's what you mean.

                             Your error message indicated that you hadn't yet assigned a value to the match field in the layout's current record--Invoices::CUSTOMER ID MATCH FIELD according to your last screen shot. That won't be the case once you link accounts to customers instead of invoices, but you'll still need to select or enter a value in that match field in the current Invoices record before you can add account records in a portal on the invoices layout. (But not via a portal to accounts on the customers layout.)

                        • 9. Re: Invoice template, i need to add account number fields
                          evansj2

                               We tested for a week and everything seems to be working, the final piece is printing an estimate/invoice with one or more of the account numbers associated with the client.

                               I have the account numbers showing up on the "Invoice Details" layout and was thinking of a  simple check box next to the account number(s) to use in this invoice. If then statement? Had zero luck getting this to work...

                               Any ideas?

                                

                          • 10. Re: Invoice template, i need to add account number fields
                            philmodjunk

                                 If you have a account number field defined in Invoices in which you enter or select an account number, then you can include that field in the header of your print layout that is based on Invoice Data.

                            • 11. Re: Invoice template, i need to add account number fields
                              evansj2

                                   its easier with pictures. This is the client tab where i have added a tab for Account numbers. This slides a portal to another table i created called accounts. You only see those account numbers assigned to this person, i included a pict of the accounts dbase below.

                              • 12. Re: Invoice template, i need to add account number fields
                                evansj2

                                     Selecting New Invoice gets you here.

                                • 13. Re: Invoice template, i need to add account number fields
                                  evansj2

                                       By selecting the account number(s), yes it may be more then one, i would like it to print here.

                                  • 14. Re: Invoice template, i need to add account number fields
                                    evansj2

                                         And just  to state the obvious, we would need this invoice to carry the account number(s) chosen.

                                         So the other idea i had was that once you hit the New Invoice button (from the selected client) a list of their account numbers are displayed (or you can add a new one) and you select them and it takes you to the invocie page where you can add products to the mix.

                                    1 2 Previous Next