Actually, this can work about the same whether you use global storage or not. The filter expression is the same, but the differences between global and normal storage are something for you to evaluate in terms of your layout design and whether you are designing a database to be shared over a network or not.
This requires that all three date fields be defined of type date. If you are using a text field to store the transactiondate, this will not work.
Define two date fields, Date1, Date2.
Your filter expression would look something like this: (Substitute your table and field names for mine here.)
Transactions::TransDate > Clients::Date1 And Transactions::TransDate < Clients::Date2
You also need to use a script to force the portal to update after you edit either the Date1 or Date2 fields. This script can be performed via an OnObjectSave trigger set on both date fields:
Refresh window [Flush cached join results]
Differences between global and normal storage:
If you set up a list view layout where you can see several client records at a time, using global storage, all the portals will filter to the same date range. If you use normal storage for these two date fields, you can filter each portal by a different date range.
If you share the file over a network, use global fields if at all possible. This prevents two users from looking at the same client record and interfering with each other's desired date range when they both try to edit the same pair of date fields. (With global storage, each user gets their own "copy" of the global fields and they don't see the values entered/edited by the other in them.)
Hi Phil, thanks for your quick response, really appreciate the help. I will try it and get back to you if I have any problems or further queries
If you go the Global route Id suggest placeing the Global Fields in their own table.
Id also add a Global to store the Client ID as well so you can build your relationship against the Global table.
You can use a number of methods to populate the Global Client ID.
The only reason I suggest a seperate Global table is so you can keep track of your filtered relationships easier.
If you add the global date fields to your client table you wont have to worry about the global client key.
The global table however has some advantages though.
Thanks. Actually your answer has prompted another question. Currently I have a portal showing all transactions per client. Is there a way to select multiple clients and view transactions for a custom date range?
thats a bit more convoluted but yes. There are a number of ways to do this as well none particularly great. ( I edited my earlier post so if you didnt see review it)
Just reread phils answer it would appear that he is suggesting yes as well with not as much difficulty as I was envisioning. Of course I was trying to do it without the list view :)
One way to view multiple selected clients is with this relationship:
Clients::gClientList = Transactions::ClientID
gClientList is a field that has each selected clientID in a list separated by returns. You'd then filter your portal by date range as we'd already discussed.
The challenge is in building the list in the global field in the first place. You'd need to set up a system where you can select clients to add them to the list (Drop down with list of clients is one option), and also a way to remove them from the list (button in portal row, or second drop down of selected clients.) All of this requires scripting and triggers to manage the values in the list.