Jump to content

Filtering for fields in a portal


ryansapp

This topic is 2934 days old. Please don't post here. Open a new topic instead.

Recommended Posts

So I'm fresh & new to FileMaker but am fairly savvy with MS Access.  Struggling to setup a form/report that can display some fields by a drop-down calender (date range).

• Currently I've got a small portal setup on my home page that I can select the date (already made a field that generalizes the month/year) from although ideally I want the selection to stem from the drop-down calendar range, and below it I'm initially wanting to see a total amount of "shop tickets" within that given date range. Following the completion of this, I aim to try to figure out how to add more filters so that I can then possibly see the amount of tickets that are tied to a particular "pump tech" or amount of tickets in the date range that were "repaired","new","junked",etc. 

I've read up on multiple solutions that should've at bare minimum addressed the initial situation, and mimicked the exact way (or so I thought) of how they set things up in other databases and yet I still end up counting the total tickets in the database and rather than having it tied to the date filter above the portal as I had hoped for. 

Any help is monumentally appreciated, thank you! 

-Ryan

Link to comment
Share on other sites

I can't figure out what your question is. If you want to filter a portal (or a relationship?) to only show records within a given date range, you first need to supply the range. If you want to use a drop-down calendar for this, then you will need two global date fields: gRangeStart and gRangeEnd. Then you'll be able to filter the portal by:

SomeTable::gRangeStart ≤ PortalTable::Datefield and PortalTable::Datefield ≤ SomeTable::gRangeEnd

Or define a relationship using the two global fields as the matchfields on the parent side.

Note that in order to summarize a filtered portal, you must place the summary field/s (defined in the portal table) inside a similarly filtered portal.

 

 

Edited by comment
Link to comment
Share on other sites

Well, thats certainly a step in the right direction creating the global start/end date fields, but I've got a summary field calculating all records still, not by the particular dates in which I've selected. 

This summary field is on the main data table, and stays at 28, regardless.  I added in the same summary field from the child table, and I notice its changing as I scroll through the records on my home page...having groupings based on the month. In other words its saying '10' for a number of records that were dated from February, 16 from March, and 2 from January. 

I feel like I must be somewhat close to getting this problem tackled with some help, and thank you again for that. Something still missing though. 

Link to comment
Share on other sites

To summarize records shown in a portal, the summary field must be defined in the child table - i.e. the table shown in the portal.

And, as I said earlier, if the portal is filtered, then the summary field/s too must be placed in a filtered portal. Usually, you would duplicate the (already filtered) portal, change the duplicate to show one row only, and place the summary field/s inside that row.

I did not understand this part:

1 hour ago, ryansapp said:

I added in the same summary field from the child table, and I notice its changing as I scroll through the records on my home page...having groupings based on the month.

What do you mean by "groupings based on the month"?

Link to comment
Share on other sites

Ok, fair enough, using the child table summary field is when I'm referring to the "groupings based on the month" previously. As I click through the records at the top left of the database, I notice that my summary field is saying '10' for all the records from the table that are dated in February, grouping them in this manner isn't what I'm wanting.  I'm aiming to have it be based on the RangeStart and RangeEnd global fields I have placed above the portal of course. 

As far as the filtered part, I did the exact same setup you had mentioned, using my main table and the child/portal table in the same instances. Having said that, I do NOT have the summary field in the filtering calculation. 

Screen Shot 2016-03-16 at 3.29.00 PM.png

Screen Shot 2016-03-16 at 3.29.11 PM.png

Link to comment
Share on other sites

I am afraid I still don't follow your description. What does a record in your parent table represent and how did you define the relationship between it and the child table? And what do you actually expect to accomplish here? Do you sell tickets to pump repairs?

Link to comment
Share on other sites

Any and all records in my parent table represent all of my base data.  We record pump repairs by the unique ID of the 'ticket'. To start and get my metaphorical feet wet in FM, I am literally wanting to count the # of tickets in a given date range in an easy and repeatable way when I open up my database and on the home page or some sort of summary page. 

Link to comment
Share on other sites

Sorry, probably a company reference using the terms base data. All of the records within that main table are the only records in which I am aiming/needing to filter out.  I simply created a 'child' table that was a duplicate of the main table because that seemed to be common practice, not because I saw it to be necessity.  I'm still fully believing I'm making this wayyy more difficult than it should be.

Link to comment
Share on other sites

31 minutes ago, ryansapp said:

I simply created a 'child' table that was a duplicate of the main table because that seemed to be common practice,

I don't know about common practice. I believe I would prefer to create a new parent table for the dashboard - or perhaps use an existing Users table, if you have one - instead of re-purposing your "main" table.

Anyway, if I understand correctly, your "child" table is no more than another occurrence of the "main" table, is it not? And how did you define the relationship between the two?

Now, with regard to the behavior you describe - that is how it's supposed to work. You are looking at a portal that shows all records that meet the filtering expression. The filtering expression does not reference any data from the current parent record - therefore the portal contents will not change as you scroll from one record to another in the parent table (this is assuming you have correctly defined the relationship to use the x relational operator).

If that's not what you want, then you need to explain in more detail what the goal is here.

 

 

 

Edited by comment
Link to comment
Share on other sites

Correct, the 'child' table is no more than another occurence of the 'main' table. The relationship is literally just connecting the similar fields.  

Yes, behavior is taking place as its supposed to work I understand.  Unfortunately not taking place as I was aiming for it to. The portal field counting the total tickets from the 'child' table in this instance is not in correspondence with the date range above whatsoever. Its simply not referencing that start and end date range, but instead somehow counting the total records in a given month, even when no change is taking place in my start/end date range selections. 

Link to comment
Share on other sites

1 minute ago, ryansapp said:

The relationship is literally just connecting the similar fields.  

That's not a sufficient description.

 

26 minutes ago, ryansapp said:

but instead somehow counting the total records in a given month,

This too is not clear. What is the "given month"?

 

Does the attached work as expected?

 

 

dash.fp7

Link to comment
Share on other sites

HA! Yes, works pretty close as I am requesting. I will attempt to do some deductive reasoning with what I've got in order to get there! I was actually just requesting the one field "sCount" that you've got that is counting those serial ID's, but nonetheless I can work with this (I hope). I can't begin to thank you enough for your help, I really do appreciate it....and apologize for my ignorance on many of these FM issues I have, unfortunately it is a little different than my familiar Access world. 

Link to comment
Share on other sites

So, after a long while (and plenty of breaks in between) I have noticed my idiotic error of having the wrong relationship. The exact thing you were basically warning me of, wrong relational operator. So I fixed that, made it a 1-row portal and now I can just see all tickets in the given date range, as I was aiming to see originally. Again, I thank you greatly for the assistance and direction on this!

Link to comment
Share on other sites

This topic is 2934 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.