Finding & Searching

Hi Forum. Heres a cutie of an excerise. I have a Database with User Records. Each Record has name, surname, adress of a user as well as a tagfield. If I delete a user, I dont really want him to be deleted so I put the word "deleted" in the tagfield of the record. In all subsequent operations when I list records I of course only list the ones that do not have "deleted" in that field. Happy sofar? Okay. To Print a record (Say that of Mary Jane), I need to be sure that my found set is one, as Im printing quite a complex records with a print preview and pagenumber calculations etc etc etc to be straight I *must* use a found set of one here. So after printing I get back …

I'm trying to see if there is a way that I can speed up Finds when searching a calc filed that can not be index because of a related filed. Is their anything that can be done? Were searching based on the Job status Calc. With over 200,000 records it takes 30-40 Sec. Quick break down. Table:Jobs Job ID Job status Calc. This can not be indexed as the calc is based on the Parts: Parts Status Table:Parts Parts ID Parts Job ID Parts Status Relationship Jobs: Job ID <--> Parts: Parts Job ID

I need to find records that meet a certain request when using a script. I've got 2 fields that I need to find across. Status and Timestamp. (Timestamp is a timestamp field) I need to find all records that Status = "Approved" Also need it to return all records that Status = "Complete" and have a timestamp range from 3am until Current time. I'm not seeing how I do multiple find request using a script. Thanks for your help.

Hello to all. I what to create a invoive layout. when the user has to input a item there will be 3 options to find it. Barcoce. Item Name. unige serial. and after they input one off the above options it will input from a look up the other 2 options and value and the price. what is the way to do it? Thanks Dani

We have a table of job reports which displays the following fields: 1. Job number (unique number) 2. Job Title (text which is autofilled using the job number) Portal results showing a list of work done: 3. Work Done (number) 4. Hours (number) 5. Cost (number - based on an fixed hourly rate multiplied by Hours) Beneath the portal it then shows a total field: 6. Hours Total (Total of all hours displayed in the portal) 7. Total Cost (Hours Total multiplied by a fixed hourly fee) --------------- I want to create a new table with a portal search that I can type in multiple job numbers in a search field and it will display a list from the table above in the following fo…

This is a very begineer post, but here it go! Can I make a text field a search field in a layout in browse mode? I want it to be a search box that stays on the header all the time that instant search one specific field without having to go into find mode or change layouts. Then, if that is possible, could I have few of those searchboxes inside a popup menu? I saw few examples of the quickfind search field showing inside layouts but I really couldnt figure out how to reproduce it! What I tried so far is having the fields I want to search into the popover with a button that perform the search, it simple says nothing found, how to get the content from the text field …

Hello there, I am building a invoice layout that get the records to display and calculate total from this method, I select the records then send the result to a printable layout. Ok. What I want is to be able to save that result, that invoice, inside the db, probably on a separated table, that would track the generated find result as a new record, as a new invoice. So, everytime I do a search and print, it will generate a new record on the affiliated table, give it a serial number and save the info from the records that I had selected (I need RecordID and AmountDue from the records), the thing is, how do I get the said record ID to save the find result on the other …

Hello there, I found out this guide on how to have a find based on "select" records, it worked very good, now I want to have a print layout that shows two fields from the selected records in sequence, lets say I selected records 1,2 and 3, and I want to invoice to show the amount due for those records so it should print something like this: "Hello there, you must pay for the orders 1( 50$), 2(30$), 3(40), thank you." I can get that said script to go to a print layout but will list the records in list view one under the other, how to line those one after the other? Thank you very much once again!

I have an attendance DB File that takes attendance of participants. By simply scanning their barcodes, their timestamp are recorded. on a more detailed explanation, I have three tables: Participants, Attendance and Attendance_Line_Items. The Attendance_Line_Items is presented in a portal placed on a layout from the Attendance Table. That is just the structure of the file. From the portal records, I have managed to calculate the TOTAL ATTENDANCE, TOTAL LATE participants and TOTAL EARLY participants. But I am currently faced with two major challenges: 1. How to get the Total of those that did not attend with respect to the Total Record of Participants I have? 2. H…

I have many records on a single List View layout, I want to be able to quickly show only the records based on this specific field called "store", on that field I have set a value list. So the idea is, a drop down list that when I chose one from that list will show all records with that criteria on the "store" field. Without changing the record data on that field permanently, just to filter by. Right now I am trying with OnObjectModify but couldn't figure out a script that does a search specific on that field using the item selected on the value list drop down. Help is apreciated, thank you very much once again!

Hello there! I have built a DB to my tech store, we have many service orders of the repairs we do, these have the Date field to auto fill on creation of the record, what I need to do is display filter the records per month when in List Mode, I only it to show only the records of this month, and have a tab or a button to go to previous/next months, what is the best way to achieve that? Thanks!

Hi there, I'm trying to find a way to filter a portal by a date range without much success. I realise this can be done using a relationship where the key field is dynamically updated but I'd prefer to use portal filtering (if possible) Here's what I've tried: Vend_Sales_Lines_api::Date = ( ProductSalesReportLines::StartDate & "..." & ProductSalesReportLines::EndDate ) (didn't work) Tried a calculated field that contained the date range also: Vend_Sales_Lines_api::Date = ( ProductSalesReportLines::DateRange ) Still no luck. Before I keep going I want to see if it's possible to filter by a date range or not.

Hi, I've assembled a database of soldiers who died during WW1 (it was begun using Appleworks on a IIe, just to give an idea of exactly how long it's been in construction). For using the database with Filemaker Go on iPad / iPhone, I want to simplify some of the Find requests to allow only the use of checkboxes and buttons. This would be useful when 'in the field' at the actual battlefield and in the associated cemeteries, for on the spot information regarding the soldiers, as well as for details of the military actions, etc. that occurred there. I want to place a Field in checkbox format - for examle 'Force' (indicates the country of origin) inside a Popover,…

