1 2 3 Previous Next 35 Replies Latest reply on May 14, 2012 11:40 AM by philmodjunk

    Reports: Help in Creating and Displaying Multiple Search Criteria

    MrMunchkin

      Title

      Reports: Help in Creating and Displaying Multiple Search Criteria

      Post

      Hi All,

      I am new to filemaker (12) and would appreciate if you could offer some advice on an area I am having trouble with when creating a report.

      Backgroud:

      We track ship cargoes. Tonnage coming in and tonnage going out. I have two tables. Ships and Tickets. This is a one to many relationship with ship name being being the link between the two tables. 

      SHIPS

      Ship Name

      Date

      Cargo Type

      Tonnage

       

      Ship Name -(one to many)- Ship Name

       

       

      TICKETS

      Ship Name

      Sales Date

      Product Type

      Tonnage

      Customer

       

      Each sale we make in TICKETS is tonnage reduced from Tonnage in SHIPS until the ships Tonnage reaches 0.

      Each of the SHIPS Cargo Type can be broken down into different product types. For example, a SHIPS cargo type could be stone rubble and when we sell this on the TICKETS Product it could be broken down into Small Size, Medium Size, Large Size.

      We need to create a report which tells us a % breakdown of Small, Medium, Large size in each Cargo. For example, 20% of the cargo is Small, 50% is Medium and 30% is Large. I have managed to do all these calculatuions.

      Problem:

      My problem arrises when we produce a weekely report by date. Each week we produce a report showing the % of the Product Type for the sales of the specified week sorted by Ship Name. I have managed to do this. It looks similar to this.

      Ship Name:A

      Product Type:Small = 20%

      Medium = 50%

      Large = 30%

      In the same report we need to show the % of the Product Type for the whole ship (not just for the selected dates) as the whole ship cargo breakdown may be a different % to the weekly breakdown. I cannot figure out how to do this. I know it should look something like this.

      Weekly Date Defined

      Ship Name:A

      Product Type:Small = 20%

      Medium = 50%

      Large = 30%

       

      TotalTotal For Whole Ship

      Ship Name:A

      Small = 10%

      Medium = 63%

      Large = 27%

       

      I think that I need to create a column in the report to enable me to get a left side (weekly date defined total) and right side (total for whole ship). I am unusre how to do this.

      I also don't know how to create a script or calculation to include all the Tonnage we have sold from the Ship when we have defined a selected ship to report and a specified date. If I search only the ship name I can get the whole ship tonnage. If I search by ship name and a specified date period I can get the weekly product % breakdown. I cannot find a way to get them both to display in the report at the same time.

      I tried to create a script which omits the date search criteria for the ship, im not sure if this is the correct way to solve the problem, it is probably due to my newness to filemaker but I could not figure out how to get it to work. We have many different ships, and when I created the script it seemed to only select one pre designated ship which I could not change when I wanted to search a different ship. As I had problems trying to implement this script, I am not even sure if it would give me the result I am looking for.

      I would be extremely grateful if anyone could offer any help or solutions.

      Thanks

        • 1. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
          philmodjunk

          I'm not sure that I understand this sentence:

          Each sale we make in TICKETS is tonnage reduced from Tonnage in SHIPS until the ships Tonnage reaches 0.

          Does this mean that you update the Tonnage field in SHIPS, reducing each time some of the cargo is sold?

          If so, why? A calculation field in Ships can calculate the total tonnage sold and subtract it from the original (and now unchanging tonnage total) to

          determine the amount of unsold cargo remaining.

          I may be misunderstanding your post, but that seems to be the issue keeping you from getting both sets of figures in one report.

          PS. I think you need more tables so that a ship can carry different cargoes on different dates. Also using a serial number field as a ShipID is a better option for linking Ships to Tickets than the ship's name.

          • 2. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
            MrMunchkin

            Hi Phil,

            Thank you for your quick response and reply to my query.

            To answer your question first:

            For our SHIPS Table

            A Ship can carry one cargo, eg Rubble, with a large tonnage eg 10,000 tn, it can only come in once on a single date. There may be about 12 different ships on file, each with different names, each with different cargo, each with different tonnages. An example may look like.

            Ship Name: Santa (unique field) (set up as one to many relationship with Ship Name in TICKETS)

            Tonnage: 10,000

            Cargo Type: Rubble

            Arrival Date: 24/12/12

             

            For our TICKETS Table

            Tickets are for many small tonnages which are taken away from the larger Ship tonnage. The TICKETS Table is the one we use most. There may be 1,000 entries in the TICKET Table for each ship until the Ship Tonnage reaches 0. There are many different dates, many different customers, many different tonnages, different sub categories of the Cargo Type (such as small, medium, large). An example may look like.

            Ticket Number: 12345 (unique field)

            Tonnage: 10

            Date: 25/12/12

            Product Type: Small

            Ship: Santa (unique field)

            Customer: ABC

             

            After I enter this ticket, the Ship in SHIP Table will change to the following. Notice the tonnage change.

             

            Ship Name: Santa

            Tonnage: 9,990

            Cargo Type: Rubble

            Arrival Date: 24/12/12

             

            I have set up the calculation between Tonnage in TICKET Table and Tonnage in SHIP Table to automatically reduce eveytime a Tonnage is entered in TICKET Table. This is working well and is causing no problems.

            From what I understand, I think my problem is that when we create the report for the week, we select a Ship Name and a date range. Doing this I can report the correct % totals for each of the sub categories for the cargo type from the ship name i select and the date range I enter.

            However, I cannot at the same time report the % totals for just the entire ship which are not limited to the % sold during only the sales period defined by the date range I have set.

            It is like because I have selected a date range, it will not allow me to report on at te same time for the entire ship because it is being limited to the date range I have set.

            I hope this helps explain better.

            Thanks again

            • 3. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
              philmodjunk

              I need to know what exactly you did here:

              I have set up the calculation between Tonnage in TICKET Table and Tonnage in SHIP Table to automatically reduce eveytime a Tonnage is entered in TICKET Table. This is working well and is causing no problems.

              A calculation can't reduce a stored number so it looks like you may have two fields where you have described one: A field that records the total tonnage loaded onto the ship prior to any sales of it's load and a calculation field that computes the total remaining. Either that or you are using a script to update the tonnage field in your Ships table. If you are using a script, it may be computing the available tonnage correctly, but it's destroying a useful figure that can be used to compute your percentages. Two fields--one that is a number field you enter when the Ship Record is first created and one that computes the total unsold tonnage.

              PS. From what you have posted, ID number still may be a better choice than ship names. There are several complications that can arise from using names as match fields in a relationship that are avoided when you use an ID number that never changes instead. Also, what will happen with your relationship when the same ship transports a second shipment? Your relationship will then match to tickes for two shipments instead of one--which is why I am suggesting that you need a second table for documenting each time a ship transports a new cargo for you.

              • 4. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                MrMunchkin

                Hi Phil,

                Thank you again for your help and time with my filemaker problem. I have spent a little bit of time creating a basic version of my database to upoload to this post. I am also new to the forum and am not sure if this is allowed. I hope so as it will make things easier. I have also filled the file with some sample data that I made up for illustrative puropses.

                Kind Regards

                • 5. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                  philmodjunk

                  You cannot upload a database file to this forum, but you can upload it to a file sharing site and post the download link to it here.

                  • 6. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                    MrMunchkin

                    Oops, looks like that didn't work.

                    Ok, here are some images of the reports I have made so far.

                    The first image (MrMunchkin Report Date Selected) is a screenshot of a PDF from my report when I select a ship name and a date range search.

                    The second image (MrMunchkin Report Ship Selected) is a screenshot of a PDF from the same report but only with a ship name search criteria. Notice there is more data in the report as the search/find criteria is much wider.

                    The third image (MrMunchkin %1) is a different layout which shows the tonnage total and % total for the ship and date selected report. 

                    The fourth image (MrMunchkin %2) is the same layout as above but shows the tonage total and % total for just a ship search (not date restricted)

                    Lastly the fifth image (MrMunchlin Desired Report Result) is a something i have created by mashing a few images together. It shows how I am trying to get the report to look like. Basically it shows the information from the first image, it also shows the information from the third and fourth image on the same report.

                    I hope this helps understand my desired result.

                    I will also try to post some images of my database and relationship tables.

                    • 7. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                      MrMunchkin

                      It looks like i need to post each image individually

                      • 11. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                        philmodjunk

                        What's the difference between "ship selected tonnage" and "date selected tonnage"?

                        Are you able to get the calculations shown individually but just want to combine the three reports as shown on the last image?

                        Please note that none of this shows me HOW you produced the reports. There is more than one way to do so and understanding your current design can help me to suggest a way to get to where you want to go if it is possible to do so.

                        Off hand, a pair of portals in trailing grand summary seem possible, but care would need to be taken to create just the right supplimentary relationships to compute the needed values shown.

                        • 12. Re: Reports: Help in Creating and Displaying Multiple Search Criteria
                          MrMunchkin

                          Please see the next images for my database design

                          1 2 3 Previous Next