13 Replies Latest reply on Sep 27, 2011 1:14 PM by user15130

    Extracting Data From Portal

    user15130

      Title

      Extracting Data From Portal

      Post

      Hello,

      I have a portal 'see attached' that has several line items.  Its like a check register but tracks inbound and outbound inventory items.

      What I want to do is extract only items that match the 'MONTH' and 'YEAR'. So as you can see there are several 'September' and 'October' and 'November'.  I will add the 'YEAR' date calc later (The month and year are uto enters from date field).  I just forgot to add it.

      What I am looking for is the total increases of each month.  So for September 2011 it would be 4,600 units and so on and os forth.  Seems easy but boy am I having difficulty with it. 

      I guess I would like the results to filter a portal that way as the the months increase in the register and the years increase it does not break and the results for each month/year are line items in another portal.

      Thank you.

      Marty

      Screen_shot_2011-09-22_at_11.52.37_AM.png

        • 1. Re: Extracting Data From Portal
          philmodjunk

          Does it need to be in a portal or could a list view type summary report where each month/year is a row in the report with totals work?

          • 2. Re: Extracting Data From Portal
            user15130

            The portal would be better but I would settle for a report. :)

            Marty

            • 3. Re: Extracting Data From Portal
              philmodjunk

              The report is easier to set up. I'm not fully sure I picture accurately what the portal would look like. One row in the portal for each month perhaps?

              As a summary report, you can use a summary field to total up the values in Increase. (and other summary fields for other totals if you need them.) Then create a layout where the body layout part is deleted. Replace it with a sub summary part "when sorted by" cMonth.

              Define cMonth as a calculation field returning Date as its return type:

              date - day ( date ) + 1 //value returned is date of first day of same month

              You can sort your records by this field and they will both be grouped by month and also arranged in chronological order.

              To set this up in a portal, I think you'd have to add a table where you have one record for each month/year then link it in a relationship by a date field in this new table to cMonth in your portal's table. Then you can put the summary fields I described earlier from your original portal's table in the portal to this new table to see monthly totals.

              • 4. Re: Extracting Data From Portal
                user15130

                OK.  :)

                 

                Let me work on this tonight and I will get back to you.  Thanks a bunch. Seems simple enough... :)

                 

                Marty

                 

                • 5. Re: Extracting Data From Portal
                  philmodjunk

                  BTW, the summary report layout would be based on the portal's table not the table of the layout shown in your screen shot...

                  • 6. Re: Extracting Data From Portal
                    user15130

                    Yes got that. :)  Ok it works but here is another problem.

                     

                    There is a field on the main layout say field:Amount ($80) and I want to multiply by the summary field of the total units for the month of September. I created another calc field Amount*SummaryFIeld but it does not total properly. 

                     

                    Where is the issue? 

                     

                    Thank you Marty

                    • 7. Re: Extracting Data From Portal
                      philmodjunk

                      You'd need a relationship that links only to records from the month of September or you'd need to do this inside a filtered one row portal that filters out all records except that month in order for it to total correctly. With the second option, the Amount field might need to be a global field in order for it to compute correctly.

                      • 8. Re: Extracting Data From Portal
                        user15130

                        Not sure I get it.

                        That sounds dificult to do. I have the reoport in list view and it works but adding the calc for two different fields acrossed summarize fields from a polter sounds like its beyond filemaker unless you want to jump through hoops to get it.

                         

                        Sound like a case of HOW IMPORTANT IS IT?  Right?

                         

                        Marty

                        • 9. Re: Extracting Data From Portal
                          user15130

                          The other issue is on the main layout that contains the portal I have a button that takes me to the list view report, except that it takes all records from the main layout into consideration and all portal records.

                           

                          I only want the current record and those related portal records to be viewed.  So it is a find of sorts on the main record being viewed.  How is this accomplished?

                           

                          Marty

                          • 10. Re: Extracting Data From Portal
                            philmodjunk

                            In the case of both your questions, much depends on your relationships, the table structure and the design of your layout.

                            You can use go to related reocords to bring up a found set of the portal's records on a layout based on the portal's table. It is usually important to either check for the existance of related records before executing the Go To Related Records step or you use get ( LastError ) to check for an error immediately after the GTRR step. OTherwise Filemaker will not change layouts, but continues to attempt execution of the remaining script steps which can results in some really messy results sometimes.

                            Your script can also perform a find to pull up the same set of records also.

                            • 11. Re: Extracting Data From Portal
                              user15130

                               

                              Nope GTRR does not work as I am doing it.

                               

                              When I am on the current record and I click the script with GTRR as first step then go to the other layout which is based on the portal it does not bring over the records within the portal on that record and then sort them.  It does something very different.

                              Somewhere I am missing a part and that is the complicated part. :)

                               

                              Maybe a find is the better way to go... How might that work?

                               

                              Marty

                              • 12. Re: Extracting Data From Portal
                                philmodjunk

                                It does something very different.

                                What exactly does it do?

                                It won't sort them unless you either specify a sort order in the relationship for the portal or add a sort step to your script, but it should bring up the records.

                                Please post exactly what you have specified as options in go to related records. This step has a large number of options and even one set incorrectly can affect the results here.

                                • 13. Re: Extracting Data From Portal
                                  user15130

                                  THANK YOU.  I got this one... :) Yes you were right on the GTRR.

                                  Marty