I'd like to verify that a number of fields do not have any carriage returns (¶) in them. I already have an auto-enter calculation that I am adding to each field to prevent this from happening in the future. One solution is to create a secondary calc field that will return "1" if a carriage return is detected in 'FieldName': PatternCount(FieldName; "¶") > 0 If I search for "1" in this secondary field it works great (and I can fix individually or quickly via Replace Field Contents), but changing the calculation is tedious to do for a large number of fields. Is there instead a way I can enter Find Mode and enter something directly in the field I'm interested in…

I'm doing a simple date range search on a report. I click Find, then click in the Creation Date field, which displays data in the MM/DD/YYYY format. I search for 3/1/2015…3/20/2015, and even though there are several records on 3/5/2015, it tells me no records match the search criteria. I assume I'm doing something stupid, but no idea what. I just tried searching for 3/4/2015…3/6/2015, and sure enough, all the 3/5/2015 records show up. Now I'm even more confused!

Hoping someone can help point me in the right direction as I have never created my of custom function, but I think it is what I need for this... I have the following fields c_search ( a calculated field that adds brand, product, barcode, part number into the same field) zt_SearchString ( a global field where the user enters there search criteria ) c_SearchMatch (a field with a calculation that ranks the search result on on several criteria, whether it is a customer favourite - Raises Priority greatly How many times the string appears - 1 point for every occurance as well as c…

I'm trying to use variables in scripted finds but it appears that the Perform Find step cannot parse repetition references correctly like other FM functions can. Example: $var = "Active" $var[2] = "Web" If I try to insert these into a find as $var[1] and $var[2] I get Active[1] and Active[2] as the strings inserted into the target fields. However, If I throw up a dialog box with $var[1] and $var[2] in the title or body, they parse out just fine to their respective values. I can't find any documentation on how variables with repetitions are supported in scripted finds. From what I can see, they aren't. I know I could just rewrite the variable na…

Hi there, Can someone please help with script find situation, is it possible to create multiple find requests and have only one test for all finds to exit script if found count is zero? As in the case of the script below, even though the first search will return result, if there are no records in the last search, the script still exits, how do i control this properly? Go to Layout [ “Console_Guided” (Console) ] Set Error Capture [ On ] Enter Find Mode [ ] Set Field [ Console::Tour_Start; Get ( CurrentDate ) ] Set Field [ Console::Operator_Type; "Chamäleon" ] Set Field [ Reservati::Final_chk; "Yes" ] Perform Find [ ] Enter Find Mode [ ] Set Field [ Console::Tour_Sta…

I want to be able to create indexes of words or phrases in documents in container fields. The documents would typically be PDF or Word files, but there could be other formats (excel? RTF?). Ideally, a (1) search on a word or phrase would (2) highlight the word or phrase, (3) parse or otherwise detect and capture that particular page number in the document, and (4) construct an index of this data. I can see (at least conceptually) how to do 1, 2, and 4, but how can I determine page numbers in the document in the container? Ideas, hints, approaches?

Hi everyone ; I'll be very grateful if you can help me on this : I'm keeping all my blood pressure and glucose readings as well as medicine & food intake in FileMaker - all records have a timestamp,and I would like to know how I can find the records of food or medicine AROUND a certain timestamp,namely if a glucose reading was 180 with a timestamp of ,say, 3/2/2015 10:18:42 how can I find all records A ) on the same day of this record B ) five hours BEFORE and AFTER this record. This will help me alot in pinpointing the exact cause of sudden elevations in bp or glucose.. I've been trying to copy the said timestamp and enter FIND mode and pas…

I'm trying to see if there is a way to perform a find but Constrain the find to a set of Related Records. Basically, I have a script that has a GTTR script step that I then want to perform constrain found set script step on to give me a sub-set of matching records. It seems like a fairly simple thing to do however I haven't had any luck constraining the Found Set of records using the Constrain Found Set script step, Perform Find with Omit, etc. Obviously there are many ways around this however I feel like I'm overlooking a very simple solution to this problem. Am I missing something?

I have a invoicing database whereas all invoices are referenced by the "case no" field. Unfortunately when I designed the database eons ago I included using the "case-no" field as a numerical field styled as "12-XXXX". Each investigator has their own prefix. The problem is when I do a find (for example 12-1111) it also brings up 12-1111.11 and 12-1111.22. We use the suffixes for additional invoices on the same matter. I need to find a way for the user to enter the case number and only that case number comes up, not the case numbers with the suffixes. Thanks for any and all help.

TWO questions from a newbie to Filemaker Pro. Question 1: How do I write a formula that performs a calculation on only one type of field content? For instance, in a field that I call, "unit_type," I have many text values (pool, riffle, glide, rapid, cascade). I only want to make calculations from the "pool" data. I already know how to use the "find" button to find all of the records with "pools" in that field, but I want to write it into a formula (preferred) or a script so I can create a more complex calculation for a summary page. Question 2: This is a question about using the Summary function when setting up fields. How do I set it up so that I c…

Hi all, I'm hoping you can help here. I am trying to make a calculation on a layout. I need to calculate measurements of a specific drop down value that I write in a specific field. The value is called "pool." There are other values called "rapids" and "riffles" etc., but I need to write a calculation that finds running totals of the lengths between the "pools." Is it easier to run a script? If so, how to do I do this? FYI- I have another field that gives unit lengths. Again, I want to calculate the unit lengths between the value "pool". I hope this is clear enough. Ideas? Thanks!

