11 Replies Latest reply on Oct 22, 2013 9:07 AM by LeanneHuff

    Newbie question - trying to customise the invoicing solution

    LeanneHuff

      Title

      Newbie question - trying to customise the invoicing solution

      Post

           Hello - Due to the fact that I have transferred over to Mac, I have left behind my Access skills and am evaluating if I should purchase FM Pro.  I am using the Invoicing solution as a starter.

           Could someone please point me in the right direction for this as I can't seem to find any information on how to start!

           When adding line items in invoices - when I choose a Product I will want to bring back more fields from the Product details - eg Size and Colour - how to I go about achieving that?

           I will have other questions - but for now if I can solve the above question or be pointed to a something where I can learn how to do it - I will be able to make my mind up about spending this amount of money! 

           Thanks

           Leanne

            

        • 1. Re: Newbie question - trying to customise the invoicing solution
          philmodjunk
               

                    when I choose a Product I will want to bring back more fields from the Product details - eg Size and Colour - how to I go about achieving that

               The layout shown uses a portal  to a line items table called "Invoice Data". As an Access user, think "sub form" and it may help you understand how it works. To add size and color fields, you add them to the Products table and then you have a key design choice as to what you do next:

               1) Simply add the new fields from Products to the portal row after making space in the portal row for the new fields.

               2) Or define fields in Invoice Data that use an auto-enter field option to copy the data from the new fields in Products and add them to the portal row instead.

               If you use method 1, any changes to color and size will automatically appear on all invoices that list that product in the portal. If you use Method 2, only new invoices will show the change unless you take specific steps to update the data.

               Hmmm, and I hadn't noticed before that this starter solution uses the name of the product (item) to look up an item from products. This often is not the best method to use for linking data in a relationship.

               And I "speak Access" having done some fairly extensive work with that database application a few years ago so don't hesitate to describe what you want in terms of Access as I can probably translate that into "FileMaker".

               Note that FileMaker does a lot of things very differently than how you would accomplish the same thing in Access. Don't be surprised to get a bit frustrated trying to set things up like you would in Access only to find that you have to use a very different approach. It takes a bit of time to familiarize yourself with the differences and learn enough about FileMaker to make the transition.

          • 3. Re: Newbie question - trying to customise the invoicing solution
            LeanneHuff

                 @PhilModJunk

                 Thank you Phil - sounds like you understand my frustrations - especially as the terminology is different so its difficult to search for help on how to do what I want.  I am taking it one step at a time, and have quickly made the decision that this database of mine doesn't have to be overly complicated!  I just want to create customer invoices, mark them off when they are paid and know what my profit is on each order, and that's it!

                 I had already tried doing what you suggested and with the No 2 version as, of course, once an invoice has been submitted it needs to stay as it was at the time.  I couldn't get it to work though so that's why I asked the question on here. I was doing something wrong though as I got the fields in the portal - set them to auto populate but they came back blank!

                 I am encouraged though - at least I was on the right track. I'll have another go this weekend - I found subforms on Access a lot easier!

                 @DavidAnders - Thank you for the list of links these will be very useful smiley

            • 4. Re: Newbie question - trying to customise the invoicing solution
              philmodjunk

                   Take a look at the field options for a field. If you define a text field in Invoice Data, you can use one of two approaches on the auto-enter tab to copy data from a field in products into your field in Invoice Data.

                   The looked up value option has been around for years and still works if you want to copy the entire contents of a field from Produicts into a field in Invoice Data. The calculation option can be used to do the same thing, or you can set up a calculation that both copies the data but also manipulates it in some fashion--such as combining the contents of two related fields into one result.

              • 5. Re: Newbie question - trying to customise the invoicing solution
                LeanneHuff

                     well after initial progress,  I am completely flummoxed by this now.

                     I have changed the relationship between Products and Invoice Data to be Part Number (as this is unique) and on my Invoice I lookup the Part Number and this then auto populates item, colour, size, and unit price.  My problem then comes from when I change the quantity, the Amount column then updates to "?".  Please bear in mind that I haven't changed the Amount field calculation (that I am aware of) what do you think the issue could be?

                     The calculation is:

                      Let

                     [
                     total = Qty * Unit Price ;
                     discount = total * Discount Rate
                     ] ;
                      
                     total - discount 
                      
                     )
                      
                     The strange thing (for me) is that when I preview the print invoice - the line amount totals are there and are correct, and yet as far as I can tell it uses the same fields and the form is using.
                      
                     Secondly - any ideas how you get the display to actually look like how you have laid it out it edit mode - all my labels are over the correct item, but then when I exit layout - they are not aligned properly!
                      
                • 6. Re: Newbie question - trying to customise the invoicing solution
                  schamblee

                       If the number is wider than the field it will display a ?.  You can make the field wider or the font smaller.

                  • 7. Re: Newbie question - trying to customise the invoicing solution
                    LeanneHuff

                         Thank you so much for that - I thought I was going mad!

                         Now I just need to sort my layout issues - all the fields are set to left align - with the exception of the unit price and line total which are right aligned.  Any ideas?

                          

                    • 8. Re: Newbie question - trying to customise the invoicing solution
                      philmodjunk

                           Enter layout mode, select the portal by clicking the lower part of it (so that you don't click a field in the portal). Then, on the Inspector's position tab, clear the right "auto re-size anchor".

                           When you window expands to the right, your portal appears to be stretching to the right while your header text above it does not.

                      • 9. Re: Newbie question - trying to customise the invoicing solution
                        LeanneHuff

                             Thank you Phil - it all looks a lot better now!

                             Another question - I have added a field to the Invoice Data table - called Payment Ref.  On my invoice in the footer there are the details of how to make payment.  I would also like to include this payment ref field as this is unique to each invoice.  However, when I put this field in the footer section, it shows the label of the field but does not display the data.  It also does the same thing if I put it in the Trailing Grand Total section too BUT if i put it in the Leading Grand Summary section - it displays fine!  Any ideas on what to do to fix it?

                             Thanks

                             Leanne

                        • 10. Re: Newbie question - trying to customise the invoicing solution
                          philmodjunk

                               Invoice data is the name the developer gave to the "line items" table used to list records in your portal. If you want one such field for every invoice, you've defined this field in the wrong table. It should be defined in the Invoices table.

                          • 11. Re: Newbie question - trying to customise the invoicing solution
                            LeanneHuff

                                 Ahh thank you Phil - all sorted now!
                                  

                                 Many thanks for all your help - I have been adding calculated fields today too so the DB is pretty much doing what I need it to do for now.

                                 Thanks again - until next time laugh

                                 Leanne