5 Replies Latest reply on Jun 24, 2016 10:31 AM by tcr

    How to create a database which allows for searching within specific dates

    tcr

      Hi,

       

      We're a bit rusty with databases and we're struggling with creating the following - partly because of not knowing the terms we should be looking for in the manual!!

       

      To over-simplify what we do, we sell 10 different types of apples, and we get paid for these apples every few months, and each month each type of apple is paid a different amount.

       

      We've set up the fields so that each type of apple is a record, containing all of the information needed about its background, date, owners etc. We've then made a separate layout which contains part of this information, plus new fields for each payment (with each field being named per the invoice number, and the contents being the amount paid).

       

      How do we assign dates to each of these payments so that we can search for payments within a specific date range? ie, May2014-June 2016, or 2015, or... We'd need to do this on a whim rather than having specific code pre made.

       

      With this done, will we then be able to search for a sub category within these dates, ie green apples only?

       

      Any links to help videos or pages in the manual would be great if it is something that is a bit too complicated to explain over the forum.

       

      Many thanks,

       

      Nick

        • 1. Re: How to create a database which allows for searching within specific dates
          SteveMartino

          Can you show a screen shot of your relationship graph and the layouts in question (both browse mode and layout mode)?  Also can you describe the work flow as it relates to the tables.

          Basically, every payment should be a record on a payment table, which should be a join table between products and sales (customers).

          Then searching would be standard.

          There are plenty of resources.  If I remember correctly, the book-FileMaker Pro 12:  The Missing Manual has a simple invoicing solution that takes into account partial payments.  I don't know which version of FM you are using or if it's a newer version of the book if the same sample file is there.  But usually if you go the website, you can download the sample files.

          • 2. Re: How to create a database which allows for searching within specific dates
            coherentkris

            Searching for a range is a common find technique that can be done with scripting or by a user in Find Mode

             

            Finding ranges of information

             

            Finds are an essential technique and deserves every bit of effort and time it takes to master.

            • 3. Re: How to create a database which allows for searching within specific dates
              CreativeFM

              The trick is to use elipsis. Here's an example file of how I typically do it.

               

              SearchingDates.fmp12.zip - Google Drive

              • 4. Re: How to create a database which allows for searching within specific dates
                siplus

                I would suggest a dashboard table and layout. The table has only one record and the fields are 3 globals, for now: gDateBegin, gDateEnd and gAppleTypeID.

                 

                There are 2 relationships from dashboard to payments, one is based only on the 2 gDates, the other one includes the appleType.

                 

                You have 2 portals on the dashboard, each based upon one of the 2 relationships.

                 

                You completely hide one of the two depending on gAppleTypeID being empty or not.

                • 5. Re: How to create a database which allows for searching within specific dates
                  tcr

                  Thanks very much everyone. I think we've bitten off a bit more than we can chew with this question as we're falling down on even inputting the data in the first place!!

                   

                  We've bought the missing manual for Filemaker Pro Advanced 14 (the version we have) but its hard to find the right sections if we don't know the terminology!

                   

                  If it is ok to stick with this thread, our new question is:

                   

                  We're inputting sales for each apple month on month, and have created a list view layout to see all apples in alphabetical order (we've got hundreds of differently names apples so a dropdown is not an option) to find them easily and quickly. It makes sense to input a new date and payment amount in this view, however we're only seeing the very first payment and date for each apple, and if we change it it changes the original record instead of adding a new one.

                   

                  We have successfully added records via a portal to a payment table, but having to go into each individual record to do it is too time consuming, especially as the apples were not added to the database in alphabetical order (and is ongoing, so new apples are added all the time).

                   

                  Have we set up the link to the payment field incorrectly?

                   

                  Thanks very much,

                   

                  Nick