I feel like I'm totally missing something here...I'm searching for an email address in a contact layout. Manual Steps: I look at a single contact record, copy the email, enter find mode, paste the email in the email field, press enter. 0 found records. I should have at least one found record. Alternatively, I've setup a table relationship to do a similar thing by connecting email and it seems to work to, but I need the search to work...(It must be something simple that I'm overlooking.) Thanks

OK, usage scenario. User has a current found set that is being operated on. He wants to perform a new search, does so, screws up the data entry, and as a result there are zero found records. If the user cancels, the browse mode no displays zero records. Preferable option would be to save the current found set, and restore to that. This page describes a way to save/restore a found set, but is overly complicated. I'm wondering if anyone knows if there is an easier way? http://www.dbservices.com/articles/saving-and-restoring-filemaker-found-sets

Hi there, I have a situation where I need to filter a portal based on multiple global fields. I have setup scripts that will filter a portal based on one global field but I can't seem to pull the logic together to have the portal filtered by multiple global fileds. Here's a bit more detail: Global Filter fields: => Globals::zz__ProductDeviceFilter__gxt => Globals::zz__ProductTypeFilter__gxt Which are populated from a dropdown menu. The portal is showing related records that have a "Device" field and a "Type" field. I'm using portal filtering (which is being refreshed every time one of the global fields is being updated) The port…

I was wondering if someone can give me a "high level" view if there is a may to save user's "SAVED FINDS" in a table in the database and give them an interface to select those "SAVED FINDS" in the future. The guys at my job do a lot of repetitive finds and I'd love to be able to store them in a table for them so they can use them in the future. Dom

Hello all: I'm sure this is a very simple thing BUT I'm banging my head trying to do this. I want to do a simple find and then after the user types into a field and hits enter to execute the find -- I want to be able to sort the browselist. I have a simple button on a browselist with the following code underneath: Enter Find Mode [Pause] Sort Records [Restore; No Dialog] The issue I'm having is that the script never executes the Sort Records command. Dom

I'm trying to create a script where one of the steps are to perform a find. I use "Find Records" in the group "Found Sets". I select a field in a table where I want for records whicre empty OR records that contains "x". When pulling down the list there is a selection of operators, but to me it's hard to see how to create this. Are the operators in the dropdown list the only allowed? Can I use f.ex. IF statements? I havn't tried since I'm not sure how to construct that.

