Selecting a specific portal from within a script

[dougL]

Hi, I have a layout with 2 portals on it.

I'm trying to write a script which loops through the second portal but when I use "Go To Portal Row", it automatically takes me to the first portal.

 

If I click on a field in the correct portal right before I run the script it works fine but I'm wondering how I can specify which portal I want to use from within the script?

 

I'm fairly new to fileMaker. Thanks in advance for any help

 

 

[bruceR]

The immediate answer to your question is that you need to go to a field or object that is specific to the portal; then loop.

(As noted by Rick, this can be the portal itself if it has been given an object name)

 

The other relevant question is - especially if you are new to FileMaker features and best practices - what is the purpose; why do feel you need to do this?

[Rick Whitelaw]

You need to name the portal in the inspector and then GoToObject. Sorry, I'm on an iPad and don't have access to the proper syntax.

[dougL]

Thanks for the quick responses! Much appreciated.

 

GoToObject worked perfectly!

 

Bruce, I am writing a script to compile data from several of the fields in the portal.

For example there is a "category" field and an "amount" field and I want to get a total of all the amounts with a category of 4.

So I'm looping through the portal rows looking for category = 4 and then adding the amount to my total.

Is there a better way to do this?

[bruceR]

Before answering the question I will ask another.

What happens next with the category 4 total?

Once you know the value, what do you do with it?

Display it? Report on it? Answer a live phone call question?

 

There are many other ways to extract this kind of information.

Are some of them better for this particular purpose? Maybe?

Are the other methods something you should learn about, add to your "toolkit"? Yes.

 

For instance:

Using a filtered portal, so that a user can choose to only see the category 4 items. (Then choose to see category 5; all; etc)

Using the list function to extract a set of related values and using a script to process the matching totals.

Using a custom function to process the related data.

For instance 

http://www.briandunning.com/cf/1367

 or

http://www.briandunning.com/cf/894

Using a standard subsummary report.

With Filemaker 12, use the ExecuteSQL function to get the total.

With versions before 12, use an SQL plugin to get the total.

Etc.

[comment]

In any case, if you find yourself in a situation where a loop over the related set is unavoidable, you should do it in the related table itself rather that in the portal. Portal is a layout tool designed for the users. As the developer, you should always strive to work directly at the data level.

[dougL]

The result is used as part of a report.

It's actually a bit more complicated than just the totals of category 4. I had simplified it but basically there are 3 or 4 conditions that need to be met in order for the Amount data to be used in the total.

 

Here's a more detailed description:

The layout has customer data (name address, etc) and then a portal to payment data  for that customer (date of payment, category, method of payment, amount, running balance).

 

At several points during the year, a user has to be able to run a report on these payments which includes totals of certain categories and methods over a given time period (for example: all payments in categories 4,5 and 6 during 2012, paid with credit card)

Then that total is included in a spreadsheet.

[dougL]

I should add that at this point I don't have a Filemaker Report set up, I just export the data to a spreadsheet.

[comment]

 a user has to be able to run a report on these payments which includes totals of certain categories and methods over a given time period (for example: all payments in categories 4,5 and 6 during 2012, paid with credit card)

 

It sounds like it would be best to produce the report from a layout of the child (Payments) table. Find the payments you wish to include in the report (e.g. payments in the given time period, within the given categories and having the given method of payment), sort them by CustomerID and use a sub-summary part by CustomerID to show the sub-summary values.

 

You can also select Group by: [CustomerID] when exporting (provided records are so sorted).

[dougL]

THanks, I will see if I can implement it directly from the payment table as you're suggesting.

Also, I'm curious if the advantage of doing it this way is greater efficiency in accessing the data or if there are other concerns with doing it through the portal?

I'm really appreciating all the help.

[comment]

You cannot do a find or a sort through a portal. A portal will always show all related records (or all related records except those that do not pass the portal filter) in the order set in advance in the portal's setup. In addition, portals do not always print well across page brakes. These are the main reasons why you should always try to do the reporting from the most atomic table.

[Rick Whitelaw]

Comment is correct about the printing of portals. Of course it can be done and page breaks dealt with, but it's a lot of picky work to set up. A portal on a page must be strictly limited in terms of the number of rows etc and extra records flow onto the next page into the same portal with the next record numbers. It goes on. There are easier ways.

Edit: then of course printing should be scripted to print the correct pages based on # of records . . . Eg. <16 one page, >15 and <31 twopages. Best to avoid. I didn't and spent many hours making the print scripts work.