Filter a portal by date

[GoHeels]

Hello,

 

I've searched and searched for an archive solution to want I want, and haven't found it, so I'm hoping that the great folks here might be able to help.

 

What I want to do is allow users to filter a portal to either week view, month view, year view, or all records view. I believe this can be done using globals in a drop down list, but I'm not getting how to set up the TO's (I'm using 7) to accomplish this.

 

Can anyone point me to a previous answer or sample file if it exists? If not, can anyone give me a push (o.k. shove) in the right direction?

 

Thanks in advance.

[Ender]

The trick is to have all the possible filters listed in a multi-line match field.

 

Something like:

 

matchkey (calculation, text result) =

"Week " & WeekOfYear(Date) & ¶ &

MonthName(Date) & ¶ &

Year(Date) & ¶ &

"All"

 

However, as it is, this would show multiple year's records when the user filters by Week# or Month Name. You may want to filter by 'Year and Week#' or 'Year and MonthName' to prevent this. This additional criteria can be added to the calc (and would then require a calc on the parent side of the relationship too,) or added as an additional criteria for the relationship dialog.

[GoHeels]

Thanks, Ender. Could you be a little more specific for me? Would I need the matchkey in both tables and use that in the relationship? Would the value list just be based on that field? I'm so confused and really appreciate your help!

[Ender]

Well it depends on what options you're giving your users to select from. Let's assume that you give users one value list to choose from with these values:

 

January

February

March

April

May

June

July

August

September

October

November

December

-

2004

2005

-

All

 

I'll leave you to think about how to allow a choice of weeks, as a value list of 52 weeks doesn't seem very practical.

 

Let's attach our value list to a filter field gFilter, located in the parent table. We'll also need an unstored calc field in the parent table to show the correct filter criteria for the relationship. If it's a month, we'll append the current year to it. We'll also adjust the match field to use the month name with the year:

 

Parent Fields:

ParentID (number)

gFilter (text, global)

FilterCalc (calculation, text result) = gFilter & case(position( "January February March April May June July August September October November December"; gFilter ;1 ;1 ); " " & Year(Get(CurrentDate)))

 

Child Fields:

ParentID (number)

ChildID (number)

Date (date)

Matchkey (calculation, text result) = MonthName(Date) & " " & Year(Date) & ¶ & Year(Date) & ¶ & "All"

 

Now the filtered relationship would look like this:

 

ParentChild

Parent::ParentID = Child::ParentID

AND Parent::FilterCalc = Child::Matchkey

[GoHeels]

Thank you so very much for the details, Ender. Happy Friday!

 

The method works beautifully for the records that are already in the portal, but now I can't create new records in the portal. I have "allow creation of records" turned on, but still get the "This action cannot be completed because this field is not modifiable" for the Date. What am I doing wrong? :-(

[Ender]

Since we're using a calc field (the matchkey) as a match field in this relationship, we cannot use this for creating records through the portal.

 

I'd recommend using a different way of adding related records. Either by jumping to a new record in the related table, or by having a bunch of empty global fields on the layout to hold the new record's data, then use a script to actually create the new record in the related table.

[GoHeels]

Of course. I was so caught up in the filter working, I forgot about that limitation. I'll come up with another way.

 

Thanks for all of your help. It is so great to have somewhere to come to ask.

[Ender]

You might consider another option for filtering that will allow creation of related records: Date range criteria. With FM7's built in range relationship operators, you can use two global date fields in the parent table to define the Start and End Date range, and still have the ability to create related records.

 

The relationship would be:

 

ParentChild

Parent::ParentID = Child::ParentID

AND Parent::gStart Date

AND Parent::gEnd Date >= Child::Date

 

If you want to allow open ranges, then use a couple date calcs for the relationship:

 

StartDateCalc (calculation, date result) = If(IsEmpty(gStart Date); 1; gStart Date)

EndDateCalc (calculation, date result) = If(IsEmpty(gEnd Date); Date(12;31;3000); gEnd Date)

 

Then the relationship would be:

 

ParentChild

Parent::ParentID = Child::ParentID

AND Parent::StartDateCalc

AND Parent::EndDateCalc >= Child::Date

 

So users would optionally enter a start and end date into globals, and the relationship would show just those related records in that range.

[GoHeels]

Ender, I tried this as I'd really like for the user to be able to enter records directly from the portal. My problem is that now, if there are no criteria in the globals, date entry defaults to 12/31/3000. I've attached a simple example of what I thought I understood.

 

Do I need to have a default value in the globals if the user hasn't entered anything there?

 

Sorry for being so thick about this. I do appreciate your time!

[Ender]

Because the related Date field is part of the relationship, it gets populated with the parent key when a new record is created. In this case, there are two parent keys for the Date (since it's a range,) so it picks one of them and uses it to populate the related Date field. I don't see a way around this.

 

You might try starting the user in the date field. If they type the correct date first, it should create the related record with that date, and as long as the new date fits in the range, the new record will show in the portal.