Hi Everyone, Its me, Rosalie, very new to the forum and filemaker. I am having trouble to make my search working, I have two tables, Dashboard and Product, In my Dashboard I have a a Global field SEARCH, Product I have A text Field Category FILTER with pop up values The search field is trigger by a script below (OnObjectSave) Script Set Error Capture [On] Set Variable [Filter; Value:""] Set Field [Product::Category Filter; ""] Freeze window Perform Quick Fine [Dashboard::Search] If[Get (LastError) = 0 Halt Script Else If [Get (LAstError) = 400 Show All Records Go to Recor/Request/Page[First] Else If [Get (LAstError) = 401 Go to Recor/Request/Page[First] Omit Multip…

I'm stuck. I have a checkbox'd text field (Phase__lxt_) with five values in it (I, II, II, IV, and ALT) to select from where I want users to be able to click one or more checkboxes to constrain found records having one or more of those values. The problem is, I'm having trouble figuring out how to set up a script/calc for multiple value choices; single value choice is easy, but short of creating separate script iterations for each possible combination of checkbox seems incredibly clunky to me. I get that I'll need to parse the field in some way where I'll use FilterValues, PatternCount and such, but whatever the calc ends up being it'll have to ignore the order i…

Hi all, Because I was in the middle of scripting a new “Find” UI for a project, and concerned (as always) about performance, a comment in another thread that == (exact field match) searches don’t use the index caught my attention. That set me on a course of 2 days worth of testing to see which indexes are created, and used, by different text-field search strategies/operators (i.e., =, ==, " ", *, etc.). The upshot is that I was unable to find a search method that didn’t create one or both text-field index types, and then use them to speed up subsequent searches. Here are my methods and a subset of my results; I welcome any insights folks have into how searches do or d…

is there a way to run the script once i click perform find then it will sort the record. I try script trigger but i fail.

My solution involves checking out and reserving equipment. When a person attempts to checkout an asset, I perform find requests for: 1.) Set Field [LineItems::LIAssets_fK; Utility::GlobalAssetNumber] - looks for the asset to be checked out 2.) Set Field [LineItems::LineItemStatus;"<" & 2] - 1= reserved, 0= currently checked out the find above works well, but I am dumbfounded as the the next step. Assets have different checkout lengths from 1 day to 1 quarter. So, I need to make sure that the date they are reserving or checking out does not match a current record - one of the found records from above.) I have the criteria to check for any date (including thos…

FMP 13-Mac  I have this DB (inherited) and when I do a search in, lets say, template 1 (list view), I get x results.  If I now go to template 2 (individual file view), I see the file that was selected in list view, but have lost the x files found in the search results since now all files in the DB are selected. In other words I have lost the search results.  See pics.  I am not that good in FMP and cannot figure why this happens; I suspect a script or the likes somewhere but do not know where to dig for it.  Any ideas?  Thanks.Â

I am sorry I am still with 8.5 folks. I guess I am a little behind. This is my issue: I am really surprised that I do a search for a specific number in a number field. So, we all know that if found, that specific number will show up on the screen. Well, instead, it finds that number plus a few others..... I did the recovery of the file, but it continues doing that. Does anyone have a way to know the reason for this happening? I will be happy to hear from you guys! Thanks in advance ND

I have created a search field on my database which allows users to search for a particular word on the database, and it will result all the records that match this. However what I want is for the results of the search to be in a particular order. I have many different companies on the database, when a user searches for "iPhone", I want the company which we recommend to purchase iPhones from to appear first on the search results. I am not too sure how to implement this. Any help would be much appreciated.

Hi all Struggling to find an elegant way of doing this, I think script triggers might do the job but no luck finding a solution. I would like certain layouts to always apply a constrain function after a find request is made but also after the show all records command is executed. Is this possible? Thx

Been away from FM for about a year working on other projects, and now I seem to have a brain blockage.. I have the following: 1. Ticket Table UID status (Open, Close, Escalated) 2.Issue Table UID country (US, Canada, Brazil, Italy, etc..) 3. Search Table UID country status Ticket table is joined by UID to Issue Table. Need to allow the user to use the Search table by selecting a Status and adding more then one country (sometimes 130 countries) and display a list of all UID's that match both those criteria. I can make it work with no problem using one criteria, but getting it to constrain to the status selection is causing me an issue. I kno…

Results of a sort in a layout with 14 records displays the following: Team, Division, Min. Roster, Team Members Bulls, A, 3, 5 Eels, B, 2, 4 Sharks, A, 3, 2 Hornets, A, 3, 3 Team members is a summary field (sorted on team field) which counts the items in the sub-summary. I now want to display only the records where the team members is < min. roster (in this case the Sharks information woth 2 being less that the required 3). I've used a calculation involving GetSummary function to display "Yes' or "No' to show who has a full roster. Of course, then doing a search for "No" produces no results because the summary field value is not retained once the search is …

Hi guys, me again - everyday i am stuck with a script this side - i am trying to get around updating a layout field with date values, i am searching through records where by multiple records are belonging to the same entity except that they are entered on different dates. I have created my script working fine upto the point where it returns the records i want to search through. Now from the existing found set, i would like to loop(any other solution to go by) through, find group of records that are related by ID and return the record with the latest date. I have attached script am messing about with........Just what i want is solution to return the records match…

Can someone point me to some examples of exit scripts? I need a script that will (1) find any empty records and delete them, and (2) will locate records that may have required fields missingl, then exit the script on the proper record and layout, so that corrections may be made.

Here's a relationship / data retrieval question that I need to ask by way of example, so please bear with me. I have three tables in a system that works like an online blog. They are Posts, Tags and a join table that links the two in a many-to-many relationship - Post_Tags. Naturally a Post can have many Tags. Question: how to I get all the Tags that have NOT been applied to a particular Post? Ultimately, I'd like the data to show up in a portal, but I'm not even sure how to structure a relationship (or even a raw query) to get it. Thanks much for any suggestions.

Hi there all, Please i need help, i would like to create a script that returns a set of records that fall on a particular date range, there are two fields, startdate and enddate, i would like to view records whose start date falls on a particular week. The date fields(start and end date) already have values so my script must go to that layout and display only records whose start date is on the current week.

I am working on an iPad interface for an existing database of an art Collection. There is a table of Artists and a child table of Works, where each artists can have multiple works. I have separate layouts for displaying Artists, with biographical information, and Works with an image of each piece, and various descriptive fields. In both cases, I want to use merge fields for displaying the information, not Text fields - this is for browsing/viewing only, not data entry. So, for Searches, I want to have a separate Layout including field boxes from both tables, so that the user can use criteria about artists and/or artworks to find what they want. 1) My quandar…

Hi everyone, I am new to FileMaker, but have a long history in other DB related programming/systems/etc. We have a number of existing MySQL databases for our other in-house systems and web apps, and I wanted to get a chance to see how FileMaker could pull in some of that data to integrate with new solutions or to just do some reporting against that data. So far, so good, things are working well. However, I am puzzled by the VERY slow performance of a particular find request I am trying to script. For the Tables in question, all of which are ESS/ODBC to MySQL, we have a Names table with basic constituent information, a Lists table with an entry for eac…

I'm trying to do a Find that finds all People who have not taken a specific Course. There are only 2 tables: "People" and "Course" Course is related to People by the PeopleID. In the Course table there is a CourseName field that is populated by a valuelist that contains a list of possible courses. People take many courses. Some People have taken no courses (and thus have no records in the Course table) This is for a report to show who has not taken a specific course. What's the best way to script this search? Thanks!

Dear FMF, how can I realise following issue: In my application (screenshot in attachement) the user should be able to klick on the red button to filter records for the field left of the button. The same is with the stack buttons in the lower part of the design. This already works and now I want to combine them: If the user first clicks the field button and afterwards the brand button FileMaker should show only records who are the right field AND right brand. Thank you verry much for your help, 51m0n

I am looking to make the switch from ACCESS to Filemaker 12, but am having a tough time on pulling the trigger. My concern is the way Filemakers Find and Replace works, versus ACCESS. In my use, I have to edit the text in fields pretty heavily, and having to bring up the find and replace window, use it, and then close it to select another field, has become quite annoying as well as much slower than having it already selected in ACCESS and then clicking into any field you want to make a change. Is there a way to alter the Filemaker Find & Replace to stay permanent on the screen? As mentioned allot of editing to the fields has to be done, moving data from a "d…

