Finding & Searching

Hi all. I am trying to find records in a date range and exclude certain records from that find. I am unable to get the desired result from the following script steps, could anyone point me in the right direction please. I am trying to find all records in the date range that have a value in the BankExpenditure and PettyCash field while excluding records that have a Type description of Transfer Receipt and where the Description_BankData field contains CostFranch as part of the description # TEST - COST / EXPENSES FOR DATE RANGE Go to Layout [ “Costs” (Costs) ; Animation: None ] Enter Find Mode [ Pause: Off ] Set Field [ Costs::DateStatement ;…

I am using Filemaker 19 and have a field which has 0-100 values which are return separated, they are different for each record. For example: Ice Ice Breaker Ice House Ice Pick I am needing to be able to search for records which contain "Ice", without finding the other records. I have tried "Ice", ="Ice", =="Ice",*"Ice", =Ice¶, etc. These all either find all records containing Ice - no matter if it is followed by other words, or no records at all. Is there a way to accomplish this, preferably within a script?

Is it possible for the Perform Find script step to generate a Get(Last Error) of 0 and NOT find any records OR should Get(Last Error ) ALWAYS return a 401 if no records are found? And if Get(Last Error) could produce a 0 with no records found under what circumstances would that happen to come up under?

I'm by and large following the recipee from here: https://filemakerhacks.com/2013/05/27/fast-grid-display-on-a-wan/ ...I'm rendering the virtual lists into a subsummary report, via an unstored field in each line - but I have next to the unstored repeating calc'field, placed a checkbox, so the user can pick 3 of the lines shown in a graph instead. Its my take on the matter, that the subsummarizing never were intended, to recieve user interaction other than eyeballing? But I've found, that the moment you tick into the second choise-checkbox some network activity is happening ... is there a way to render virtual lists into stored fields instead, other than sc…

This should be easy for you guys.... I use case numbers like 12-2222. If I have an addition to the case it gets a two digit decimal like 12-2222.22. I want to find all cases without decimals. For instance I want to find 12-2222 but not 12-2222.22. How do I do this??? Also, the case numbers may be only 5 digits like 17-888 and I would need to find them without the decimals also, like do not find 17-888.22

I would like to constrain records to only show 1 occurrence of an individual within a found set of records. (see pics for example).I perform a find by date range on table which produces result A. Now I want to constrain that set to get result B. ie.. If John Smith shows up twice within that date range I want to constrain to only a single record for John Smith. The idea is produce a report of Unduplicated Individuals Served within a date range.

