11 Replies Latest reply on Dec 16, 2016 2:32 PM by tleitzke

    create Send Mail message with multiple lines and variables

    tleitzke

      I am trying to make a simple inventory system, where the "order" and "quick order" buttons send an email a message with the parts, number needed, and total estimated cost. However, I am having difficulties putting more than just a string OR database variable in the message. The email does send with either string or database, but not mixed or multiple lines. I am coming from a background of real programming (C#, C, and C++)- but this scripts thing is entirely new to me and lacks any real online resources.

        • 1. Re: create Send Mail message with multiple lines and variables
          philmodjunk

          What you describe is pretty straight forward. The "body" box in Send Mail can be treated as a calculation and this calculation can combine data from fields, variables, etc in a number of ways. You just click the button with the black triangle to the upper right of the Body box to get FileMaker's standard calculation dialog. Both List and ExecuteSQL can be used to pull together data from multiple records in another table. The "list of" summary field can pull together data from your layout's current found set for use in such a calculation.

           

          However, the result is plain text and any kind of tabular data included in the email body may not display the way you want it in all cases due to that built in limitation. Thus, you may want to consider alternative approaches:

           

          Generate a PDF of the order and attach it to the email. This allows you to use all of FIleMaker's layout tools to produce data in a clear, easy to read format.

           

          Consider getting a plug in that enables you to send out HTML formatted emails. These can allow you to set up, for example, an HTML type table of your information by combining HTM table tags with the data from your table(s).

          • 2. Re: create Send Mail message with multiple lines and variables
            tleitzke

            HTML is disabled on outlook365 for me, so would provide nothing useful in generating a neater or easier to read format. I will attempt the PDF generation, but I have never used it before (I am still fairly new to Filemaker, but I am getting around fairly easy in layout tools areas).

            • 3. Re: create Send Mail message with multiple lines and variables
              tleitzke

              I have also had trouble trying to figure out how to actually make a list for order. So far found nothing to create an array, or dictionary, for holding the records needed (which would be a simple 2-d array database-location and how much to order). I did create a database table for just creating a list of items to order- but not sure how to grab that database pointer/variable for the item's location (so I don't have to store every piece of information in this new database)

              • 4. Re: create Send Mail message with multiple lines and variables
                philmodjunk

                What is your current set up for this?

                 

                A pretty much standard way to handle orders in a relational data base is to use three tables with the following relationships:

                 

                Orders----<Line Items>------Products

                 

                Orders::__pkOrderID = LineItems::_fkOrderID

                Products::__pkProductID = LineItems::_fkProductID

                 

                If a person places an order for 2 widgets and 3 thingamajigs, this is recorded with one new record in Orders and two new records in Line Items that link by Order ID back to orders. Additional info about each ordered product such as the current unit price comes via relationship from a matching record in Products.

                 

                In FileMaker, a data entry layout for recording new orders is usually based on Orders with a portal to Line Items for recording the actual items ordered. For printing and saving as PDF, a good option is to use a list view layout based on Line Items that includes fields from Orders in the header, footer or grand summary layout parts. Data from line items and Products would be shown in rows listed in the body. This format allows a single order to easily span as many or as few pages as you might need.

                • 5. Re: create Send Mail message with multiple lines and variables
                  tleitzke

                  Currently, I set up 3 databases. Products, Order List, and Order History (which I assume I can store the generated PDF into the database using a container). I am using the original inventory base, which I modified the screen that produces the list of all inventory items to have a field for how many should be ordered of that item (which a require on hand next to it). Then the user will scroll to the bottom to "submit order" which then emails the generated PDF (which I figured out how to set one up). The items with not-0 get added to the order list, which is then put into the generated PDF file (which then that list gets entirely cleared out).

                  • 6. Re: create Send Mail message with multiple lines and variables
                    philmodjunk

                    Not how I'd do it. Keeping a record of each order--which is done via the Orders and LineItems tables, would seem a pretty important part of the process. Yes, you can save a PDF, but then you can't generate reports from your past orders so that seems pretty limited.

                    • 7. Re: create Send Mail message with multiple lines and variables
                      tleitzke

                      I am just getting lost in this. Filemaker's original inventory system works on selecting every item and typing how much is left, and then generating an entire list summary of everything on the inventory-which is not useful for the case needed.

                      • 8. Re: create Send Mail message with multiple lines and variables
                        philmodjunk

                        That's because it's a system for tracking inventory, not ordering products. I assume that by "FileMaker's inventory system" you mean a starter solution file that came with FileMaker. These files are intended more as examples of what is possible in FileMaker rather than as tools you can just start using. They tend to be very "single task" oriented and thus usually fail to be the complete solution for what a business needs. They are also sadly lacking in documentaition which can make them hard to figure out even for experienced developers. I pointed this out directly to one of the people responsible for these files in a face to face conversation as well as by email to several other "contacts" that I have inside FileMaker, but to no avail.

                         

                        These starter solutions are far from the only option. What I've diagrammed can be found in the Invoicing starter solution. Both selling and purchasing products can be set up from the same basic data model. The main difference is whether you are spending or earning money.

                         

                        Changes to inventory can be driven directly from the line items table as this table can serve as a "log" of every item ordered and received much the way a check register can record deposits and withdrawals.

                        • 9. Re: create Send Mail message with multiple lines and variables
                          tleitzke

                          I loaded up the invoices starter solution, and it seams like a good piece to produce off of- but I can't seam to grasp a lot of it to replicate it based on my needs. The invoice itself, where it contains lines for items is something more what I am wanting than compared to the inventory management starter solution.

                           

                           

                          To be more specific for the needs, because I may be messing myself up and you may have a better idea of what I need. I work at a small HVAC business, and we need to control our inventory more in order to save money. Each van has its own little inventory, which needs to be tracked daily. And along with that, I need to be able to receive orders from them when they happen to run out of parts or items when on the job- or to get orders for jobs ahead of time while they are out.

                          • 10. Re: create Send Mail message with multiple lines and variables
                            philmodjunk

                            What you describe is still something for which I would use the data model that I have described here.

                             

                            For a more detailed view of how I'd do this, see here:

                             

                            Managing Inventory using a Transactions Ledger

                            • 11. Re: create Send Mail message with multiple lines and variables
                              tleitzke

                              Thank you so much for these tutorials. I have only gotten through, most of, table occurrences (which is just like object-oriented programming). I am a little stuck with the  final part (making Fax appear from [contactID = contactID && cFax = PhoneType]. With a little bit of thinking and re-reading , I was able to understand it fairly well- and it is pretty helpful. I also find that industry standard list to be helpful, as it also related to how normal programming is done.Capture.PNG

                               

                               

                               

                              Edit: I have been able to create a base inventory system so far using 4 databases (Orders, Inventory(van1, van2, shop...ect), Products, and seller (Some items have multiple sellers, and so both- or more- need to be defined WITH that seller's product ID for that product). I am still trying to actually figure out how to separate/reorder the item list  when the wanted inventory is chosen.