hi all, lately i was wondering what's the best way to determine records which are completely empty!? a customers data has to be verified prior to import and, amongst others, the empty records have to be removed upfront. i can't add a calc field so the finding has to be done via script. or…? enclosed my attempt. anyone with a better idea? thanks in advance egbert p.s. YES, i know that an id should be mandatory but i have to work on the data as is… p.p.s some of you aren't in the FDC so i thought i post the question here too… Empty_Records.fmp12.zip

I am having trouble when selecting a portion of a field (e.g. Last Name, First Name when you want to select just the Last Name). When I place my curser at the point where the First Name begins, and then drag to the left, most of the time (not always) the entire field gets selected instead of just the Last Name. Is there a cure for this?

According to FileMaker's online help, if I turn off field frames on a Layout, it should also hide the magnifying glass icon in find mode. Not happening in FM13. Â Now I understand how this is useful. BUT if I want to place a "functional" field on the layout, which is not intended to be searched in, this is plain annoying. For example a drop down to insert search operators without having to show the native status bar. See screenshot attached. Â Has anyone found a workaround to hide this silly icon? Oh Filemaker... :-(

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 …

Hi there, My school assessment database is about to move into its 2nd year. This means that on the whole I'll only want to view classes from the 2014-15 academic year, not from the 2013-14 academic year. I've a global field for gAcademicYear in the Classes table, and also an AcedemicYear field for each class. - Both can be populated from a value list created by an Academic Year table. I was going to have a drop down, for gAcedemicYear with a modify trigger to run a script, something like this one I found: Does that seem feasible so far? Secondly, I also want to filter down by class type - class, ensemble or instrumental lesson, with in the set found fr…

Hi, I am a FileMaker novice, just picking this up in my free time at college. I have a database that generates paperwork and prints the into reports. I would like to be able to, before printing a report, to see if there were changes/modifications to specific fields within, for example, the last hour, and if there were changes to the field within the last hour these changes could have some conditional formatting where they change color (or something else to note that these have changed recently) I have looked into conditional formatting but cannot find a way to check for fields that have changed recently. Thank you in advance

Hi When I use the All units button (which uses the all units script) it doesn't use the date ranges I put in. It does however use the date ranges on the other two report buttons where the script is pretty much identical. just links to a different layout pretty much I cannot see why it is not allowing the date range on the all units button. Any help on spotting my error is much appreciated. thanks sarah Crowd Breakdown step 3 getting all unit script to work.fmp12.zip

I'm thinking that I'm just looking at this all wrong ... but let's see how folks chime in. Two tables -- people and registrations (assume registrations can be the values A-F). Relationship via a "People ID" The people layout has a portal that shows all the registrations for that person in the portal. I want to do a find that has all the people that have registration A and registration F. In other words, they cannot have just A or F, but need both. Since you can only search on one portal row for the record, and multiple finds requests are an "OR" not an "AND" -- what's the best way to do this? (Note: There's a lot more than A-F, and there's more complexi…

Hi there... If I have a field that contains a drop down list and the user starts typing, filemaker will go to the first entry for the value it finds. As expected. My problem is that filemaker will start from the bottom of the list and work its way up instead of starting at the top fo the list and going down. Does anyone know how to change this. Hope I explained it OK!

I have a Customer table with email address being the unique field If I enter a new customer then the system will tell me if I've entered a duplicate value in this field and offer to Revert the record, BUT I would like it to be a little more elegant... I'd like a script that, when entering a new Customer Record and on Exiting (completing) the Customer Email field: Checks if this value already exists in the database If Not then I can continue editing the current new record If so I would like the new record I'm entering to be reverted/deleted and to be automatically taken to the existing record I tried a script to do a Find on exiting the email field but... M…

Hello Everyone, I am trying to do a search and then get back the result to a new layout. So far I am successful but the biggest challenge I am having is, I have to add multiple address into the layout. But when I perform the search second time, it searches the value and overwrite the existing value. Can anyone help me in fixing this issue. I am using a portal in one of the layout to have the address field, where I got a search button which takes me to the new layout and perform a search function, After the search, I have a button in the layout which help me to take the value and paste it back into the previous layout. I need a way to add more address without ov…

I have a script that I am using to control finds that users are performing but for some reason the 'Set Field' script step is failing with no error. The user selects their search criteria in a set of global fields and then clicks 'Search'. The script then takes the information they've added to the global field and, while in Find Mode, uses the calculated result of the global field to set the actual field for the find. This script has worked fine in the past, however now I am attempting to Find records in a related table and the 'Set Field' script step is not taking the value from the Global field into the related field. The find works if I manually enter the data. …

Help me out guys, I'm probably missing something obvious? Here's the problem: a user can only access a certain set of records, other records marked with "no access". Said user often performs searches and likes to do a "show all" command to see the full picture from time to time. What I cannot do is subvert the show all command to make FM display the current record while filtering out the rest. As we all know it's easy to filter out "no access" records by running a simple find, however that has a side effect of returning you to the very first record while I want to stay where I was! I've tried everything: constrain, extends, even loops - they don't work as I need. "Go …

I am uncertain how to get a particular search result. I am creating a Home Owner Association solution, which has: HOA table with one record Lots table with one record per lot (lots have a lot number) Home Owners table with current and previous (timeDeleted not null) home owners. There is a unique homeowner ID per record. Linked to Lots table by lot number. Addresses table with current and deleted (timeDeleted not null) addresses. Linked to home owners table by homeowner ID. Has a Type field with values such as Owner, Billing,and others. And has a PMA flag (0 or 1). I want to create a search that gives me a list of addresses that meets these criter…

I've attached a simple fp7 file - it works the same in version 11-13: If we Enter Find Mode and set the value with "$var" in quotes, it fails because it places $var in the field to search instead of the $var value. That is what I would expect. But if we use Perform Find [ Restore ] and place "$var" in quotes, it works ... finding the value in $var. It does NOT place $var as the search value. I would have thought that wrapping with quotes would have broken the Perform Find [ restore ], acting as the first example and inserting $var into the field to search. I assume it is because Restore expects literal values ... but I am unclear on the real reasoning behin…

I use two global fields to find a date range of records. I then use these two global fields to print the date range in the report's header. Here is the script that I'm using: Enter Find Mode [ ] Set Field [ sales::datefield; global::gDateStart&"..."&global::;gDateEnd ] Perform Find [ ] How can I modify this script so I can include a second search criteria? I need to select records within a specific date range that also have this criteria: sales::keyfield="X" Thanks

When i am doing a lookup how can use the closest match not the next higher or lower. For example I have two fields: 1) Version 2) Service Pack I run a lookup that takes the version number from one table and gets the match from the next table and copies the Service Pack. In the case that there is no match i am forced to use either the next higher value or next lower value. I want to choose the closest value be it higher or lower. How can i do that? Thanks again! -Erik