Howdy, howdy: I use the "TOG" method to save a found set (https://community.claris.com/en/s/question/0D73w000006YjbYCAS/detail ) , but I must be missing something: If no records are found, and I perform the Restore part of the script, the original found set isn't restored. How do I restore the original found set after a failed Find? As an aside, I read elsewhere about creating a Summary field (List of record IDs) to create a list of primary keys to use later to restore a found set, but I don't know how to make use of it to restore a found set. How to? As always, TIA for your help! Rich

I have two different Filemaker solutions, let's call them app1 and app2, with the same filtering functionality for a list view (app1 is a small "alpha version" and app2 is a complete system with app1 as one of its modules). Within my filtering logic I'm using Modify Last Find to let the user filter the found set further down as they are searching the list view. I noticed that in app1 the script step works fine as it should, but in app2 it does not restore previous find criteria - all fields are blank again, and this happens not only through the script, but also manually using status toolbar buttons. It also works (app1) and does not work (app2) on other layouts (form and …

Hi All! Stumped on this one! Is there any calculation that would let me mark if a record is in the current found set? So, if I've a database of say 100 files, I then perform a find for something, and that results in 30 records - is there any calculation that I can have so that those 30 records are marked, and the others aren't? I've had a look through all the Get (x) and the other expressions, and I can't see one that would fit the bill. I could do it via a loop to mark the found records, but then I'd have to invert the selection, and clear that marker, which is more of a fuss than just being able to find if they're in the found set. It would get tricky if …

I've been building a database to store and sort photos for books and exhibitions, which is now working very well - I save as PDF and have a high-res scrollable file I can load onto my iPad or other device. I start out by pasting photos and applying labels to fields. It works really well to a point, and that is sorting the order. I have three primary fields: [Project title], [location], [sub-location]. This is easy to create as I go along but then its time consuming to sort as a sequence after that. I need a process to sequence the photos into location>sub-location then sequence (1,2,3). I guess I could sort on label order in the location>sub-location fields, then do…

Help me understand why this code is returning ZERO. Runing FileMaker 18... Nevermind... params are backwards

I wanted to verify if it is expected behavior that submitting: 1Password … (which I have in 6 records out of over 3,500) in quick find returns records containing a value of just "1" in one more more fields (the found set includes over 95% of the records in the database). Same results when I enter a string that does not exist, such as 1asdfsdfaf. This appears to be the case after doing some testing, but it's possible I'm missing something. FileMaker Pro 19.6.3.302 macOS 12.6.9.

My file has a couple tables that have almost nothing to do with each other, but I have a relation between the two because of what I'm trying to accomplish. It's just a key field on both sides. Table A is a list, Table B is more of just a viewer, with portals to a few tables, and does some calculations, but only one record. I have one field in Table B, where I need the contents to be the value of a given field on the last record of Table A. The field in Table B can be a calculation, but how do I tell it the calculation equals the contents of Field X on Table A's last record?

Set error capture[0] Freeze window Set Variable[$inv; value:invoice::invoiceNum] Set Variable[$total;invoice::Totaline] Enter find mode [Pause:off] Set field [cash::Fkinvoice;cash::search] Perform Find[] Set field [cash::Fkinvoice; GetValue ($inv;1)] Set field [cash::SumBill; Getvalue ($total;1)] Exit Script [Text Result:$Null]

I can't go into too much detail, much the same issues exist in a recipe, or a tech tree in some game. I am converting a variety of information into a FMP.19 database. One of the items is a part, which may require other parts to make it. Each part is listed with its immediate parents, so for example: Part (1): No parents Part (2): No parents Part (3): (1) Part (4): (2) and (3) I need to be able to identify all ancestors, not just the immediate parents I am given, so from the above data I would need to identify (4) as (1), (2) and (3), since (3) also requires (1). I can do this with a recusive loop that traces up the parentage tree, and takes a bazil…

Sorry in advance...I've done a ton of searching and still can't figure this out I am trying to make a layout that shows the number of records whose data matches two criteria. Fields are Ensemble and Score Order. Looking to count number of records where Ensemble is Band and Score Order 1. I will then repeat that for score order being 2, 3, 4, etc. Thank you so much for your help!

Hi All I am trying to find if there are any duplicate serials within a quote but I just find ever line item and not the expected error 401. Any help appreciated. This is the find criteria. QuoteId = $QuoteId AND QitemSerial = ! The second screen is the Find result

Is there a way to do a find that is similar to the mysql type WHERE field IN() Each record in a table has an unique ID and I'd like to find many in one operation. It seems like this would be a common find operation, but I can't find any mention of it. select * from `table` where `id` IN (1,2,3,4,5)

Hi Folks, Hope this is the right spot to post this. The Standard sorting order (by creation date of the dataset) is probably the worst possible way to view my Database for most things. Resorting it everytime it get's opened isn't very feasable either as sorting the almost 30'000 datasets takes ages to complete and is annoying if it's done every time you log in, when you might only wanna search for somethign real quick. So I thought it might make much more sense to just sort the search results every time someone uses a find. (100 records are sorted in almost no time). I'm not quite sure which trigger I have to choose for this to work... The claris Kno…

This is a little convoluted so my apologies in advance. I have a parent table (DISTRICT_STAFF) where the teachers' grade levels are captured in a child table (GRADE_LEVEL); they're entered using the portal circled below. (As an FYI, I use the List function to list them in the Grade_Levels field by the red arrow head.) To look up teachers and to create lists/reports, I created a checkbox'd value list so users can pick which grades they're interested in finding out which teachers teach them. Mind you, their selections may not be in sequential order, e.g., K, 3, 5 , 12. Here's where it gets tricky: How do I find (related) records from a checkbox'd value list? As f…

I have 2 text fields in a database that I have concatenated into a calculated text field in the same database. One text field contains a student ID and the other the name of a class. The calculated text field contains something like "Week 4 Before Care853C410F-42DF-49D6-AD31-BF7A19A2F44A". I use the calculated text field in a script to determine if a particular student has previously signed up for a particular class. When my script step enters find mode and sets the calculated text field to "Week 4 Before Care853C410F-42DF-49D6-AD31-BF7A19A2F44A" and then performs the find I get multiple results seen below. I can't figure out why and for the moment I have set the…

Hi there, I'm having a bit of a strange issue where quickfind is not working for some of my layouts. I initially noticed it when my quickfind script didn't work. Next I tried using the native quickfind field, and noticed that that wasn't working either. Naturally I checked the following: Layouts > Layout setup > enable quickfind = Checked Field inspector > Include field for quickfind = all checked (fields are also marked with the green quickfind icon Yet no matter what I try and do, quickfind simply won't work - ie. it returns all records each time. Remember I'm using the native filemaker quickfind for this so it's not script related. (I …

I have several records and some of them have the ckeckbox field selected. I want to isolate the records with the selected checkbox but am not able to set up the find to look for them. I have tried setting the checkbox field to 'true' but this does not work. Can anyone assist please?? Thanks.

I'm probably going to be embarrassed at the simplicity of the answer to this question, as in extensive searching I haven't found any information that resolves it. Opening a simple inventory spreadsheet with about 5000 lines with Filemaker 13 and saving it as a 'converted' filemaker database, I expect to be able to use the Quick Find facility to find records where there is a field that matches the 'find text'. In an example search for string "KDC2" I get a found set of 998 out of 5028 records. To find which fields contain the "KBC2" string, from the Edit menu I use Find/Replace, but cannot find the "KDC2" string anywhere. In fact when I go to the spreadsheet and…

I fell like I'm going insane. I've created a simple little solution, just a couple related tables, no big deal. I am creating records via a portal in a Parent table layout, and the records are turning up fine. ....but somehow on newly-created records, perfectly simple one-field FIND efforts are simply failing. I am looking at simple layout based on the child table, and the records are there. In browse mode, I am looking at a record as you see below -- and searching for a record using the primary key, and it just yields no matches. SO I put a simple text field on the layout as you see. I am in Brose mode and I can see the value is '222'. So I search on that fi…

I am using the id and parent_id fields above on the right to link related trades. The 2 parent_id's in this case are the result of selling the 1 id buy. Now I am trying to do a search for the id (all UUID's) and the parent_id's that are the same. I want to isolate the 3 transactions as 1 completed trade. I have ended up with the below script, but it does not work and returns no matching results. the individual set fields return the correct results but not when I combine them. Can anyone assist please?? 210512.fmp12

I have a one-to-many relationship in play here. The ADVERTISERS table contains the ad clients (the "one"). The AD HISTORY table contains all the ads a client has run over the years (the "many"). The tables are related via an account number field. At the beginning of the script, I generate a variable containing the issue for which I want to see a report. This variable is named $$RunNumber. I can successfully find all the ADVERTISERS that have placed an ad in the chosen Run Number. I want to display one field of data from the AD HISTORY table in the report. The use the "Go to Related Record" function to get to the AD HISTORY table. Here's where …

I have a simple payroll app I am (trying) building. When I try to narrow an employee's time into a date range I can't get it to work. There is already a variable field called "DateRange" - it currently is "6/15/2021...6/30/2021" Script: Go to Layout [ “TimeSheet” (tblTime) ] Button 2: “Cancel”, Commit: “No” ] Enter Find Mode [ Pause:Off ] Set Field [ tblTime::dtDate; tblTime::DateRange ] Perform Find [ ] So it doesn't perform the Find - won't proceed, saying "The provided find criteria are not valid" etc Apparently I can only populate in find mode with a fixed string criteria, not with any variable. If I change the code to Set F…

Hi All, As mentioned on a topic I have a script in my DB, my Scenario while inserting the date from - to without fill the employee ID and press enter should give all records in the visitor list. on other hand insert the employee ID in Custom in the Search field to give a specific record for the employee only.

I am trying to build a FOUND SET based upon field data from two different tables. The FIND logic would be a logical "or" search. From within a script, I want to find a set of records from one table based upon a field value, then add records to that found set based upon a field value in another table. Is this possible? If so, what's the process?

I have a field with "Y", "N" or <empty> as values. When I search on the field for "Y", I get all of the Y's back and one of the "N" There are many more records with N in the field.... I just get one... the same one every time. I am performing the search manually. The environment is FMP 15 against a FM Server 15. Any ideas?

I have a layout that finds a list of results based on a group of fields. I now want the user to be able choose which of those found records they want to print in a report. I am thinking adding a "print me" checkbox to each record. To do this, I think I need to add a field to the table called "print me" and set the checkbox to "checked". They simply uncheck the box to omit the record if they don't want it. But, that means they have to have Write privileges, right? 1) Is this the way to do something like this? 2) If so, is there a way to allow users to modify only one field (print me)? Thank you!

I'm stuck (again.) Temporarily, I created this goofy setup where DISTRICT_STAFF is the parent to the GRADE_LEVEL table (hence, the portal on the left.) As is, the user clicks the Perform Find button after entering either one grade level or a range of grade levels, but that's no good because what if the user wants to find grades 2, 6, and 10 (and no others)? On a lark I thought of using a checkbox'd value list but I can't even think of how to go about coding a script to find the ticked grade levels. Any help with figuring out the schema, or a better way of doing this, would be greatly appreciated.

Hi! Thanks for taking the time to try and help me. I have a script that uses a field named EmailFind. If I enter a karate belt color such as Black it finds all of the students that have a black belt and I can then send them an email. That works fine. However, if I wanted to find, say, all of the black belts and yellow belts I type in black...yellow and it finds every belt starting with a b through a y. I understand this is how Filemaker searches. Is there a way I can pick 1, 2 or more colors (the names for the colors) and only find those? Any help is greatly appreciated. Jim

A table with 100ks of records should be checked for exactly identical records and those should get removed. So I concatenated all fields which have to be included into a single string. But when searching for duplicate records it failed to find the exact duplicates using "!". Some were the same up until the e.g. 176 chars, but then different. I assumed that even the word index is not working as expected. @Kevin Frank found out that it can find duplicates until the first 109 chars when 108 are identical but the 109 is different. But asa 109 are identical and the 110th is different it doesn't find duplicates anymore. Here it doesn't matter whether it is a Value or Wo…

Hi FM people! I have one problem which is probably simple to solve but so far solution eluded me. I have a dropdown text field which offers a list of years (Table::Years). This is global field. I have very simple find script: Enter Find Mode [Pause: Off] Set Field [Table::Order Date; Table::Years] Constrain Found Set [ ] Table::Order Date is a date field. Idea is to perform find according to the value chosen from a dropdown list. However, this does not work. I'm getting "provided find criteria not valid". But, if I enter manually one of the years from the list, It works. Same is valid if I write down a year in the script e.g. Set Field…

Hi All, I have a database with around 1000 players, with a report in List view. How do I GO TO a particular persons name in the list without doing a Find which only shows that record? TIA Milton.

Hi there, I am trying to search information like a colour in multiple fields. I'd like to use one field where users enter a search criteria like RED and then search this criteria in multiple fields. For example, field X where RED is entered to search RED in field A, B and C. I made some attempts with a global field without success.

I have a script to email a list of selected items, which works great! But the check boxes stay checked after I run the query, how do I clear them?? With my other fields I simply do a Set field [inventory::Email_Info ; "" ] and it clears out the data, that doesn't appear to work with my checkbox field. This must be a simple fix to clear out all checks, but I can't figure it out, please help 😉 THANKS!!!!

Hello all! I'm working on a very high latency solution. A server in Germany, clients in East Africa. I've a number of scripts that involve a (scripted) move to a different layout, record creation and a return to the original layout. In an effort to speed things up, I've been trying to reduce the data requested from the server by entering find mode before switching layouts, getting an empty found set (say by searching for an empty key field) and then creating any records. If there's anyone who uses this (or a similar) technique and has some feedback regarding performance, I'd be very interested to hear about your experiences. And regarding this particular method,…

I have a script to find records by date, and would like to just include records from the current week, starting on Monday. Meaning, if today is Wednesday, I would like it to include records for the current week Monday - Wednesday. I searched online, and while I did find 1 page that discussed this very thing, I could not make it work. Their suggestion was: Div ( date - 1 ; 7 ) * 7 + 1 which I presume the date refers to a date field? I added it to my script as: Div ( Get ( CurrentDate ) - 1 ; 7 ) * 7 + 1 & "..." & Get ( CurrentDate ) But that returned a number instead of a date (737696...10/4/2020) which failed. Can anyone…

Hi, I have a database that I want to manage training events. I want the user to be able to easily sort / filter / search, with one click and only display the records below of the related entry in the drop down. See attached Pic, the Training Event field sat the top pulls the events from another field The 'Venue' field in the list pulls the same the data from the same field. (But they are different fields in this database) So if I select '11/10/2020 - Webinar' at the top, I want it to only display the entries below where the Venue matches. Is this possible? Thanks!

Hi, I would like to make script to search to display all records if the following Criteria are met Criteria1 - Field1 = "y" Criteria2 Any 1 of below fields do not have a "y" Field2 field3 field4 Sure its simple Thanks

Hi, Not sure if this is the right section. I want to create a row of repeating fields, not in a portal, in the same table so Fields like Type. Date. reference so when I enter a new record , it creates a new one below to add another record and so on. Thanks!

Hi, I have a simple contacts portal in customer list, all works great as expected. I would like to search / calculate if any of the contacts in the portal are a certain 'Type' My simple calculation works, but only seems to look at the top record in the portal, not all of them? Is there something simple I need to change? Contacts::Type = "Admin" Thanks

Hi, Similar my post on Friday regarding renewal dates, which was very kindly answered. Thank you! I want to be able to automatically flag companies compliant / non-compliant based on the expiry dates of their qualifications / certifications. For a company to be compliant they need to meet meet 3 x criteria. I want to create some simple Calculation fields which I think is the best way to Achieve this? Criteria 1 calculation - They all have valid expiry dates for ALL of Renewal1, Renewal2 and Renewal3, is this true then is puts say '1' in that Calculation field? (If any of the expiry dates are blank then they are non compliant) Criteria 2 Calcu…

Hi, We have a database with various renewal dates. Each date field relates to a different renewal type, and is its own field. I can easily do a search on Renewal type 1's date field to bring back results / sort. What i'd like to be able do it is search across multiple date fields, and bring back a list of results if any of those fields say are less 30 days in the future. Is that possible? Thanks

Hello, still fairly new to FM and am sure there's a simple solution to this... I have a table of records that are organised into group by their id prefix One group of record ids may be for example: SV1001 SV1002 SV1003 And another: PT1001 PT1002 etc... When I'm looking at a single record, I'd like to then provide a button that will perform a find that returns all records that contain that prefix. E.g. if I'm viewing record SV1001, I'd like to then be able to return all records with the SV prefix. I hope this was clear. Happy to provide further explanation. Thanks in advanced.

Hello to you all, my first post here. Mac OS Catalina FMP 15 I’m stuck on a script to make a find! I’d really appreciate some help having gone round in circles for a while. I have 7 fields on 1 table. Among them "Ledger" (There are 6 ledgers), "Account" (There are 20 accounts) and many "Category". The rest are, Date, Detail, Credit and Debit. I am hoping to make a find that shows all the related fields from a selected Ledger, showing only the records for the account selected and the category selected. And a button "Go" that runs the find script and shows a list of the result. I have 3 buttons available on the Layout’s header section. They set th…

I am a foster volunteer for a kitten and cat rescue. Each foster has daily medical and health items we preform for our little wards. Years ago I created a simple Foster Tracking database in Filemaker 16 and now use FM 17 on Mac OS Catalina 10.15.x Many of these chores repeat at a regular interval so so my Foster Tracking has charts that when given a start date propagates repeating fields with the next occurrence. Typically foster arrive in litters of 3, 4, 5 or more kittens and all have a naming theme. I've used "Companions of Doctor Who" and "Gins of the UK" for example. This is stored in a "Name Theme" field and displayed at the top of each kitten/cat rec…

how do you send email to committee members in member template using find?

I've got a list of Platforms and a list of Attributes. Platforms are straight up by a unique ID. The amount of common Platforms is about 15, but the total possibilities are in the Mid 3 digit range. Platforms ID Name 1 Apple II 2 Apple /// 3 Macintosh 4 Atari 2600 5 Amiga 6 Commodore 64 7 Windows 8 Amstrad CPC 9 ... 130 ... 131 Emerson Arcadia 2000 132 Bally Astrocade ... The attributes are broken down by sub-categories (SubID) and a unique ID Attributes SubID ID SubID_Name Name 1 391 Sound Devices Supported Mockingboard Sound I 1 270 Sound Devices Supported Roland 32 1 27 Sound Devices Supported AdLib Music Synthesizer 6 23 Inp…

Gents, my Category field has: Phones, TVs and Laptops. I'd like to display on my layout the amout of Phones, TVs and Laptops in the current DB. For all I know is that I need to create three variables and use Insert Merge Field option to paste those into layout. How should the formula look?

Gents, I am a bit confused with Find and Search functions in FM. I need two buttons: 1 - find all Phones from DB 2 - find among those phones an Apple items What functions I have to apply in this case?

I have a table that I need to push a button on and then it will copy data from a cell on another table. That table only has a signature that I need to copy on to this table. It should be so simple, but I can't get it straight. It keeps moving it, instead of coping it. So then next time I try to add the signature to another file it isn't there to add. Here is the simple script command I am using on the button to copy the signature picture from source "Signature_Director::Directors_Signature" to the destination "Subpoenas::Director_Signature". I simply want it to copy that picture from one table to another. What is wrong????? It seems to…

Hi all I have the current script below set to find a random number of records based on the test question requirement (TestQuestionCount) within a found set. The script successfully is able to isolate x number of questions depending on the TestQuestionCount requirement. I have watched the Data Viewer during script debugging. However, if the question count requirement is 30 questions, it's usually finding ~25 records or so. Upon further investigation I see it is randomly (of course) capturing duplicate record ID's. When the system is creating a new request there are usually a couple duplicates, thus my found set is not matching the TestQuestionCount. The error I believ…

I have a value list with 4 names and I would like to do a report that just shows 2 of the names. Can I not just do a script that tells me to find Name 1 and Name 2? Everything I try fails and I cannot find anything on-line for this. Any help appreciated. I tried ="name1", "name2" ="name1" "name2" ="name1" or "name2" ="name1"..."name2" Thanks in advance, The Missing Man

Hi All, wow, it's been awhile since I was last here ! I am playing around with FM18 to see what's new since the days of FM7 and discovered "Google style searches" with a global field and a portal that shows "filtered" results as I type - all good. Is there a way do do the same thing BUT to use it instead of a drop-down list ? I have a WorkOrder table with a clientID field that links to a Clients table to get the client info for that work order. What seems like the simple way to get the info is : a drop-down list showing all records from Client using a value list. What I would like is to replace the drop-down by the result of a google type search so th…

I have a task list that I use to manage work orders at my cabinet shop. This list contains all work orders for all customers. Each record essentially contains three fields: Customer Name - Task Description - Status. Status can be either "Pending" or "Complete" The Status Field is populated with the status "Pending" with an auto-enter upon record creation. When I want to thin the list down to tasks germane to a specific customer I click on that customer's name then run the following script: 1) Set Error Capture [ on ] 2) Copy [ Select ] 3) Enter Find Mode [ ] 4) Paste [ Select ] 5 Perform Find [ ] This script returns a…

Hello. I am experiencing difficulty trying to extract specific information out of a larger block of text. The block of text I have is several kilobytes long and a response from and IMAP server - the complete email - header and all. throughout this text are the words "date" and one instance of "Date". When I use MiddleWords or MiddleValue for "Date" I get all occurrences of the word - regardless of the case. I then started looking for the condition where the first character of that word was Char(68) - which is capital D - yet is still finds all occurrences of the word. (I tried using the "==" in my search, but that can only be used in a Find command.) Left( …

Hi, I have a List View part Sub-Summary when sorted by ProductType for example Mobile Phones, Headphones. When I sort by ProductType, I get this list Mobile Phones Apple Iphone 7 Apple Iphone 7s Headphones Apple Airpods Beats Headphones What I would like to do is sort the records inside the Sub-Summary ProductType without breaking the Sub-Summary ProductType. Mobile Phones Apple Iphone 7s Apple Iphone 7 Headphones Beats Headphones Apple Airpods My idea is to add a number field to the record but the Sub-Summary…

Hi, I'd like to search in a field with validation set to 'numeric only' using the available find operators (e.g. match empty "=" or, zero or more characters "*") but the find is being prevented by the validation. Is there anyway of getting around this without removing field validation? I realise you can set it to allow overide, but would like to avoid that as well. Is this a recent change to FMP? I'm not sure I've run into this issue before in the past. Thanks.

Hello, I have VIRTUAL LIST ----x---- PERSONELL tables connected with cartesian join. On a layout based on the PERSONELL table, there is a "filters" button that fires a script, which gathers data from some tables and presents them in a list (VIRTUAL LIST TABLE) . User clicks on a record (search criterion) , fake checkbox is updated and the search in PERSONELL TO occurs. The problem is that if I don't first close the card window and only then perform the search, the found set in the parent window remains unaltered. Is there a workaround to this? Closing the "filters" card window, performing search and opening again is not very nice to watch. Than…

I'm running a script that should find a set of records within a specific date range. The date range is contained within a variable '$DateRange' which is then entered from the 'Perform Find' script step. Regardless of how I have the date range formatted, the script returns error 500 after the find. I've entered the date both as '1/22/2012...2/26/2012' or '22/1/2012...26/2/2012' or '1-22-2012...2-26-2012' and nothing seems to work. I've run this find request with the value I see for $DateRange in the data viewer while the script is running manually and it works without issue. It just doesn't work during the script. I've run this sort of find before without any issues... the…

Hello, I have an interesting problem that I can't seem to figure out. I have a database of fire trucks, each truck gets its own record in the Apparatus table. within the apparatus table there is a field for what "Group" that fire truck falls into. The field is a checkbox set because an apparatus can be in multiple groups. In this instanced a fire truck can be a LADDER or it can be a TOWER LADDER. They are very different types of apparatus so searching for them would be important. Right now the way I built the script if I search for LADDER I get a return of LADDER and TOWER LADDER records. Is there a way to tighten up the search parameters so this over…

Hi. (If somebody feels this question needs to be moved to another section, please advise - I couldn't make it "fit" anywhere else) I have a table with several items in it. One of the fields is "order_num" (a number), the other field is text. What I am looking to do is be able to reorder that list based on the number - which can change. But wait for it... I want to be able to reorder that list with either a increment and decrement button, where clicking them would either increase the order_num number or decrease its position in the list. A script trigger could then re-sort that list based on the new numbers in that list. What I am not sure how to do is i…

I have in the layout A button that when clicking shows all the records of the database, but I wanted that button to only show a certain record, for example when I log in with account 1 shows the records that are linked to that club, account with account 2 shows the records that are linked to club 2. I leave an example: Click the button instead of displaying all the records showed only the following: Account 1 John Alex Jordan Account 2 Sarah Anne Megan Regards, Lewis

I am at my wits end with this one. This has been occurring for select users for a couple months. We have two locations running the same file. Each with it's own copy of the file on it's own server. This is happening a couple times a day for one out of 20 users at one location and once in a great while for one user out of 12 at the other location. I have a layout with a scripted find over multiple tables. The user enters the find criteria into a global field and fires the script by clicking a button or hitting enter. When the search executes it appears to find the record being searched for and then FileMaker freezes up and closes without any kind of dialog. When …

Ok, I hope this is simple.... I have a headache over this and can't figure it out! I have imported data from an excel spread sheet. ONe of the columns is if the person is a male or female. I simply want to put in the header a count of the number of females and males. I have tried many different things for the past 3 hours. The field name is "Gender". I tried this with value count, and just "count", and can't see to pull out the word "Male" out of the 40,000 fields. ValueCount ( FilterValues ( List ( Database::Gender ); "Male" ) ) PatternCount ( "Male" ; Database::Gender ) I tried setting it up in the field as a Calculation, …

I have a database that was imported from an Excel spreadsheet. It has rows of repetitive data in it. I simply need to do some query searches in it to pull the data out. Users where asked questions, and obviously there are answers. Each question has a unique label on it 😀, and each user has a unique ID Number😀! So my question(s) is, how do I get both fields, QUESTION_ID and RN_NUMBER to both come up to show me the question and answer? Do I create a script that has in "IF" statement and then another "IF" statement inside of that ??

In the database I received there are ID numbers for each user. Sometimes the user's ID is in 10 or 20 rows, depending on how many questions they answered. I need to calculate how many different ID numbers there are. I obviously need to have someone who answered 20 questions only count as 1 person. So how do I go through the IDNumber field and only count duplicate ID's as a 1? THANKS!

Ok we have a table of names of people which relates to a table of skills that person has and what level of skill they have. Table 1 Full Name Table 2 Skill Skill Level We want to be able to do a find with multiple requests. For example, I want to see all the people in Table 1 which has First Aid as a skill at level 2. Easy! Now I want to see all the people who have First Aid at level 2 AND also can drive a car at level 3. The important thing here is I want request 1 AND request 2. I have tried to do this and cannot figure out a way. Any help?

This should be straight forward but it's not - to a newbie anyway. What I'm trying to do is perform a find based on a date calculation of a membership database. If the find doesn't locate any records, it should show the custom dialog that says, "No membership renewals due in 7 days." When you hit 'OK' it will go back to a layout called "Member Detail" and sort all the records in the database. If it finds any records it will go to a new card window, sort them and display the records found. The problem is when it doesn't find any records the default Filemaker dialog pops up first saying, "No records match this find criteria." and has the usual 'Cancel', 'Co…

I've got a pretty basic summary layout that I'm trying to do a three field search on and FM18 just spins and spins on trying to complete the file. The fat client and WebDirect both seem to lock to the point where I have to terminate both the hard way. Is there something I should be looking at? Seems to be a relatively simple task that FM shouldn't have a problem with. Thanks

Hi All, I am trying to find duplicate records based on two fields so my fine criteria are: QItemSerial ! Type 1 It is finding duplicates but also finding records that are not. What am I doing wrong. The screen shot below has the records sorted by QItemSerial

Hi good day, Im new in the community as well as a filemaker user. im developing a system for a small office here in our local area, and im having problem with date field when searching. Its like when im trying to perform a find the date format suddenly change. Im using the drop down calendar in find mode, when i click the date it entered as MM/DD/YYYY as what i want it supposed to be, but when performing find or hitting enter the date change into "04/*/2019 09/*/2019 */*/2019". I already checked my machine date format just like anybody else in other forum recommended. But i tried to create a new simple database file with date field just to check if is…

Hello again everyone! I have a hopefully quick and easy question. Our database has a lot of older records that need to be deleted. When we did a search for these records, and told the system to delete all of them, it seems to take FOREVER!!!!!!!!!!!!!! These 25,281 records would take well over two, or three hours at that rate it is going, and that is only one month of records 😞 . We are simply searching for them, and then selecting, "delete found records". I have tried creating a query to delete all shown records, and had the same results. Is there any quick way to delete a lot of records? THey are NOT associated with any other table in the database e…

Hi, I am trying to search contacts in a list that have been modified in the last two years. Is this possible? If so, please walk me through the steps. I appreciate the help. Thank you!

Hi there I have a list of building elements in a table each with a unique primary key against them. The table contains all kinds of info about each building element such as cost. The list contains item such windows, doors, painting etc and the idea is you select an element and the system gives you a cost. At the moment I have this set up as a series of conditional drop downs so you select cladding, type of cladding, exact product so the selection is narrowed down as you go. This isn't quite working however because the users can never find what they need. They have asked for search mechanism where the user types in the word or building e…

I have two tables: Workers Clients Workers work for multiple Clients/Clients have multiple Workers. I have a join table that contains a record of each Worker/Client relationship. Each Worker/Client relationship is managed by a Supervisor. Multiple Supervisors can manage the same Worker but with different Clients. The record in the join table includes the name of the Supervisor. Initially, I was asked for a report to show which Workers are managed by which Supervisor, and I followed a Filemaker help article to create a self-join search so I find only the first instance of a Supervisor managing a Worker. Now I’ve been asked for a re…

FileMaker Pro 16 Advanced (16.0.5.500), macOS 10.13.6. I have a field (text, indexed, not global) that is searchable" (green) in Quick find. When I enter a Quick find in the form of subdomain.domain.com , I get 16 found records, but a particular record (in the form of server.subdomain.domain.com ) is not in the found set. When I do a (regular) search on the field of interest (using subdomain.domain.com ), I get 16 found records, but again, does not inlcude the particular record of interest, in the form of server.subdomain.domain.com. (Note: there is another field that contains some records with subdomain.domain.com , so that accounts part of the difference in found record…

Is it possible to find matching records in my database using a list of imported records. my existing database contains a field name BinRef and contains a seven digit variable e.g.: 1234567 plus an ImageRef which always has 5 Digits 12345 I have another imported table which contains up to 100 records with examples of the variables i need to find in my database above. Without manually finding every match using the "new record request" how can i script a find function that will match all 100 records in my imported table. ? Thank you in advance to all Guru' who can help.

FM 17 Pro. How can I sort based on a value list? The sort dialog does not offer this option.

Having a bit of trouble getting the find function in a script to run. I can't really post the script here because I'm using the Japanese-language version, and most of you wouldn't be able to read the text, but the only problem is with the find function. I have two tables in a single DB. Table A is list of people I need to pay, each person with a unique ID (text). Each record is marked with that ID, and a calculated field saying "Paid" or "Unpaid." This list is used to actually calculate the total amount payable to each person that month. Table B is a list of payables, each record for a single payable, and marked by amount and ID. The script is supposed to go …

I arrived at a solution that is working now. The problem I was trying to solve: I have a field containing a bar code number, and want to have a find executed against the data, but if the first two characters of the barcode were "AB", to strip those off before executing the find. The solution: Use a custom dialog box with user input to scan the barcode; store the results in a global field; run an If statement, and if the first two characters are "AB", set a variable to the value minus the left two characters; perform a find using that variable. --------- Following is what I tried before the working version. I'm leaving it here mainly because I…

End Result: With two related tables, I want to search one ( Searching for 5 digit codes) and then find in a second related table all the records that don't exist (empty name). CURRENT FMP DATABASE/TABLES Table 1 = NEO LIST. Field: NeoID. (7500 records) Table 2 = CONTENTS. Field: FilmNameID. ViewerName. (6000 records) NeoID and FilmNameID are related. LAYOUT Layout = NEO and references NEO LIST. Its also displays CONTENTS::FilmNameID and CONTENTS::ViewerName. Search for a 5 digit NeoID, and the layout correctly shows 4000 results of NEO ID, CONTENTS::FilmNameID and CONTENTS::ViewerName. FAIL…

I could write one myself but depending on pricing, might be easier to just buy it off the shelf. My google-fu is weak and I can't find it. Anyone recall what I'm talking about. It was maybe a plug-in, maybe just a product definitely for FileMaker-specific. Did cross-table searches FAST in a pop-up window as well as approximate string matching (fuzzy find). So I could type in John and get results for Jon, etc. Don't remember much else. I'll be very impressed with anyone who can answer this based on the limited amount I included. That is all. I think this was the description of the session: Fun with FM Search Results I am going to show a f…

Hi there, I have a topic that can take the chosen value in a vertical radio buttons list. But some values in this list have a description so long that it can not be displayed on one line. So I need a "return to line" in the list of radio buttons. (see attached photo) How to do ? Thank you for your wise advice. V!ncent

I have a global field, call it "Loans" that holds a list of return-separated ID values created during a library check-out process. As part of a script that takes the user to a list of those items, I would like to go to the new layout and perform a Find using that list of values. Can someone suggest the simplest way to do that? I know I can base a relationship on this 'Loans' field and use 'go to related records' and that will work, but there are some other complexities that arise if I take that approach involving navigation, and I'd rather not add another TO for this little task. I suspect that as usual there must be a simpler way I'm overlooking? Thanks!

Greets, all: I'm trying to wrap my head around how to enable users to perform Finds when the values are stored in a child table. The attached is a portal with two fields, KeywordMarker and KeywordText; the former is used as Boolean field to store 1s and 0s, and the latter is to capture Keyword text. The idea is to have users tick the KeywordMarker field that incorporates a button that fires a script to toggle the contents of that field (to 1 or 0, represented by an X or null). The button would be placed (inside/outside?) the portal where another (radio button) field would have two options for the users to choose, Exact (for exact find of the parent records that conta…

I'm using FM 12 after most of my databases were created in FM 11 and I had to upgrade for Yosemite (yes I'm old school), but I can't get finds to work as I want them to. If I create a simple find script such as; Go to Layout Enter Find Mode [pause] --- to manually enter search terms Perform Find Go to Layout.. What happens is that when I type in the search string and hit the Enter or Return keys, nothing happens. The blue Continue button is there but not responding to the keyboard. A similar thing happens when I search on a multifield page - the Enter key just tabs to the next field where it used to trigger the search in FM 11. Ca…

Okay, I'm going nuts here I'm trying to do a 2-criteria search: Find all records which have 1) Duplicate values in Field A AND 2) in which Field B is Empty for two or more *of those duplicates* In other words, I'm trying to find pairs (or trios, etc) of records that have matching values for Field A, and for which Field B is Empty. Can anyone instruct me on the right formulation for this find?

