1 2 Previous Next 19 Replies Latest reply on Feb 16, 2016 12:26 PM by siplus

    Tutorial Required : Custom Invoice Solution

    lcot17

      Hi -

       

      First of all, I want to stress that I have refrained from resorting to forum assistance all weekend whilst I work through online tutorials and videos! After exhausting 2 Lynda.com resources, I am still stuck!

      I want to learn FileMaker freelance so that I can create my own personal databases specific to me. One thing I do not want to do is use a starter solution! - nothing wrong with this, just that's not how I learn!

       

      So my issue -

      I want to create my own 'Invoices' solution

      I do have a few reasons for wanting to create my own - the most important is that I have more bespoke requirements.

       

      As I am still fairly new to database creation with FMP I am going to build a very basic version and expand my knowledge from there. Therefore, I am creating very simply a Customer, Product, Order database.

       

      I have the following tables:

      • Customers
      • Products
      • Orders
      • InvoiceLineItems

      Screen Shot 2016-02-14 at 21.44.45.png

       

      Creating layouts - fine.

      Creating a relational layout for an order page - Order ID, Customer Names, etc - fine.

       

      My issue is using the portal tool to create the InvoiceLineItems records!

       

      1. I create a portal showing related records from InvoiceLineItems. No sort, no filter and add in the fields very simply Product Name, Cost and RRP. I have then used the All Products table to create a list of product names to select on the portal. When I select a product, it will fill that field with the name but it won't fill the cost and RRP fields with the relevant information?

       

      Can anyone help me solve this issue and actually create a fully functional orders database. What am I doing wrong?

       

      FMP Version: 14 Advanced

      Mac OSX: 10.11

       

      Thank you.

        • 1. Re: Tutorial Required : Custom Invoice Solution
          beverly

          Kudos, for DIY (do-it-yourself). If you have not already done so, take a look at this:

           

               http://www.fmstartingpoint.com

           

          This may give you good ideas, even if you decide to not use 'as is'.

           

          beverly

          • 2. Re: Tutorial Required : Custom Invoice Solution
            lcot17

            Hi Beverly,

             

            Thank you for the kudos and the suggestion.

            I have actually taken a good look at the FM Starting point. Unfortunately, I am still unable to decode this specific issue with creating a portal to add products to an invoice...

             

            Are you able to assist further?

            • 3. Re: Tutorial Required : Custom Invoice Solution
              beverly

              Hello! I went back to your original post. I believe you need to use "lookup" for the fields in your lineItems (based on ProductID) to bring in the other values you need. Lookup will copy the contents, so you have a snapshot in time for the invoice. That way if things like prices change, the invoice will be correct at the time it is created.

               

              Look at the field definitions in the SP solution to see if that's how this was done.

               

              HTH,

              beverly

              • 4. Re: Tutorial Required : Custom Invoice Solution
                lcot17

                Hi again,

                 

                It does help - thank you for taking your time with me!

                Below I am going to talk my way through the process - please stop me if you find an error.

                 

                So, as I understand I am doing the following:

                Tables

                • Products
                • Customers
                • Orders
                • Invoice Line Data
                  • Item Name (text)
                  • SKU (text - lookup value from product table)
                  • Price (number - lookup value from product table)
                  • Cost (number - lookup value from product table)

                Relationships

                • Customers ----||- Orders
                • Orders      ----||- Invoice Line Data
                • Products    ----||- Invoice Line Data

                Primary Keys

                • Customers -> __customerIDpk
                • Orders -> __orderIDpk
                • Products -> __productIDpk

                Foreign Keys

                • Invoice Line Data -> _orderIDfk
                • Invoice Line Data -> _productIDfk

                 

                ...then

                assuming I have relevant fields for customers (name, company, email, address), products (item name, SKU, price, cost) and orders (order reference, sub total, status etc), I can create a layout for the orders page.

                 

                On the orders page I will have the main fields for Name, Company as well as Order ref, order sub total and order status (this is very very very simplified). Creating a new order record will create a new and unique order ID. I can select the customer name which will in turn complete the field for company. I can enter the order ref and set the order status. DONE! - fine.

                 

                Now on to the bit where I can't resolve - adding items to the total so I have line items to add and calculate to generate a order sub total. To remain simple, I am going to omit using a QTY field.

                 

                Problem area!

                • On my order layout I create a portal window.
                • Show related records from 'Invoice Line Data'
                • Check allow deletion and vertical scrolling
                • Add fields from 'Products' table
                  • Fields Product Name, SKU, Price and Cost
                • OK.
                • Create a drop down list for the Item Name field in the inspector taking values from the product table.
                • Browse mode - problem. Can't select products. Doesn't work how i'd of expected it to, or like the starter examples.

                 

                If I change step 4 (and add the product name from the Invoice Line Data table and get drop down values from Product table, I can see the products list down when I click, but this does not auto fill or prompt a lookup for the other fields.

                 

                What I believe I am doing

                From what I gather, I think I am creating records in the Order Line data that is saying in Order ID = 1 has a link to Product ID = 1 with a specific price / cost record.

                 

                What it is NOT doing is autofilling the other records with the data from the product table when the product name is selected. Is this a script behaviour? It is also not creating records correctly in the InvoiceLineData table.

                 

                What am I doing wrong? How do I correctly configure the portal to be able to add/delete products on an order record layout? Also, how do I configure it to be able to (like you said) lookup values to copy contents so that if I did want to change the price, I can do there and then without affecting the price record globally for that product.

                 

                I am getting closer, I can feel it. I just need these last few questions answering.

                 

                 

                 

                • 5. Re: Tutorial Required : Custom Invoice Solution
                  siplus

                  zip and attach your database so that we can take a look.

                  • 6. Re: Tutorial Required : Custom Invoice Solution
                    lcot17

                    Didn't know I could do that!

                    Quick database mockup is attached

                     

                    Layout is very basic as I'm just focussing on learning the functionality but this is what i've got so far!

                    • 7. Re: Tutorial Required : Custom Invoice Solution
                      siplus

                      here some fixes.

                      • 8. Re: Tutorial Required : Custom Invoice Solution
                        lcot17

                        That is great!! Thank you so much - I will dissect this and try to re-create what you did from scratch tomorrow! Thank you, it is a lot easier to have something this basic and stripped back to work from.

                         

                        The final piece to my puzzle now however is how do I create the following behaviour.

                         

                        If I change the unit price in the order layout portal (invoice), how can I STOP it changing the price of that product globally?

                         

                        Currently, if I add a T-Shirt for example with a price of 30. If I change this value in the order layout it will change the price of that product. I do not want this to happen....

                        However, the cost field for example should always stay the same and any changes can reflect the product globally.

                         

                        An example of why I would need this behaviour:

                        The cost of the product will always stay the same but I may charge a lower price for the product as it depreciates. I may also charge different prices for different customers. I will be adding a discount field in but this is something I would like as well as the above behaviour.

                         

                        On this note though, would this have any other limitations? For example, if I wanted to create a report/graph for how many units of a specific range of products have been added to an invoice?

                         

                        I know I have written a lot above but on a final note, I can help simplify your answer.

                        Do I answer my own question here?...

                        To stop 'changing the price field' on the invoice form changing the price field globally - do I simply change the 'specify field' for the price to change from the Products table to the OrderLineItem table? if that is correct, i'll be happy with a 'yes that is right'.

                         

                        But also, my second question still applies about what implications this may have?

                         

                        Thank you again for your help!

                        • 9. Re: Tutorial Required : Custom Invoice Solution
                          beverly

                          You need a field in the portal that is a field to store the price-in-time. That means the definition of the field is a Lookup based on the relationship that brings in the product information.

                           

                          https://www.filemaker.com/help/14/fmp/en/html/relational.12.20.html

                          https://www.filemaker.com/help/14/fmp/en/html/relational.12.21.html#1029032

                           

                          beverly

                          • 10. Re: Tutorial Required : Custom Invoice Solution
                            lcot17

                            I get it! These links helped me clear up any misconceptions

                             

                            So working through the script that adds the products into the portal (thank you for this), it sets the variables for product ID and order ID, adds these into a new record in the OrderLineItems table which in turn triggers the lookup defined in the field options. This then copies the data from the product table into this table. Finally brings me back to the order layout to then specify the qty.

                             

                            If I want to create a report for how many units sold for a product for a specific month I can create one by looking at the product ID, name and counting the qty's set in the OrderLineData table for each unique product ID?

                             

                            Is that right?

                            • 11. Re: Tutorial Required : Custom Invoice Solution
                              BruceRobertson

                              Not quite.

                              You didn't mention how you would constrain the found set to one month.

                              While in a layout based on OrderLineData, you will have to perform a find on the related (parent) order table based on the month/year you wish to report on.

                              Thinking like this:

                              "Show me all the orderLine records for product X where the associated sale occurred in January 2015"

                              • 12. Re: Tutorial Required : Custom Invoice Solution
                                beverly

                                If I want to create a report for how many units sold for a product for a specific month I can create one by looking at the product ID, name and counting the qty's set in the OrderLineData table for each unique product ID?

                                 

                                I tend to have a field (text) that is auto-enter and based on the date I'll need in reports like this:

                                     = Year(myDate) & "_" & Right("00" & Month(myDate) ; 2)

                                 

                                You can omit the "_" but it's easier for me to read. This field is "alpha-sortable", can be used in summary sorts, can be used in portal filters, can be used to group for charting, ...

                                 

                                The YYYY_MM format and also be searched as long as you enter the same format, as well.

                                beverly

                                • 13. Re: Tutorial Required : Custom Invoice Solution
                                  BruceRobertson

                                  Here'a mod to siplus' file with YYY_MM dates included.

                                  • 14. Re: Tutorial Required : Custom Invoice Solution
                                    lcot17

                                    Thank you Bruce and Beverly - i've taken a look at your mod and implemented the above myself to a different database I started from scratch last night works perfect! - looks like i'm learning! I'm going to keep building again and again so the knowledge sticks! I did the same with PHP/MySQL and it kinda worked!

                                     

                                    Is it too cheeky of me to ask just another question whilst I've got your attention?

                                     

                                    Based upon your experience of designing database architectures on FMP, what would be your best and most efficient solution to the following?

                                     

                                    It may be clear now that I am in the clothing business. When it comes to entering data for a new product, there are a lot of different variables/options that a customer can select in an order.

                                    • Product table consists of individual products (e.g. Viscose Pocket Tee)
                                    • Individual Products have a model # (e.g. SGC16001)
                                    • Each model # may have a number of colour ways (White, Black, Khaki) each with their own SKU #
                                    • Each colour way has a variety of sizes (Small, Medium, Large, XL) each with their own SKU #

                                     

                                    However, the cost or price of each variation may be different in certain cases. For example, the cost of two colour ways may be different but not the RRP. Entering each product and separating the colour ways into separate products is an option, but obviously a different product per size will require a lot and a lot of data entry. Far too much with a large catalogue.

                                     

                                    Taking into account - I would eventually like this to work with inventory/purchase orders where I will be able to create a PO and specify the qty levels of each individual product and each individual option. Additionally I would like to make it as easy as possible to enter into the invoice form - like below maybe?

                                    Invoice Portal Option?

                                    Product IDItem NameModel #ColourwaySMLXLQTY TotalUnit PriceLine Amount
                                    IDAdded from the portal table on exampleLookup FieldDrop down option based on lookup??QTYQTYQTYQTYSumLookup FieldSum
                                    1Viscose Pocket TeeSGC16001Khaki24421230360

                                     

                                    This would be very similar for the PO form where I could still select the colour way from a variable list depending on what is set in that product and then the quantities purchased with the price paid.

                                     

                                    Now my question - is my suggestion above good? Would it work? Or is there a better way to do this for the products?

                                     

                                    Would it be possible to show me the table/relationship structure about how your best suggestion would work so I can try implement?

                                     

                                    p.s. I PROMISE, I am not slowly getting you to build it all for me

                                    1 2 Previous Next