Hello, Here is what I am wanting to accomplish. I have a contacts database that contains a number of your basic contact fields (i.e. first and last name, addresses, phone numbers, emails, etc.). I want to add a feature into my solution that will attempt to locate duplicates. I would like to present this data in a simple format that could be followed up by the user to determine which record is kept, deleted or perhaps merged together. My question is how to 1) find these records, 2). display all of the possible records and their accompanying duplicate records in a list, 3) provide options for then following up. I am open to working with Execute SQL, relationships, etc.…

I am feeling sooooo stupid! I am trying to do a Find on a Job table using a calculation field: cCountProductionDays which is defined as: Sum ( JobToProduction::cProductionDay? ) (which is a boolean field in the Production table signifying whether or not the Production record is complete) Extremely simple and straight forward. Displays correctly on Job table layout. When I do a Find operation from Job table using 0, or >0, or =1, or <10, or .... anything - I get "No records match this find criteria" I am CERTAINLY not new to FM, but at the moment I'm completely Brain Locked!

Hello, I hope this is the right section in which to post this topic. I am wondering if there is any way to prevent FileMaker from showing all records when the current found set reaches zero records. For example, when an employee logs in, they are shown the found set of their own work day records for the current month. I want to allow them to delete records, say, if they accidentally created an extra record that they don't need. However, if a user deletes all of the records in their found set, then FileMaker shows all the other records in the table. I have set custom access privileges such that the user cannot see the data from these records (the records show "&lt…

Hi all, I am facing a strange issue in doing searching/relational link for a DATE type data. I am having 2 global fields (g_StartDate, g_EndDate ) to take input (DATE) and there is a column exist on the table creation date (DateCreated_xd) based on the input date range I am filtering list of records. While I do the search its working as per the requirements but when my client use the same file with same date range it doesn't fiend anything. The issue is not in my programing/scripting but the issue in the DATA. I am from India and I am using a system which is having system settings (LANGUAGE, DATE & TIMING) US English. Where as my client is an Australian gu…

I use "Go To Related Records" from table "Inspections" using Layout "Reporting Template" After that runs, I have to run a "Sort Records" command. This template gathers data from fields within the Inspections table and presents it in a report fashion for customers. This report gets printed to PDF and is the deliverable. When I use Go To Related Records, I see only the record I want to print. But if I dont include the Sort Records in the script, it reverts to creating a view with every record in the database. each report is about 10 pages, so instead of a 10 page report with just the 1 record I want to report, I get a 500 page PDF with every record in the d…

I'm not sure what is going on, but here is the situation. I have a FileMaker Database residing on a FileMaker 10 Server I'm using FileMaker 11 Pro Advanced client The FileMaker database is linked to two SQL servers via two different ODBC connections Both SQL databases have 1 table each in the relationship database with a relationship setup between the two The relationship works, evidenced by setting up a layout with related fields displayed and populated Now, here is the problem. Searching in these related fields (in number 5) doesn't seem to working. I've tried searching with wildcards and with exact values, all of which return nothing. I'm at a loss, and…

Hi. I have a query that somebody may be able to help me with. When I see a solution I will kick myself for it being so simple, but at the moment I have gone in too many circles. The concept is this: There is a start date and a finish date - for the sake of ease, let's assume its a hotel (it's not by the way! lol. The list of clients is shown with a book-in date, and once they've left, a checkout date. Whilst they are still there, there is no checkout date. I am looking to get a snapshot in two different scenarios: The first scenario is to provide a single date (lets call it date#1) in the past and see what the occupancy status was at that time - so anyb…

Good morning, I am sure this topic is covered/explained somewhere on these forums, but I don't know exactly where to begin my search, or which keywords to search for. If someone can point me in the right direction, I'd greatly appreciate it I've put together an employee table [EMPLOYEES] which includes all employees within one department. For sake of explanation, let's say this table holds 100 records (employee names). I created another table [TRAINING_RECS] to hold training records. In a nutshell, supervisors use ipads to train employees. Within the training layout is the field employee name (relationship -> [EMPLOYEES]). I've been able to create a re…

Two questions. The basic setup is that we have class designations. For example 1A, 1B, 2A, 2AA, 2B,11A, 11B, etc. I have a script that takes you to a layout that (among other fields) has the class designations come up and you choose which you want. First problem is if you click on 2A you will also get 2AA. I know it works if you choose 2A and then go into the box and modify it to ==2A, but I don't want the user to have to do that. Second, if I come up with a check off box and they choose, say, 1A, 2A and 3A, it will come back with nothing because it does an and on that field, looking for records that have a class of all three. I want an OR on that by the user clicking on…