I have a layout (CLIENT DATA) that has a fixed field (CLIENT STATUS). On a different layout (NEW SEARCH) there is a calculated field (DQ DATE RANGE) that is a based on an entry of a starting date (e.g. entry of June 7th generates a text field of 3/30…6/7). A third layout (LINE BY LINE DATA) contains a large number of records that are related to the first layout by an ID number. This layout has fields that show data for a transaction date, a Client Status field, and other fields related to the transaction, I am trying to construct a script that performs a find in the LINE BY LINE layout. This requires placing the layout in find mode, placing the date range…

Hi guys, I have two questions, 1- is it possible to change the title of a button based on the layout mode using a script e.g. a button with the title "Find" will enter into find mode once in find mode the title will change to "perform find" I do plan to have a button to cancel find mode Thanks Rudy

I have either a txt file or I can do a copy and paste. I have 1600 records and I would like to find by part id, 50 of the 1600. I could manually type in each of the 50, but there has to be a better way. Any help is appreciated.

Using FMPro up to version 11 I used to hit enter or return to trigger execute find, but using the templated databases that come with FMP 12 I find hitting enter or return just tabs to the next field. How do I revert this to older version of Find triggers as described?

Hi all, I have an odd situation where I need to write a script that loops through two tables and finds not only records with exactly matching string values in a field but also that exact match AND one or more additional string values. Allow me to explain: The parent table contains a record with a field named Movie and a record with that field containing "This Movie". The child table contains a record with it's own field named Movie and a record with that field containing "This Movie" also. Easy find there. But the child also has another record with the field containing "This Movie (2007)". So how can I find both child records? The conditions are always that the…

