4 Replies Latest reply on Apr 7, 2015 5:02 AM by philmodjunk

    I want to change Startup solution Invoices for my needs



      I want to change Startup solution Invoices for my needs


      Because I am at a so low level of experience in FM, I decided to try one of the Startup solutions - Invoices. If anyone is familiar with this solution, could they kindly help me with this?

      I need to change something for my needs. For example, I send one monthly invoice to customers and I need to include a some more details for each item, such as date of services provided, names of patients (not customers). I am talking about medical services. Where should I create this files? Would it be in the Table Invoices? Or Invoice Data?

      I tried both. When I did it with Invoices there appeared a complete mess. 

      When I created those fields in Invoice Data, it looked OK but the Dashboard  became Obsolete, does not show any records for invoices.

      What is the difference between the two tables Invoices and Invoice Data?

        • 1. Re: I want to change Startup solution Invoices for my needs

          You've asked for more information than can be provided in a single forum thread. The starter solutions, as I pointed out very emphatically to some folks from FileMaker Inc. when I got the opportunity, are tragically lacking in any documentation to help either the experienced or inexperienced user to understand the design of the layout, data model, calculations and scripts.

          This compounds your inexperience with the program. I suggest that you find a good training resource, this one from FileMaker is free: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

          There are also books and videos available.

          To answer one specific issue, I'll tackle Invoices vs. Invoice data.

          This part of the system is set up to document a typical sales transaction for goods and/or services. There is one record in Invoices for each sales transaction. But a given sales transaction may list multiple items purchased at the same time. (Think of going to a "big box" store with a shopping list, you get one sales receipt (invoice), but with many items listed on it.) There is one record in Invoice Data for each item listed in that sales transaction. For one trip to our imaginary store, that makes for one record in Invoices linked to multiple records in Invoice Data. When I create such a system, I prefer to name Invoice Data as "lineItems". It's more descriptive of what its intended purpose.

          So you might have one Invoice for a visit to a doctor's office or a single hospital admission but with multiple related records in Invoice data where each record records a different product supplied or service performed during that visit or admission. As an example, I patient might receive a flue vaccination and a chest x-ray on the same visit. You'd have one record in Invoices for that visit and two records in Invoice data that are linked to it--one for the vaccination and one for the x-ray.

          You, however, are describing a different business model--one were multiple services/goods are supplied over time and then all those provided in a given time period (one month) are listed on a single invoice. To do this without a major redesign of this database solution would require you to create the Invoice record on or before the first service or product is supplied, but then you'd use the portal to Invoice Data to keep adding more records in Invoice data throughout the month as this medical care is provided. So a date field defined in Invoices would identify the month in which the listed services were provided. A date field in Invoice Data would document the specific day on which that good or service was provided.

          • 2. Re: I want to change Startup solution Invoices for my needs

            Thanks PhilModJunk

            That makes sense. The Invoices is the parent table for Invoice Data. Invoices for the whole Invoice and the Invoice Data for particular items provided in each invoice. How I did not guess this myself???

            Now I need to understand why after I added a few additional fields to the Tab Invoices Data and created corresponding objects on the layout Invoices the Dashboard stopped working. The Object for list of invoices on the Dashboard (I think it must be a portal) is always empty now despite I have invoices which are visible in the other layouts

            • 3. Re: I want to change Startup solution Invoices for my needs

              Thanks  PhilModJunk

              for the book you recommended. I already had this book. It is very helpful. However I have one problem with this book and similar to it. All  the basics are designed for a complete novice in databases so that they contain a lot of contents trivial and I need to filter it in order to find what I really need and not to waist time. While I filter the contents I can miss something important. At the same time books for advanced users are hard for me. Why on earth someone would not write a book designed specifically for those who want to switch from MS Access to FM? In which I could for example take something which I did in Access and find out how to do the same in FM.


              • 4. Re: I want to change Startup solution Invoices for my needs

                There are many different training resources available. What works best for one person will not work well for another. You'll need to investigate your options to see what fits best for you.

                I would suggest that someone who is switching from MS Access to FileMaker represents too small a potential audience to be a likely one for training materials.

                But now that you mention that background, perhaps this tutorial on Table Occurrences will help: Tutorial: What are Table Occurrences?

                Also, this "term translation" may help. In FileMaker, Forms and Reports are replaced by Layouts. Sub forms and Sub Reports are replaced by portals. The underlying query on which you'd base a form or report is replaced by the relationships defined in Manage | Database | Relationships (See above tutorial) where the Table Occurrence on which you base a layout works like a SELECT * query with Left Join clauses linking it to the other tables set up as related in that Relationship Graph, but with no WHERE and no Order BY clauses. The WHERE clause is replaced by FileMaker's ability to perform a find and Sort Records replaces Order By.

                And here's a key difference. The Record Set in MS Access is replaced by the Found Set in FileMaker. In Access, you have to manipulate the form or report's underlying SQL query in order to change what records are found in the record set when you work with data on a given form or report. In FileMaker, the user just enters find mode and performs a find or uses sort records to sort them as needed. This makes the Found Set in FileMaker a much more dynamic record set than found in MS Access. A FileMaker Find makes simple searches of your data very easy while more complex queries can be more complex to implement in FileMaker than in an SQL based system like Access.

                And the function ExecuteSQL is a new feature first introduced in FileMaker 12 that gives you some limited ability to query a database using FileMaker's version of SQL.