Jump to content

Search the Community

Showing results for tags 'filter'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Community Forums

  • The New FileMaker Platform
  • Community Resources
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro Advanced 18
    • FileMaker Pro Advanced 17
    • Legacy FileMaker Platform Discussions
  • FileMaker Server Administration
    • FileMaker Server 18
    • Zabbix Server Monitoring
    • FileMaker Server 17
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Bar Codes (Printer, Scanners, Software)
    • Hardware & Networking
    • OS Level Database Automation
    • Product Ideas
  • JavaScript Integration
  • FMForums Affiliates & Sponsors
  • FileMaker Classifieds
  • FM Forums Operations
  • FileMaker Friday Night Chat's Topics


There are no results to display.

There are no results to display.


  • White Papers
  • Infographics
  • Samples
  • Solutions
  • FMGo
  • Plug-Ins
  • Tutorials

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

Found 33 results

  1. To begin, I have about 100,000 records (and growing) in a "Parent" table. Each Parent will have at least one "Child" (usually about 2 or 3, but can be 5 or 6). Each Child has a "Quantity" calculation field {sum( in - out)} which is related to the "Transactions" table.  I'm trying to filter a table (in list view) of the "Product" aka. parent to show only products that have any "Inventory" items aka. child that have quantity > 0.  I'm able to generate the results, my problem is the speed at which it calculates  For instance, "Prod A" might have three children: A_1, with qty 1 A_2, with qty 0 A_3, with qty 0  "Prod B": B_1, with qty 0 B_2, with qty 0  "Prod C": C_1, with qty 0 C_2, with qty 0 C_3, with qty 5 C_4, with qty 7  Therefore, I created a calculation field called "invQty"in the Product table as such: sum (inv::quantity). I have a button on my product layout attached to a script that looks for a value > 0 in the invQty field.  The result of the above example will display "Prod A" and "Prod C" only, which is great but it takes about 45 seconds. (Script is performed on client, file is hosted on FMS)  Would this be a time to use executeSQL, if so, how would the calculation be written (I have yet to use this function) from what i've read it isn't much faster? Otherwise, is there a faster way to do this? You guys always come up with some clever stuff, hope you can help me.  Additional thoughts: I've been working with PSoS lately, and it's been amazing, but not sure if this can work here to get a found set to display on client. I've also been looking into the "MasterDetail" setup by Todd Geist. Pretty awesome stuff, just not sure if it's the right solution for me yet.  Thanks in advance to everyone.. I also attached an image of the relationship for this example. I put a lot of information for this simple task, just wanted to be clear as possible.   Â
  2. Hi, I hope that someone on this community is able to assist me in a rather complex dynamical filtering of portals... I am working on a pretty complex CRM based on this demo for dynamical filtering of portals by Sara Severson: http://www.soliantconsulting.com/blog/2013/03/dynamically-filtering-filtered-portals But I have an issue when trying to dynamically filter the portal based on several relationships, that someone here maybe can answer... This is my situation: Filemaker file A (the CRM) contains a portal which I filter. The portal show posts from file B (Contact database) with the use of a relationship X. The relationship match fields in the two files are global fields containing just "1" - which matches all posts. The filtering of the portal here is working great. I can filter posts from the Contact database without any issues. But I would like a second dynamical filter field in relationship with a file C (an Order database) so I can narrow down the contacts in regards to what they ordered. So I made, in file A, another relationship between file B (relationship X) and file C (Order database). Here I match a client ID in file B with a client ID in file C. And adjusted the portal filter criterias accordingly, so it took notice of this second filter field. And yes, now it seemed that I could filter out specific orders, to find, for example, only clients in city X (from the file B, the contact database) which has bought item A (from the file C, the order database). But I noticed it didn't find every client with a particular order - after some debugging I found out that this procedure did only find the latest order a client made, in file C. In other words: If client A (from contact database) ordered item A and then later item B (posts in the order database), the filter did only find the client's order of item B. If I try to filter for item A, it found other clients that ordered this item, but not the client which ordered both A and B. It seems that the relationship between file B and C only matches the latest match, which seems a little odd. If I put a portal in file B (the contact database), with relationship of Client ID with file C (the order database), Filemaker found all orders, but only the first row in this unsorted portal here is found by the filter in file A. Any ideas? How can I make the relationship between file B and C in file A to find all orders? Here an image of the tables in file A, with some complementary information: https://postorder-hstrom.tinytake.com/sf/MTUxODk5N181Mjg2NDAz With kind regards, - Johan.
  3. I have a table of 200 clients related to a table of each client's visits. The visit table has the date of the visit plus other fields not involved here. These monthly visits numbering 1000 range across all visit records from 2003 through 2013. I put the visits in a portal on the client layout. It all works fine. What I'd like to do is show only those records that have visited recently. The business rule is: If the client hasn't visited since Jan 2008, he's abandoned us and is not longer interested. Of course I'd not delete those abandoned cases but I want FM 11 to show me only those records with a visit from Jan 2008 to now. I've tried doing this with the simple FM 11 menus using the "Date_of_Visit" field which is all I have. I can't use "> */*/2008" becuase that includes all records back to 2003. I can't use "< 12/31/2007" because that again includes all records which I don't want. Perhaps I don't know the sequence of joining a ">" find to a "<" find. How do I filter these dates?
  4. I currently have a portal showing all invoices attributed to a given client. At the moment, I use the following portal filter calculation to filter based on the 'Amount due' balance (i.e. invoice status) for each invoice (using a drop-down list Client::InvoiceFilter, on the layout); Client::InvoiceFilter = "Show All" or Client::InvoiceFilter = "Show Paid" and Invoice::Amount Due = 0 and not IsEmpty (Invoice::_pk_InvoiceID) or Client::InvoiceFilter = "Show Unpaid" and Invoice::Amount Due < 0 and not IsEmpty (Invoice::_pk_InvoiceID) or Client::InvoiceFilter = "Show In Credit" and Invoice::Amount Due > 0 and not IsEmpty (Invoice::_pk_InvoiceID) What I want is for the user to be able to also filter based on a date range. This date range filter would be mutually exclusive of the above filter. Scenario would be; Layout opens with portal showing all invoices. User has the choice to set a date range (using global startdate and enddate fields shown on the layout) - default is 'all' User has additional option to filter remaining items in portal using the above 'invoice status' filter. Should I be trying to incorporate the date range into the above calculation? e.g add a line to the above calculation like; and Set Field [VisitNotes::Date; DateRangeGlobal::gStartDate & "…" & DateRangeGlobal::gEndDate] or is there another way of having an additional portal filter using another means? Alternatively, should I be using, say, a List layout (not a portal), to implement these two filter types (date range, invoice status)? TIA
  5. 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
  6. Hi All, I am trying to filter a portal using case to display records matching only one condition (the first it match going down), but it looks like the filter is taking into consideration all the options and displaying related records that match any of the conditions. EG: Case ( newA = oldA ; 1 ; newB = oldB ; 1 ; newC = oldC ; 1 ; newD = oldD ; 1 ; 0) I thought the filter would work by only showing me the records that matched the first matched condition, but instead it's showing records that match any of the conditions. EG: if I had a record that had 5 related records, 1 where newA = oldA and 2 where newC=oldC, the portal would show me those 3 records instead a of just the first one which matched the first condition newA = oldA. Can anyone give me any suggestions as to how I can get it so that the portal only filter 'stops' checking the case condition once it's found a matched one?
  7. Dear All, I am designing a database for my workshop. The system lets users generate LPO Master (Table) with LPO Lines (Table linked to LPOs). An LPO Master can have many LPO Lines. LPO Lines have quantity values of spares ordered (LPOQtyOrdered). After an LPO Master is made, goods ordered, goods received, a Goods Received Note (GRN is made). This is stores in a GRN Master (Table) with Bin Card Lines (Table linked to GRN Master for Stock in, Table linked to Req for Stock Out). You can order 10 bolts, receive 9 bolts today and 1 bolt tomorrow. So you can make 2 GRN Masters for 1 LPO Master. You can order 10 bolts @ 1 USD and receive 9 bolts @0.95 USD an 1 bolt @ 1.05 USD. The LPO Lines has a field called QtyLeftOnOrder, which deducts the amount received in the GRN. The LPO Master has a field called LPOStatus that calculates Case(sum(QtyLeftOnOrder)>0;Full;Empty). When creating a GRN, the first thing you do is select an LPO for which the GRN is made from a drop down list. So, Case 1: LPOLines:LPOQtyOrdered may be 10 Bolts (lets forget about the price) LPOLines:QtyLeftOnOrder is 10 Bolts At this Point, LPOMaster:LPOStatus=Full Case 2: Now you create a GRN Master and receive 9 Items, At this Point, LPOLines:QtyLeftOnOrder=1 At this Point, LPOMaster:LPOStatus=Full Case 3: Now you create another GRN Master and receive 1 Items, At this Point, LPOLines:QtyLeftOnOrder=0 At this Point, LPOMaster:LPOStatus=Empty What I want is that since there will be over 1000 LPO Master that will be created over the year, I want only the Drop Down List to only filter out LPOs whose LPOMaster:LPOStatus=Full. I want Case 1 and Case 2 display, but Case 3 not to display but currently none display if I filter the list. But somehow, FIlemaker is refusing since this LPOMaster:LPOStatus is based on a calculation and so cannot be indexed. Anyway I can work around this? In the past I have managed to de-active an LPO manually by using a switch where someone makes it active or retired, this seems to work in a value list since the field can be indexed as it is not auto calculating but rather requires me to input manually ACTIVE or RETIRED Status by pressing a button. Please let me know. Thanks, Aly
  8. I've got some strange behaviour on portals which are used to regularly display data from different years. Each record of data on the child table has a group number, a date, a year (derived from the date field) and a value and of course an FK_ID back to the parent. There are multiple records per group. In order to sum these up and use them for further maths and place them on the parent records, I summarise by group and then calculate a GetSummary, breaking by the year field. At this point I will add this has worked perfectly for me up to now. Recently, a need arose to be a bit smart with the portal and dynamically display the most recent year's data in a given portal. Other portals display prior years. To do this, I just created a calculation of Maximum=Max(Year) and filtered the portals by this, changing the filter calculation on each portal as needed (so Maximum-1 would be the previous years on the next portal, Maximum-2 the year before that etc). That also worked well. I've now added further data to my main data table, placed in into a numeric group, provided a summary and GetSummary calculation as per all the other records and this is not displaying at all. My GetSummary is breaking on the year field, same as all the others. My portals are displaying all the other data when dynamically filtered, but they refuse to display the newly added data. If I add a static year field to the filter, hey presto, the GetSummary data appears. I would almost be tempted to say that you cannot use a calculated field to break a GetSummary, but I know this to be untrue as it's working elsewhere and I need to keep this as dynamic as possible without hard coding filtering into the portals. Thanks in advance and apologies for the rambling, this is seriously frustrating me.
  9. Hi, I've got a pool of product IDs (it's for a book database - so these are 13-digit ISBNs). I get these from a pool of IDs that I keep in one table. I'd like to allow users to select an ISBN from a drop down list and assign it to a product in another table, without using an ISBN that has already been used before. I've managed to use a value list to get the drop down box happening, but can't seem to filter it based on whether it already appears in the product database. I tried using a calculation field in the ISBN pool table that verifies whether the ISBN is used in the product database (I set the relationship to link the ISBN field). The field will give me a boolean response which is correct, and I can even use conditional formatting to strike out the ISBNs in the pool that have already been used (this makes it easy for me to see which ones have already been used when I import more ISBNs later down the track). However, I can't seem to use this information to filter the value list. Does anyone have any tips on how to do this? Or if I'm doing it completely wrong, any starting points on how to do it differently?
  10. Hi there, In FM13, I have a layout with about 10000 records, i would like to create another layout/duplicate and display only specific records of the 10000 records already existing on the main/old layout, i have a drop down field that defines the category of the records(e.g, Employee, Ex Employee, Accommodation, Embassy, Car Hire etc) - is this possible, by relationship definition or any other way, how?
  11. So I have two tables. I'm going to be very specific and use very general terms to make sure there isn't any confusion, so thanks ahead of time for bearing with me since this description might seem a bit monotonous. Table A Name_fk Item Number | Table B Name_pk Team I put a relationship between Name_fk and Name_pk. Name_pk is a list of names and what team they are. Name_pk is filled with entirely unique values, so you know John Smith is on Team Blue there is only one record with "John Smith" in the Name_pk field and that record has "Team Blue" in the Team field. Now Table A will have a lot of repeat items under Name_fk. There will be ten rows with John Doe in them, ten Jane Doe rows, ten Joe Doe rows, and ten Jess Doe rows. Joe and Jane are on Team Green according to Table B, and Jess is on Team Blue with John. In the relationships section of the Database Manager, I have linked Name_pk and Name_fk. I need to be able to search for Team Blue, and only have John and Jess's item numbers show up, or search Team Green and have only Jane and Joe's Item Numbers show up. So far I have been unable to get this to work in my current solution, although a test solution works fine.
  12. I am a novice at layouts and filemaker language, So this is what I am trying to do and I thank anyone who helps me figure out how to get this completed. I am trying to create a database search filter that will be able to return any and all records that fit the filter but can have an unlimited number of search criteria that are based on "and"/ "or" strings. I'm not sure if using a portal can solve this but this is what I'm talking about. example, search: first name="" , and, age<"" , and, height<>"", + or last name="" , and, team="" , and, record>"", + + The plus signs are buttons I would like to have to automatically add to the search criteria to tag as an "and" string or an "or" string. So when you go to click one it adds a box to enter the "field" from the database then the command "<=>,<>" then the "value/comparing field" I'm not sure if I'm asking/trying to do something complicated here or this is a walk in the park, but any help will be greatly appreciated. Thank you.
  13. I am working with INT and MOD functions to generate receipts. For example an amount splitted into 5 receipts. The first 4 have the highest integer and the 5th has the remainder. What I would like to accomplish is to show through a portal a calculation field with the result. I have made a sample file to explain my idea. As always, thank you very much for your help. NumberOfReceipts.zip
  14. Hi, I have a solution that I have received much needed help from by FMForum members and part of the solution is to filter a portal from a long list (1000 records). the application is to be used on an iPad and the filter doesn't behave very well in FMGo with the keyboard popping up and a dialogue asking me if I want to discontinue the script...I did a search and nothing came up. Is it just intuitively obvious to experts (not me for sure) that you simply don't filter portals for fields that will be used in FMGo? If so, what do you do? I'm filtering a list to select the item for use in another table Thanks for the help! Mary
  15. Having a difficult time getting $$Event_Filter to work. My event table has id_event_type. I have a global field that filters an event list by this value. Any help incorporating $$Event_Filter? I even tried hard-coding the $$Event_Filter by setting it to "id_event_type = "1F7CED2C-D153-45FC-AFD5-DA796FAF4F28" and still just ?s. Here is what I see in the data viewer: SELECT "event_description", "EzCal_Text_Color", '', "start_time", "end_time" FROM "event" WHERE ( ( NUMVAL ( "start_date" ) = [[DATE]] ) OR ( ( NUMVAL ( "start_date" ) < [[DATE]] ) AND ( NUMVAL ( "thru_date" ) >= [[DATE]] ) ) ) AND ( id_event_type = "1F7CED2C-D153-45FC-AFD5-DA796FAF4F28" )
  16. I'm using a global field to filter a portal so that each user can set the global field and view different sets of records simultaniously. The problem I am having is that if any user clicks into any field, it is locking all records for all other users. I came across these threads that I think are related to my problem: http://fmforums.com/forum/topic/57413-relation-graph-cardinality-question/ http://forums.filemaker.com/posts/96fc4993b8 I think it may have something to do with having a global field on the RHS of the relationship. Has anyone else experienced this? Everything else is set up and working exactly how I would like, but the fact that all other users are locked out from editing all records is a big problem.
  17. Hi all Since upgrading to FM14 we've experienced crashing (errors 1000 and 1026 in the error log). I thought perhaps it was to do with version 14 or .net or my scriptmaster plugin. However it appears to be to do with a filter that I had in place for a portal. It was probably just a coincidence that I had so many records that it was referring to by the time I did the FM14 upgrade. I can probably do without the portal altogether really, but was just curious as to what the problem might have been. The filter formula was: Cases Closed::Closed_TimeStamp > (Get(CurrentDate) - 30) The Cases Closed table has around 22,000 records. Any ideas what's wrong with this? Thanks
  18. Hi gurus, I am sure I’m missing something simple, but for the life of me I can’t figure this out. I have a database with tickets. What I want is when a new ticket is created I want to see a popover (see attached) then I want the user to type in the top box and as they type I want to filter the list below (a portal) and once they client is shown they would select it and a ticker is created for the client. I sound a script and modified to fit my needs but can’t get it to work. Can someone stretch a helping hand? BTW the filtering is working but its not reflecting in the popover its reflecting on the layout.
  19. Good morning, I have a table with Jobs and each job is related to a specific year. I would like to have a Drop down list in my Jobs layout that would filter all the jobs for the specfic year. I have tried to use Value lists and read the year data from a table. Both options and due to the fact that I have to select a Jobs table related field to use the Dropdown invalidates my attempts. I would like to have a dropdown list that does not store anything and it just serves as a trigger to filter data. Is this possible? If so, how? Thank you for your time in advance. Nuno Neto
  20. Hello All, I have a parent table with a related table that holds records with 2 fields - a field holding either "S" (Strengths) or "W" (Weaknesses) and a comment field. For report printing purposes, I'd like to combine the comment field of all related portal records in a combine_field in the parent table - but only if the record is a Strength. Could you please advise if I need to script the combining similar to this http://help.filemaker.com/app/answers/detail/a_id/2403/kw/combining records but with an additional check on the S/W field... or is there a more efficient solution whereby I can make my combine_field a calculated result using the List function and some kind of conditional function like If or FilterValues? Thanks for your time, Ben
  21. I am filtering a portal where one method works and other other does not. WORKS Case ( IsEmpty ( table::globalTextField ) ; db_payment::id > 0 ; PatternCount ( table::globalTextField ; "/" ) > 1 ; db_payment::date = GetAsDate ( table::globalTextField ) ; db_payment::invoice = table::globalTextField or PatternCount ( db_payment::reference ; table::globalTextField ) > 0 ) DOES NOT WORK line 1 and 2 of case work but not line 3 Let ( [ txt = GetAsText ( table::globalTextField ) ; num = GetAsNumber ( table::globalTextField ) ; dte = GetAsDate ( txt ) ; dt = PatternCount ( txt ; "/" ) > 1 ] ; Case ( IsEmpty ( txt ) ; db_payment::id > 0 ; dt = 1 ; db_payment::date = dte ; db_payment::invoice = num or PatternCount ( db_payment::reference ; txt ) > 0 ) ) I have simplified examples but ultimately I would prefer a Let function to allow for further options in my filter Portal_filter_with_Let_function.zip
  22. Hi all.. Can a portal be filtered to a certain number of records it can display.. For example, I have a portal that shows my contacts (100).. I want to filter the portal to show only max of 10.. I have tried removing the scroll bar and limiting portal records to 10 via the portal settings, but I still have the feeling that filemaker actually served all 100 records just that I am being able to see 10.. Reason for my question : I have read that to enhance performance issues and fast loading of data especially via 3G networks, it is advisable to limit the records been served.. Such that only needed ones are served to the user... I don't know if I have really explained my point.. But I am available in case of any clarifications
  23. Can anyone take a look at this and tell me i am on the right track relationships are correct What can i do to improve this? trying to also based product(s) selected ask for different attributes. DOnt really know what to do there yet. thx, -i start_over Copy.fp7.zip
  24. This is my situation, I have a table called "create parts" which holds a set of record of parts standarized with their info, I created a filter with a field which is related to this table by "type" for example: "inventory" type (pull down): plastics <----> "Create Parts" Type That would display all the parts in the portal that has the "type" I select. I would like to add a smart filter (a global for example) that if I start typing it filters within the current portal. for example: type: computer parts Will display: System board, LCD, Memory, CPU, Keyboard, Mouse, etc.... If after I select "computer parts" in type and it shows all these records i want to find quickly "keyboard", I would like to have a global that by just typing "k" it would show me only the records starting with "K" on the portal and so on, the more letters the more accurate the result is. Is there anyway this can be done? Thanks in advance.
  25. I have a table of personnel which includes a "department" field. There's a related table, with a layout that has several dropdowns that all have the last and first names of personnel as the data to display. Each field on the layout corresponds to only one or two applicable departments due to the nature of the form and it's purpose. For that particular field on the layout, only names from a certain department would ever be selected. Having ALL personnel show up just clutters things and makes the dropdown list huge. I was working on a conditional value list but I'm not getting anywhere so I'd like to take a step back and get everyone's input on what is the best method to achieve this filtered dropdown list result. I don't want the user to have to enter the filter criteria. I want one dropdown that only shows personnel from department A in the field where that is the only applicable department; then another dropdown that only shows personnel from departments B and C (some will have two, but still filtered), and so on. Possible Ideas a saved find of the personnel table (one find for each department). Somehow make the name field of the found set be the source of the dropdown Conditional value list: I'm at a dead end on this method. see thread: http://fmforums.com/forum/topic/80277-filtering-contents-of-dropdown-menu-using-values-from-another-field/page__fromsearch__1 separate TO of the personnel table for each department or several child tables of the personnel table that only contain the personnel in one department (I know, seems messy) Any other suggestions are welcome! I really like the idea of the saved find results being the source of the dropdown but I've brought it up a few times in another thread and couldn't get any advice on implementing it.
  • Create New...

Important Information

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