Hello, everyone, here is what I want to do today. I’ve learned how to create a field that provides a running total of my movies/records. Thanks to everyone here for teaching me how to do that. I’ve also learned how to perform a find that omits duplicates of my movies/records. When I perform that find, the running total field displays the total number of my records, minus the omitted duplicates. When I select SHOW ALL, the absolute total number of records is displayed. Again, thank you, everyone, for that. What I want to do now might be considerably more complicated. Here is what I want to do: I want two completely separate fields in my footer. I w…

Hello, everyone, here’s my latest question. I hope this is the proper area to post it. I’m creating a flat-file movie database to keep track of where my movies are in my home. I added a serial number field, which gives me a running total of how many movies are in my collection. But I think I need something different, and am requesting help for creating it. I have several duplicate movies in my collection, and I don’t want the duplicates included in any total count of my movies. At least, not in my forms or reports. I don’t care if duplicates get serial numbers, and I don’t care if the count in the toolbar (i.e. 516 unsorted) counts them all. I understand that th…

Hello all! I'm sure this will be a very basic inquiry for the gurus, but I can't seem to wrap my head around it... My old FM database was a simple flat file affair, and searching was pretty much a no brainer. After rebuilding it as a relational database, I'm noticing that my searches are... impossible. Many of my fields are based on value lists, that are in turn based on related records. When I enter Find mode, and I select a dropdown that is set up in this way, I am met with "No Values Defined". Now, it makes sense that no values are defined, as I am in Find mode and not IN a record that is related to the choices in the dropdown menu, but I suppose I am wonderi…