Range Of Date Filtering

[K1200]

The attached file contains a simple example of a portal that displays information for a selected User. The problem is that I want to display results only for a range of dates contained in that User's base record (the date pair is different for each user and can be changed when needed). I've tried two different methods and both fell short of what I'm trying to achieve.

Any suggestions of a straightforward method will be appreciated.

Thanks in advance.

RangeOfDates.fp7.ZIP

[comment]

Try it this way?

RangeOfDates.fp7.zip

[K1200]

Excellent! Thanks!!!

I've updated my example with dropdown calendars and a refresh button to update the totals, but I was not able to get the portal to automatically refresh as new purchases are added in the rows. Any ideas there?

RangeUpdated.fp7.ZIP

[comment]

Yes, that can be a problem when using related summaries (it IS an undocumented feature, after all). You can get the total to update instantly by using a calculation field (in the parent table) instead. In any case, your refresh script should be:

Commit Records[]

Refresh Window [Flush cached join result]

[K1200]

I've implemented the suggested changes -- and disabled user edits of the running total column -- but the condition persists where the column blanks out whenever any value in the amounts column is changed.

Unless someone spots a misstep, however, I'll live with that small deficiency in an otherwise straightforward solution.

BTW, this example reminded me of another issue: is there a way to cause a user-entered amount such as "82" to display as "82.00" while being edited on the portal row? I've seen the Retain User Formatting option, but not the other way around.

RangeImproved.fp7.ZIP

[comment]

1. The calculation field in the parent (User) table should be =

Sum ( Information::Amount )

This will update instantly.

I am afraid I don't have a good way to do the same for the running total in the portal. BTW, here it does refresh instantly when an amount is changed/added - it is only when the date range is changed that it goes blank. Again, summary fields weren't meant to be used in a portal. There's another way to do this, with a self-join in the child table, but since it is rather complex and since you seem willing to live with this as is, I won't go into it.

2. The 'Go to Layout [ original layout ]' script step is not required - since you're not going anywhere, there's no need to come back.

[K1200]

Thanks for the pointers. All the totaling is working fine now. The remaining hitch is with the refresh.

Are you testing this on a Mac? I ask because on my PC the column blanks out when the date is changed and also when any amount is edited. You seem to be seeing different results.

Also, I was surprised/pleased/confused to find that pressing the Numeric Enter key after editing any amount causes the entire portal to update correctly (Refresh not needed). NumEnter is not activated in the Field Behaviour definition. I suppose out here on the "undocumented edge", nothing should be a surprise.

I'll continue to test and will post a final update of my best results in a couple of days.

Any further suggestions of things to try will be appreciated . . . . plus any ideas on the 82 vs. 82.00 field format issue.

[comment]

No, we see the same thing - I didn't consider the blanking when TABBING out of the field as a problem. And yes, you are on the "undocumented edge" - see here.

[dhartin]

Found this thread extreemly helpfull for something I'm working on as well - so thanks for the posts guys..

I have a question though about possibly extending the functionality (and helping me out : )??

How would you go about adding a second portal that showed all other records - apart from those returned in the first date range search..

eg - in the above, rather than searching - you could show all records from the last month in the first portal, and then all other records in the second portal

possible :?

[comment]

Hmm... I don't think you can construct an 'out-of-range' relationship as simply as an 'in-range' one - it requires two mutually exclusive criteria.

I think you will need to use a custom function, or a repeating calculation field, to gather all the dates of the relevant month. Then use a simple = relationship to show the in-range records, and a ≠ relationship for the second portal.