Hello Board, I have a table, 'Production_Orders'. It stores a list of Production Order numbers, the quantity and associated Product Stock Code. I have a table 'Production_Logs'. It stores a record of each activity for each Production Order. So a Timestamp, the Production Order Number, the Operator, the Action (Started, Checked, Processed, Packed) etc. etc. These two are linked by Production Order Number. What I'd like to do is produce a list of Production Orders, only when the related records in 'Logs' have certain conditions. So, I'd like one report that lists the Production Orders that ONLY have a related record that says 'Started'. I'd like one repo…

Hi- I have a database which contains 10 years worth of records. It has become extremely sluggish, especially when performing scripts, so I want to create an empty clone of the database for continued use. Is there a way to link the two databases so that any search performed on the new database will also search the old one? Thanks Jon

Many years ago when I was a teacher, I used a FileMaker Pro database which allowed me to search for students through the use of a field with a drop-down menu with the following value list: “All, A, B, C … X, Y, Z” and a portal which displayed the search results (as shown in the attached file). When a letter was selected from the drop-down menu, students whose surnames began with that letter were listed alphabetically in the portal. When a surname was clicked in the portal, you went to that student’s record. When “All” was selected from the drop-down menu, all students were displayed in the portal. I now work in the fundraising department of a rehab hospital an…

Hi, Im stuck with something really simple, please can you give me some assistance as I'm having a brain block. I have two tables, Lets say they are called cars and customers. In the car table it will contain the following. Registration number Model Engine size no of seats. In the customer table it contains information about the customer but also the info about the car that they own (using same field names as above). I want to be able to type in the registration number in the customer layout and for it to look this up in the cars table and 'autofill' the fields (model,engine size and no of seats) in the customer table based on what it reads from …

I have a database with some 33.000 records (classical CDs). I have a field "Composer" nad I want to search/compare with this list: http://en.wikipedia.org/wiki/List_of_21st-century_classical_composers. Have I got some of these composers in my database or not? Unfortunately my database has become some kind of a mess over the years. I'm still looking for time to harmonize the entries so that they have a uniform syntax, but they are not at the moment. Here are examples of instances in "composers" in the database: Woods Kenneth / Orch.Of The Swan Tryon, Valerie / Royal Philharmonic Orchestra / Woods, Kenneth Minton, Phil / Chen, Audrey Samuelsen, Stanley Alda Mag…

I have a layout set in list view, of transactions relating to a given client. I have set up a drop-down list containing the following filter criteria: Show all Show unpaid Show in credit Show paid I'm having trouble setting up a script (script trigger based on the drop-down list) that filters the currently displayed transactions for that client. At the moment, selecting any of these filter criteria, causes the filter to apply to all transactions for all clients, and are not being applied to only those showing in the transaction list. Selecting 'Show All' after selecting any of the other filter items then shows all transaction for all clients again. Obvious…

Hello, I have a contacts solution that may contain similar names but not identical (perhaps). My goal is to locate possible duplicate records. What would be the best way of searching through a list of more than 1000+ contacts and display possible "duplicates" for review? These are contacts that may have been created twice and not actually duplicated. So they all have a unique id. I've seen scripts that use the Levansthein (or something like that) method for comparing similar text. Any thoughts? Thanks and regards! Stephen

Would be most grateful if someone could help or point me in the right direction please. I have a button on a portal whereby when clicked I would like it to copy data from a field on it (field named 'Beneficiary Allocations ID PK'), then paste it into a field on another portal (field named 'Beneficiary Allocations ID'), then perform a find to see if that value already exists. Note: both portals are on the same layout. I just keep getting the same message returned ""Aided funding type has already been used". Here is my script: - Go to Object [Object Name: “Aided Funding Portal”] Go to Portal Row [select; Last] Paste [select; Aided Expenditure Projects:: …

Hey everyone I am pretty much new to Filemaker Pro so I am not sure what is going on. I have a database of around 800 entries and I was cleaning up blank entries. Deleted 19 blank entries. Then went to find duplicates in the database and everytime I click FIND, it will show it is looking in 1 entry which is blank. I go back and click show all and I see my 800 entries and able to scroll through them but when I click find. It looks like it is looking at one entry and not my 800. Hopefully I've worded this correctly. Anyone know whats going on? Thanks!

I have a layout with 2 date fields, this is so we can book the appointment dates for the customer. Sometimes the appointments are both on the same day, sometimes on different days. I am trying to find a way to do a search through the records and find today's appointments. if one of the date fields is blank and the other has todays date it must show the record. If one of the date fields contains a date in the future and the other one today it must show the record. If both date fields are for today it must show the record. Any ideas? do i need a script? Thanks.

Hi, everyone, first post , having a issue with privilege set and performing a find. 1. Set up privilege to be based upon a record field and a globel variable. ( working fine) 2. users sees all of the records including the no access records. ( working ) 3. when i perform a find, even for records that the user is allowed to see, it says cannot find record. I can even see the data right in front of me and when i perform a find, it still says no records found. Is this because it cannot "search" through all the records because it doesn't have access to ALL of them? If i set the users privilege set so they can "view" this table then the search works fine. …

Hi All. I would be very grateful if someone could help me with the following please: - I have 3 buttons on a layout in List View each with scripts attached and performing the following: - Button 1) - Find Enter Find Mode [] Button 2) - Perform Find Perform Find [] Button 3) - Cancel Find Perform Quick Find [Projects:: Consultant = Get(AccountName)] Sort Records [Restore; No dialog] On cancelling any find I perform, I want the form to list/show only those records where the field 'Consultant' equals the account name the user has logged on with. I get the message "No records find this match criteria" I have tried creating a global field an…

Hello everyone! Hope everyone has had a great weekend... I have a database in which a user will select 1 of 4 options from a field set as a Radio button (HE, EFF, NI, U). I want to use the Get(FoundCount) function to get the total amount of HE selected, EFF selected, etc... for each different radio button set (40 TOTAL) all on separate fields in another layout; a totals page per say. I'm unable to create such a request. Please help.

Hi everyone, I'm not quite sure how to express myself on this one, but I'll give it a try. I have a list of trusts (it could be companies too) that have people. Some people are involved in more than one trust. I want to print a list of all the people, sorted by the trust using a layout. I wrote a script to do this and it finds all the records, but only prints the information about a person once. So the next trust that the person appears in omits their name. How can I get the information to appear again under the second or third trust? I attach the database and the script is "See All ID Held" and the layout is "Report of All ID Held by Client". The button on the Home Scre…

Hey guys, I am trying to create a script that will preform a search and pull up a record in my FM database with a barcode scan from CMS Barcode. I have already setup CMS Barcode so that the barcode number imports into a text field in my database with a scan, however from there I am stumped. The script can't be that complex, and CMS only offers limited information. Any help would be appreciated! Tomio

Hey guys, this is my first post so please don't be to hard on me I'm searching for an hotel-management solution with filemaker. It's a small hotel but the software / (filemaker solution) should be able to take reservations / to view an assignment chart etc. Is there a filemaker solution for this out there? Filemaker Go integration would be awesome! thanks a lot!

I have a database which serves as a data collection device. Within a found set, I want to be able to navigate to a record (by doing a search within a specific field) which brings up that searched record but still within that found set. In other words, I do not want to loose my original found set after performing the search for a particular record. Any help is appreciated.

Hi all, This problem was addressed before by other users on this forum but the solutions where not successfull or at least they would not work in my case so i will try to be very clear about it In my database is a field called "Tagging" This is a check box field that could have more values such as: Recurrent 2013 Recurrent 2014 Misc 2014 TRTO TRTO-Performance ... and so on. I set a variable $tagname with a value form one table and the find is performed in another table. FIRST ISSUE - When I do a search in a script for $tagname ( value TRTO-Performance ) it is working as expected. However when I perform this find for TRTO it also finds TRTO-Performance…

I need to create a database holding the results from various tests on scientific samples. The only data that is common to the test results is the sample they were carried out on so I have placed the results from each test into individual tables, in my test database I've set up these tables but now have a problem finding records. In order for the database to meet it's purpose I need to be able to identify particular subsets of samples from results across all the tables, For example I need to find any samples that have value x in a particular field of table 1, value y in a particular field of table 4 and value z in a particular field of table 9 (i.e any combination of fie…

Hello everyone, I am pretty new to Filemaker, and I am trying to create a database for my job. We do a lot of parties, and we need to invite a long list of people. What that's entailed thus far is a bunch of Excel spreadsheets. I want to make it easier by creating a Filemaker database based off of Outlook contacts, but categorize them. Importing the information from Outlook is easy, but I've run into problems when I want to categorize. I'll give you an example. If we have 500 contacts, and some are actors, some are managers, some are agents, some are on the west coast, some are on the east coast, etc....I want to say, "Let's invite all the actors and agents o…

I have been working on FM 12 solution. Whenever I try to perform find for an particular Order record, 2 records are found in the found set. Where one record in the found set is the original record been searched for and another record is an blank record where all the fields in the record contains "?". So thinking it as a corrupted record i deleted it. It changed the found count to 1. But deleting the record didn't affect the total record count. It is same as the before And after doing that, I again made the performed a find for the same order no. again 2 records in the found set appears with one of them is an undefined record (Ghost record) that been deleted pr…

Hi all My first post.Hope i make sense. I have a layout with possibly 100 or more fields on different tabs. I would like to be able to select all non empty fields on this layout and then place them in another layout as merge fields. I would like to do this for one record at a time. I feel there must be a way to script this or do it without 100's of "If " statements. Any help greatly appreciated. JeffC

In the cobwebs of my mind, I seem to remember there is a 'trick' to getting FM to allow me to use OR in a find. I just can't locate it on the forum or in my mind...  Here is the situation: I have a membership application. There is a Dues table which allows members to make donations. These donations may be either a single donation or an excess paid over the dues required. This works.  (Note there is a Dues Year and a Donation Year. These two fields record the year of the dues payment and /or the year of the separate donation. This is the 'or' part of the problem)  I created this Donation Layout. It initially shows ALL the donations regardless of Do…

Hello Not sure if this is the correct topic, but here it goes: The search returns a set of records and the two fields I'd like to sort them on are 'locked' and a time field 'TIME'. 'Locked=1' means that the record does not need any attention now but should still be listed, and the time it a deadline. I can sort on one of them without a problem, but I would like to know if I can sort them like this: REC#1 -> LOCKED = 0 - TIME is 16:00 REC#2 -> LOCKED = 0 - TIME is 16:50 REC#3 -> LOCKED = 0 - TIME is 17:00 REC#4 -> LOCKED = 0 - TIME is 17:20 REC#5 -> LOCKED = 0 - TIME is 18:00 REC#6 -> LOCKED = 1 - TIME is 13:00 REC#7 -> LOC…