Finding & Searching

I have a database of committee members - the purpose is to help determine who should be on committees. To make a long story short I have a table for committee information (e.g. eligibility rules) and another table for all potential members with their status and a table linking Members to Committees. The relationships work fine to create a list of current committee members and previous committee members. However, when I go to create a list of potential committee members the relationships don't provide enough information because some members are not yet in the table that links committees to members. I want to use the rules in the committee table to search for eligib…

I have a value list which is red, blue, green, yellow as the values. I have them in my layout as bullet boxes. When I try to make a script to find those that are selected as "RED" it doesn't find anything. How does one do a find for a bullet button value? Thanks Dave

I've got a dbase with about 113K records in it. I need to take blocks of that data (maybe 10-15K records at a time, sometimes as few as 150 records) and edit a single text field across all of them. In other words, I do the search to get my data set, then need to edit one line of text across all of the fields in the search result set. Can I do this? If so, how? Seems obvious, but I'm totally missing this.

New to filemaker - creating a report to only show closed projects for whatever month a user wants to search on. I currently have Enter Find Mode Set Field [PROJECTS::Closed_Date; Month (Get(CurrentDate))] Perform Find[] This only shows records for the current month. Is there a way that a user can enter a specific month instead of Get (CurrentDate)?

Hello! I am trying to implement spotlight search http://www.secretweaponlabs.com/words/2005/09/24/spotlight-and-filemaker-redux-building-a-filemaker-search-engine/ but am getting stuck on the eventscript function. My Script seems to work fine if I run it independantly, however if I try to trigger it with an auto enter function: Case(not IsEmpty(Search Field); S4HU_EventScript( Get(FileName); "Search Script"; Search Field)) the search isn't performed. Has anyone got any advice? If I haven't provided sufficient info - just let me know!

Hi everyone, Before I ask this please bear in mind I am so far completely self-taught in FM and so there is a very good chance this is a very stupid question. I have a table called Projects. I have a related table with invoices in it. I need to make a total of all the invoices from a project, in each of the 12 months. So one for February, one for March, etc. However I can't figure out an easy way to do this. It's just a matter of something that goes through the table of invoices, checks the month, and if it's "1" adds it into the "Total Invoices for Jan" field, for example. I'm sure there must be an incredibly easy way to do this, but the only way I ca…

Hi all this is my First post since joining FM Forums... So here it goes.. What I need to do is sort all of my records based on the characters from right to left instead of left to right.. It is something that I think should be quite straight forward but at the moment after spending 2 days looking at it I am coming up blank. Please help.. Thanks in advance, Scott

This has to be easier than I'm making it, but I need to do an OR statement in specify find request. I can figure out how to do an AND, but not an OR. I need it to find every record, print or Album that scores above 80. I can get it to find every print about 80, but I can't add that other category. Can you help?

I've been using a database of cross reference numbers and I've just discovered a problem... (This is with a 5.5/6 database) I sometimes get a list of requests for our products with someone else's numbers on them (happens a lot). For a short list, we usually just cross the numbers one by one doing a simple find on the manf number's field, but for longer lists I'll turn the spreadsheets into FM files and then relate them to cross to our part numbers. What I discovered today is that sometimes the manf numbers may contain more than one number for the number we carry. In cases like these, I separate them with a comma and a space. This works fine for doing single item…

My Transactions table consists of the following fields: -TransID (auto number) -TransDate (creation date) -TransType (Income or Expense) -PayDate (entered when Transactions is paid) -PayType (also entered when paid - There are different layout for Income and Expense Trans but are on the same table... the PayTypes are different for each type of Trans) -PeoplePlacesID (related to PeoplePlaces table) -VIN (related to Vehicle) -Subtotal (Unstored calc. of TransDetails::ExtPrice) -Discount (Unstored calc.) -Shipping -Tax (Unstored calc.) -Total (Unstored calc. of Subtotal,Discount,Shippin g,Tax) So, I am trying to create a report that will show my …

How does one perform a find based on the contents of a field and what is the syntax? for example find a PO based on the global field gPO Thanks.

Hi - I hope this has a simple answer . . . Let's say I have a Film database with a Title table connected to an Actor Table by a join table called Appearances, so that a single film's actors are reflected in many Appearance records. On the main layout, whose context is the Title table, I have a portal to the Appearances table with, say, 10 slots to enter actors names. Q: How do I perform a find on multiple names, for example, search for films featuring both Bogart AND Bacall? (When I enter find mode, I only get one field in the portal to enter a name in.) Thanks!

Hi all! I have a very flat table of information I'd like to make available to employees. I'd like them to login with their own account, and have FMP find only their records, and let them print, but not allow them to see other records (of other employees). Is there a simple script way to allow access only to a found set on login? I'd love to see this happen: user A logs in, a find command happens for all user A records. User B logs in and sees only a list of User B records. I'd think that would be defined via the accounts and a Script of some sort. Any ideas or resources would be welcome! Thanks Rob

I have a user who continues to use the "Return" key instead of the "Enter" key after populating a field with a PO Number. The result of this is that subsequent searches fail to see a match between the fields containing "PO123" and "PO123" because of the hard return character in the field. How can I set up FMP 8.5 Advanced to ignore trailing hard returns in a field for the purposes of content comparison? (I can't merely compare the values of the first X characters because the field must be very dynamic, as the PO Numbers being entered are from a variety of external customers and have no set format, structure, etc.)

I have a script that loops through my project table and performs various finds based on a presence of a validation code. Example: my ExceptionCodes field can contain many code values (e.g. "P1 P2 P3" ,etc) in the same field. Each code represents a different validation error. After each find is performed, is there a way to copy the found set into another table? Each found set would then need to append the found records to the new table. After the script is complete the new table will contain all records that had validation errors, with some records showing multiple times if they had mutiple errors. The thought behind this is then i could easily group the rec…

I have posted my problem here: Click Click Click Here Thanks for taking time to look at this. James

Hey, Folks, I need some help. I have a Courses table that links to a Calendar Sessions table, where each Course has a Calendar Session for every time it meets. For a particular report, I want to be able to search within the Courses table for all courses that meet (not just start or end) during a given interval of dates. My original approach was to create a calculation in Courses like: List (CalendarSessions::Date) to give me a field with carriage-returned dates that could be searchable. The resulting calculation delivers the desired list. Unfortunately, when I attempt to search for a date range in this field, FileMaker returns ALL YEARS for the…

Hi Just got back to an FMP database that someone was looking after inputting data while I was away for a while. They have left their new additions in red formatted text to highlight them. Now I can go and check their inputs and decide whether to keep them. In case I miss some though - I was wondering if it's possible to search all fields for anything formatted in red. Any ideas how I might try that? thanx gessie

Hello, I have a client database with which I am regularly searching first or last names to get to a particular record. At the moment I simply enter find mode and type the name in the field and search. While this works, I would like to create a slightly more "elegant" solution. I would like a find button to open a new window with fields for first and last name. The fields would have the Auto Compete function turned on and I would like the last names to be filtered to only include those that match the first name entered. I.e. You enter "John" in the first name field, and the only auto complete options that come up for the last name are the last names for clients called J…

I want to search my Plant List table by Project Number and Plant List Name. Problem is this: all the plant lists for each project number are lumped into the same table. Project 1408 has Preliminary Plants, or Final Plant List, for example. I need to search for '1408 Preliminary', or '1408 Final', for example. But I don't know, before searching, what plant list names might be associated with each project number. Still, I need to search as above. I've spent so much time: tried portals, and self-join on project number, and many other combo, but I cannot find a solution that is essentially a 'double find': I want to enter 1408 in a find field and tab to an…

Hi everyone, In my solution I prefer to keep the status area hidden from users. Is there a keyboard shortcut or some other method of omitting a record in a find without the omit button in the status area? Thanks in advance Mike

Thank you for looking. I've just received my FMP this week and have learned how to make custom Layouts etc.. on my customer database. I'm setting up printing customers address on postcards and what I need to understand is how to print only customers with zip code 07728 for example. I've been going thru the manual and each time I think I've got it I get lost, i.e., field control etc... Can you help please? Thank you so much Tom

I'm working on a database where they often search using checkboxes. The value lists for the check boxes contain spaces and this is causing incorrect result sets to be returned. Example list values would be 1. Newsletter 2. Newsletter Do Not Mail If they click the Newsletter in find mode the results returned will include both Newsletter and Newsletter Do Not Mail. Is there a way to force it to find an exact match only. I know how to do this with "" or == but not using a checkbox. Thanks

(See the attached; you may have to change its extension from .zip to .exe for it to self-extract) I'd like to find records within a range of dates in the Parent table via a relationship from the Child table; once those dates are found, I'd like to have a script in the Parent table print the results. The two problems are: 1) I don't want to generate records in the Child table, so how would it look "through" the keyfield to find related records in Parent if no key is created? 2) I tried using GTRR in a script step but, since there's not a key generated in the Child table, it doesn't know how to relate to its parent. (Databases imitating life yet again.) …

I have a report that gives me the production total for each of our parts each month and then YTD totals. I have run into a problem.. on my report I have the part number AB123 its total production is 100, I then have part AB1234 its total production is 200. On the report it shows that AB123 is 300. Is this problem in my sort or my find? I can't get the report to keep the 2 separate. The system runs through 300 parts so I don't enter AB123 manually.

Hello! I have a db in which a invoice numbers and PO numbers are tracked. The relationship is such that multiple invoices can be created against a given PO number. I'm trying to use the List function to build a list of unique Invoice Numbers when the user queries for a single PO number. In other words, the user would like to enter a single PO number a see a list of all unique invoice numbers associated with that PO number. I have the search working: it successfully pulls up a list with an invoice number entry for every time each invoice is billed to a given PO, with repetitions. When I try to use the List function to produce a listing of unique invoice numbers f…

Hi, I'm a newbie in FM, and I'm always trying to improve the database that I'm using to get my work more efficient. I have this question: I want to calculate the price of a transportation based on a range of values, for example: 0-5kgs 4€ 6-10kgs 5€ 11-20kgs 6€ I've created a table with this values, and I want the field to search in it. Can you give me a help. This must be very simple. Thank you.

I recently needed to split up a large group of records into 3 smaller groups due to a problem I ran into when emailing over 1000 users. I figured I would split the record set alphabetically using the last name field, so I wrote 3 scripts to constrain the record set using the range operator like A…H I wanted every name starting with A to H for the first group. I got some strange results and believe it is related to spaces in the field. For example using the A to H range I would get results like these John A. Sara B. It’s like it treated the middle initial as a separate search. Is this how it functions? It also did the same using email addres…

This may be an easy one but I'm stumped. I have a db with the fields firstname, lastname and other data. I'm importing records from an excel spreadsheet. I get multiple firstname/lastname combinations. I'd like to get a unique list of all the the firstname/lastnames without the multiple repeats. I can't figure out how to do this. Much obliged for any help. Cheers, MJ

I have a solution, running on FMS, with a relationship that looks like this... table 1::emp_id = table 2::emp_id table 1::global_thru_date = table 2::thru_date I have a calculation field in table 1 that is defined as table 1::count = count ( table 2::emp_id ) It tells me the number of records in the child table for each parent record. As expected the count displays the correct number of records when I change the global_thru_date field. If I perform a find using the field count where count > 0 filemaker will find parent records that do not have any child records. But not all childless parent records. This works as expected…

In our non-credit database we track programs and registrants. Pretty straightforward. In our registrant table we track the date they registered and have 3 fields for payments (Payment 1, Payment 2, Payment 3) and the date of the payment (Date Rec. 1, Date Rec. 2, and Date Rec. 3). Rarely does any registrant make more than one payment but it has been known to happen. So here was the request made of me: Build a report that counts all of the registrants in each program that registered in a certain timeframe as well as provide the total payments made during that same timeframe. Now, I can search through the 3 payment dates and isolate only those records that hav…

Hi, I'm new so forgive me if I break any rules with my first post. My question is this - my first-ever database was Mini-Office Pro on a PC. Compared to FM it was laughable, but it did have a nice little trick that FM doesn't seem to, and I was wondering if anyone has evolved a way of doing this in FM? Briefly, in Mini-Office I could Search for a set of records, and one of the options was to 'Mark' the found records. Mini-Office would then display the entire list view of all records in the database, but a keystroke would jump to the next Marked record in the list in turn. I used this a lot - it enabled me to see found records in the context of the whole databas…

Given table A in one/many with B. Table B has some number fields and corresponding summary fields used to total each. I have no trouble building a report that shows totals from B for each record in A. The problem is when I want to group the records in A. For example, A has a color field. I want to get the totals by color. FMP will group the results on Color, but still insists on computing totals form each singe A record. I want totals for subsets (unions) of records in A. ???

From the find mode, How can I find all records in which a particular field is empty? The asterisk would not help as it is used for zero or more characters

I've been trying to find an efficient way of doing this but my brain in hurting. I trying to create a report that shows a list of people that have a birthday within 60 days from the day the report is run. I've been trying to use the DayOfYear function to get the difference between today' date and the birthday, but it fails when today's date is within 60 days of the end of the year and the birthday is during the first 60 days of the beginning of the year. Is there a simpler way of calculating this or creating a calc field to use with find? Thanks for help.

Hi all! I could use some advice on performing a find... Namely, I have two related tables: a detail table that contains detail records, and a summary table that contains a calculated sum() based on an amount in the detail table. (For other design considerations, I need to have the calculated sum() in a separate table.) I would like to filter the detail records based on one of the attributes in the detail table so that the sum() in the summary table reflects a total amount only on the found set of detail records. So, I created a script - here's a rough description of it: Go to the summary layout Find all records Enter find mode Set field det…

Hi, I must be missing something, cause I do not understand the way my searches are working. I have a scripted search. The search opens a new window using a search TO, related to the source TO by a cartesian relationship. If the search finds records, I use a GTRR step, From table: (search table) Using layout (the original layout). I then close the search window and the original window. This leaves the user with the result window and the result records. This works fine. Here is my problem: If I start a new search in another TO (using exactly the same method) and the search finds results, the new window shows me the results, but I loose the results of the…

I have two tables: people and schedule. Through a relationship, I flag a calc field in people indicating whether the person has a valid record for next week's date range in schedule--Case(IsValid(people_schedule::date);1;0). I would like to perform searches, etc., on this value, but with tens of thousands of people records, searching on an unstored value is prohibitive. Any ideas how to get a searchable field in people that will update with each passing week?

I have 2 databases that have a relationship on phone numbers On database 1 I want a field that will look at database 2 to see if that phone number is there. But I am not looking for exact match. I am looking for area code - prefix match. Example database one has 222-333-4000 I want to do partial match on just first 7 characters. So if database 2 has 222-333-4125 & 222-333-4287 then it would show there is a match I tried a calculation field If(Left(Phone;7) = Left(TBN_ADM_IC_231::Phone;7);"XX";"") also tried PatternCount (Left(TBN_ADM_IC_231::Phone;7) ; Left(Phone;7)) But they only returned exact matches.

I have basically an inventory management database (has Name, Type, Product Name, etc for fields)... and have a field for [sOLD] pieces of inventory ... what i'm trying to do is, is set the "Find" button, so that it searches the database based on whatever criteria you put into a given field, (Name , search for Tiger Woods for example) and have it ONLY show records for Tiger Woods that are NOT sold (omitting each record that is declared [sOLD]) ... my current set up is -> Button Setup is set to 'Enter Find Mode' and it searches fine (but finds ALL inventory including the SOLD pieces) ... when i 'Specify Find Requests' and set it to Action>Omit Records and Crite…

I have a series of checkboxes that list many product lines. The user is being asked with the check boxes to check the product lines they wish to be a part of the Sales Quote they are creating. Lets say for example I have these product lines. Widgets Gadgets ThingaMaBobs The user checks off Widgets and Gadgets. I then want to switch to the view that the sales quote template is defined in and run a search. However I want it to search based on products that are Widgets OR Gadgets. I have the field defined as a variable $productsToQuote The value obtained from the check box lists all fields checked, however I cannot figure how to search …

I need a script that looks up the date from 8 different fields from a custom dialog. Is this even possible? This is what I wrote and it didn't work: http://www.benecab.com/misc/filemaker00002.jpg Thanks!

I have a database of sales records. I want to be able to sort or calculate the people/business who are most active: example Name: Price: bob $3567 jim $567 bob $4798 bob $467 tim $3568 joe $567 tim $46 And I want the data to show something like this # of records: Name: 3 bob 2 tim 1 jim 1 joe Thanks, Justim

I am trying to figure out the best way to to sort records (from high to low) based on the amount of times the same information was used for a field. Say I had a field called "Name" which can re-occur and my database contained thousands of records like this John Jamie John Jamie Justin Peter George John Sam Sam John Justin Amanda Tim Bert and I want them in this order John John John John Jamie Jamie Justin Justin Sam Sam Amanda Bert George Peter Tim Or even better yet like this so I can sort them by number of recurrences. 1 Amanda 1 Bert 1 George 2 Jamie 4 John 2 Justin 1 Peter …

Hey all...I am so not used to the search/report functionality of FM. Could someone explain to me the following script setup. i have a table of records that I am trying to get where one field is equal to only a few string values and another field is only equal to a few values. the best i can do is give a sql based version of what this script will look like... SELECT * FROM FEE WHERE (Stage = "value1" or Stage = "value2") AND (Status = "value3" or Status = "value4") I hope that helps to define it a bit. YEAH I LOVE FILEMAKER AND THE SCRIPTING!!! Extend Found Set...why not just allow the entering of multiple criteria on the same field in t…

I would like to perform a find on field 'A' so that it returns the records that contain either the value (say) 131 or the value 101 (but nothing else). CORRECTION: ".. returns the records that contain the value (say) 131 *and* the value 101 (but nothing else)." The problem is that this find will operate on top of a previous find. In other words, we cannot simply do a search to omit the first number, then constrain to omit the second number and then invert ... because this will also bring in values from the initially constrained set.

I'm trying to figure out a way to mark only specific entries/records so that when I run a report, only those records appear in the report. The specific problem I am working on is the following. I have a medical database that records injuries of students. Only certain types of injuries need to go into a specific report. Any advice on how to do this? Thanks.

Hi everyone, I can’t figure out how to handle a search over multiple TO’s. I have four TO’s involved here. Customer TO, Products TO, Invoice TO and Line Items TO. All TO’s contain pretty much what you’d expect them to. The only added wrinkle would be that the Customer TO, aside from the standard customer information like name and address, contains 8 questions like Sex, Make, Model, Year, etc. How do you set up a search where you need to search on multiple fields from multiple TO’s. And of course everyone wants a little different search and sees the information in a little different way. Some will want to do a search and see all the records supporting t…

What I would like to do is be able to create a field that would compare 2 database that have a "phone" field relationship. What I am tryomg to do is compare just the area code & prefix between the 2. I can get it to match exact matches but not just the first 7 characters. Example if on database has 222-333-4000 & one I am comparing has 222-333-4100 and 222-333-4200 that it would tagg both of these just from the first 7 characters 222-333. I tried If ( Left(Phone;7) = Left(TBN_ADM_IC_231::Phone;7) ; " -DJM- " ; "") but i still only get hits on exact matches not partial. Also tried PatternCount (Left(TBN_ADM_IC_231::Phone;7) ; Left(Phone;7))…

Hi all I have been using a five step conditional value list to find and display a welder's qualification record. This has been working. But I have been reading on the forum that a Hierarchical portal would be a better choice to preform this task. Not to sure on how to preform this. I have attached a file on the way we do it now. Would someone please review the file and let me known which way you think would be the proper way to preform this task. Open the WPQentry file first Thanks Lionel searching.zip

I am using FM 5.5 I need to find the same records in two different files. When I use the Script I created it just keeps looping to infinity. I have records with a select checkbox in one record, the same checkbox in the second file, I can do the finds seperatly but when I try to implement them in a script it just loops. Thanks in advance.

I have a “Customer TO” that holds the standard customer information along with 5 questions such as Income, Sex etc. I have a “Invoices TO” that holds the standard Invoice Information and a “Line Items TO” that handles the standard line item information. I’ve not been able to figure out how to handle searches. Some of the search criteria is in the Customer TO like Name, Income etc, some is in the Invoice TO like Invoice Nbr, Date etc, and Some is in the Line Items TO such as Item Nbr and Product Description (from the Products TO) etc. Somehow I have to set up a search routine where a user can make the selections and get the correct information and I have no ide…

I have two database files, the first contains a layout which is similar to an invoice. The other contains one table which has all my inventory. Records are very simple-- an Inventory # in one field, and some other fields such as Description, Location, etc. In the invoice form, I have the fields set up as repeating fields. When I type the Inventory # into the line 1 Inventory # field and hit Tab, the rest of the fields in line 1 lookup just fine and fill themselves in. However the remaining 6 repeating lines do not fill in! I have a relationship defined between the two databases, specifically between the "Inventory #" fields on both. The inventory database can cr…

I have a layout with a portal on it and a "search" function which limits the entries shown in the portals by going back to the related table and changing the placeholder variable from 1 to 0 for all the records which should be omitted. (The relationship between the tables is established by the placeholder variable, with a 1 on the record in the layout with the portal) I also have a search function for the table on a different layout, not with a portal, just a direct search, and with BOTH search functions I am having the SAME problem. ALL searches work for any combination letters/numbers and give the right number of records, etc EXCEPT when I search for "at1g"; …

So I need to include a field in a layout in the tab order but I also need to have it skipped over when the computer enters find mode and tabs through the fields. I tried setting "field --> behavior" to not enterable in find mode, but the computer just ignores this, and goes to the field anyway. I noticed you can set the field to global storage and it skips it, but I can't have this field be global storage. Any help greatly appreciated!

Hello, I was wondering if it was possible to specify a find request to compare a field's content with a local variable. this is what my codes looks like. I have two fields in layout called whatever. the fields are called fname and lname. GotoLayout(Whatever) Set Variable($fname, VALUE: globalvariable_1) Set Variable($lname, VALUE: globalvariable_2) Enter Find Mode[] Perform Find[Restore] within the perform find I have specified the following find request. Action: Find Records field criteria Database::fname ==$fname Database::lname ==$lname Please help, thank you in advance. Carlos C.

I've got two related files, each having an "ID" field, and I want to compare the IDs contained in each. I'm running a script in File 1 that opens File 2, and enters a loop. For each record in File 2, search to see if its ID exists in File 1. I don't seem to be able to compare both files back and forth from within the same script. (I also tried loading up all the IDs from File 2 into a repeating field, but then couldn't access each cell with a variable, so I gave up on that.) Here's my script executing from File1: ======================================== Open File2 Show all records Sort them Count them Go to first record LOOP Enter FIND mo…

Does anybody know if in FMP 6 you can search more than one field at the same time? I want to be able to search several fields in all records at the same time by entering the data ones in one field. In other words, there is data in field 1, 2, 3, and 4. I want to search all those fields at the same by entering a search word in a find field I than be able to search all those fields simultaneously. And if possible can someone tell me how to do this? Thanks

Hello all. I have a table called EXPENSES. Each entry in this table has a Project Code, a date, and an amount. I'm really stuck. Is there a way for each entry in this table to have access to a summary of the amounts for all the other entries in that table that: a) Have the same Project Code, and; : Have a date BEFORE the date of that entry in the table. In other words each expense should be able to see the total of all the expenses relating to that project code that have come before it. Effectively, each expense has a running total of previous expenses against that project code. Is this possible? If so, how does one acheive it? If I could someho…

(The following is not strictly find-related, but I couldn’t find a better place ...) Hello everyone, I have been working with trusty old FileMaker 3.06 for, well, since the Stone Age, and it has always served me well. I miss very little. It’s fast, reliable and intuitive. There is one thing, however, that I cannot get to work and that forces me to use other apps such as outliners and text editors. I have several (relatively simple, i.e. non-relational) databases where I need to have the entries (mostly text snippets) in a particular order (*not* sorted alphabetically). Often, there are many such snippets, and I need to order them quickly. In a tex…

I have a table with people and the activities that each person participates in. The activities are a value list displayed as checkboxes. People can participate in more than one activity so (as checkboxes), I can check off multiple activities. How can I find all people who play soccer only- not all people who play soccer AND baseball. (I will eventually put this found set and display it in a list). I think this is a really basic question but I just can't get it. Thanks in advance!

I have a Person database with a linked table of codes. Each person can have an unlimited number of codes assigned to them. So, on the main view for people, there is a portal to the PersonCodes table (joins People and SubjectCodes). When I enter find mode, I can only enter one code to search for. This works fine for OR searches as I can just make a new request. But, I cannot figure out how to do an AND search. Is it possible? Thanks for your help.

Is there a way to save searches to use them again? Or, is there a way to take a search and convert it to a script? This would sure save a lot of time.

This is just a little annoying thing that I don't know if I can fix but I thought I'd ask anyway. When I do a search on last name field (set to auto-completion) I'd like to hit "return" and have it perform the search instead of first selecting the auto-complete and then having to hit return again. I don't know if that's clear. Basically, if I don't type the entire last name before I hit return, I have to hit return twice. Does anyone have any ideas about this?

This is the dumbest thing. I have a set of pulldown lists that work find in the browse mode but when I want to use the same related pulldown lists in the FIND mode they stop working. Go figure. What am I missing?

Hi, Could you help find the number of UNIQUE patients that took the drug "Zidovudine"? I know the answer is 3 because i manually counted the patients, but how can this be programmed. I would like to know the unique number of patients based on a search for any drug. I attached my small file. The account name is admin and has no password. Thank you so so much. testing_unique.zip

I have created a company "to do list" with fields for customers and the employee whom is responsible. I'd like to create a drop down menu and button to sort the records by employee, so the individuals can view their assignments. I know how to do this with the find feature, but wondered if there is a way to accomplish this witha script and global field with a drop down menu? Thanks

Hi In a simple database which handles PBX data, i have two tables. On one there is a list of clients with their respective names and cell numbers, on the other there is the data received from PBX. On the last table I have created a relation based on the cell number. Now, if a new number has been dialed, with a new number I mean a number which is not on the clients table, there is nothing displayed in the related field, it is empty. If I execute a search to find the empty fields, it would find nothing because in fact on the clients table there does not exist empty fields. How can I perform such find? How can I perfom a search to find numbers which are not on th…

OK, here's my issue: I have a FM8.5 database with over 300k records. Each record is assigned to a subject area using a "subject Area" field. The subject area field uses a drop-down list (populated from another table) of roughly 126 subject areas. I need to figure a way to generate a report that will take the entire database as the found set, then report: 1. The total number of subject areas. 2. The total number of records assigned to each individual subject area. I tried using the count function, but can't seem to get it to work correctly. If anyone has any pointers or knows of an example that may be relevant, I'm all ears. Thanks! -Dav…

Hello. How do I do the equivalent of SELECT UNIQUE in Find mode? Searching help for "Find Unique" returns help on finding duplicate values, which returns every one of my records because each data value is in at least 2 records. Thanks.

Hello, On my "Search results" layout, I would like to be able to display the search string in the following way: Displaying # of # results for "search string" I show 25 results per page. At the moment I don't know how to put the "# of #" part in nor know how to put the search string on my layout. Any ideas? Thanks!

Seems so basic, but .. Can someone direct me to where I can find a Find and Delete Duplicates formula? I need: If Field A plus Field B in rcd 1 equals A plus B in rcd 2, delete all but most recent by Date Field C. TIA!

I am using the Email Campaign Management template with FileMaker Pro 9. It allows you to create groups, but currently you have to scroll through a list of names. Is there any way to use a found set and make that the group? Or, will I have to scroll through 2000+ names and search for each member that way?

Solution: Is it possible to copy found sets across different windows? Description: I have 1 layout and 2 separate windows. Both windows refer to the same layout. Is it possible to perform a find in one of the windows and copy the found set to the other window? Is the found set of 1 table shared across layouts that refer to the same table but are in different windows? The reason I am asking this is because I am currently developing a solution that has a "find Pop-up" window that will perform a find and go back to the previous window and display the results. Thank you for your time, Carlos C. Working Under: Solution Status: Beta Pre-…

Hey all- I am in FileMaker 6 on a mac, and my organization's database is a total mess that I would like to clean up. I am wondering whether there is a way to find all fields that are not on a singly layout? Also, relatedly, I am wondering if there is a way to find records that have data in less than a certain # (say 7) fields? I would appreciate any suggestions. Thanks a lot, Jesse

Hi. Im relatiely new to FMP and am havig difficulty searching on a date range. I have three fields. _DateFrom, _DateTo, and Date. I am trying to locate all records in the Date field between and including _DateFrom and _DateTo. I understand the mat hbehind it, but cannot seem to get it working. I thought perhaps that in a Find I could use the two fields yet it only allows me to insert literal text. I have tried Omitting as well - same problem. Anybody please give me a pointer in the right direction? Cheers, Greg

Hello. How do I do a simple query against a .fp7 file on my desktop? By this I mean, - Where do I type the SQL statement? - How do I execute it? Very basic questions from a complete novice who finds FM very, very frustrating and the help files useless. Searching for "Query", "SQL", or "Create Query" returns irrelevant (at least to my newbie eyes) results about external data sources. 10 years ago I was an accountant. I then taught myself MS Access and got good enough at it that I've been an IS&T professional since 2000. The point I'm making is that I'm not new to database software. I just can't get to square one with FM other than opening f…

Hey, I've built a search script with stored criteria, I just don't know how to suppress the error message when there are no valid results. I know how to display a custom dialog (via: If (get record count) = "0", then display dialog/exit script etc), But that only pops up AFTER I get the annoying alert with the three options that can override my script! Any ideas? Thanks for the help.

How do I change the number of records that are displayed per page in a found set? I'm finding that the number of records shown is different between the web interface and the actual Filemaker program interface.

Hey guys, Okay, so lets say I've got the following scenario. I want to find all matching records where a persons date of birth, gives them a certain age by a certain date.. For example: Date of birth: 3/17/89 Table... [color:red]Age|[color:blue]Based on Date [color:red]18|[color:blue]3/16/07 [color:red]17|[color:blue]3/16/07 [color:red]18|[color:blue]3/19/07 In the above example, records 2 and 3 would idealy be returned... Cheers guys.

Hi, I'm fairly new to FMP and I'm trying to figure out the Count function. I have a field named status that contains 5 options from a value list. What I need to know is a Count of how many times status=Available. Can you help me with the syntax? The function library lists it as: Count ( field {; field...} ) This information will be stored in a calculation field named Prints Available. Thank you! Tatiana

Hi! How will i search more than one values in same field in particular table. For Example: Consider the table Student This table having three fields namely Sno, Student Name and City like below SNo Student Name City ======================== 1 AAA XX 2 BBB XX 3 CCC YY 4 DDD ZZ Now how will i fetch records like city=XX and YY I tried this concepts via Relationship way mentioned below. I created a another TABLE namely z.Globals having a calculation field namely zct.Text Calculation : "XX ¶ YY" And link zct.Text(z.globals) & City (Student) Finally i g…

Hi, I use the technique of creating a global field that I use for a pop up window search box. The user enters info and the global field passes onto the search field (via set field), and finishes by clearing out the global to make it ready for the next use. It works great... but how would I use this same technique in a multi user environment? If user 1 is using the global field and user 2 tries to search... as soon as the search box opens, won't it already be populated by user 1's information? How would I get around this? THanks. V.

Hi, is there an omit search symbol or something that can be used in that sense as my left menu is hidden in the application so the users cannot select omit from it? Not a scripted search but a regular one? In detail: I have a find that displays a dialog box which allows the user to search the db via item code only, there is also an Advanced button which they can press and will be taken to a regular find mode and after they enter their criteria they can hit enter to complete the search or they can add more search requests. The problem is that as the left menu is hidden so that they cannot select omit. Is there a symbol that they could type in before the criteria which…

I'm working in FM 8.5 on Windows XP. I have a Contact table with contactID, first name, last name, title, company, etc. I need to track multiple people from the same company and I want the address of a company to auto enter if it is already in the database. If I create a Company table and activate "create records" in the relationship, it is creating a separate company record every time I enter a contact even if the company is already in the Company table. Any suggestions?

It seems like there would be a simple solution to this, but I haven't found it yet. I'm setting up a report that needs to have the PREVIOUS month's name in the header. What calculation, based on the system date, will accomplish this? I've tried the following: [color:red]MonthName(Get(CurrentDate)) (the result is "August") [color:red]MonthName(Month(Get(CurrentDate))-1) (the result is "January") Any ideas? Thanks, Rodney

Hello, I'm trying to determine which method is the optimum method of performing a find of client information. The setup is as follows: 2 databases (heavily based on the FM 8.0 integrated solution). 1 is for client info; the other is a booking database containing info on a travel booking (flights etc). The workflow is that the client first checks to see if the client is existing or new... the question is which of the following methods is best to perform this function: 1) from the booking DB, run a script that switches to the client DB and performs the find there and then copies the clients data (address etc.) back to the booking DB (assuming the client exists)- *I'v…

I was wondering how to script the following: I would like a script that can display the last 5 records added to the database. This script would begin in Find mode and would work as a single button. The attempt is to display the latest arrivals. (Since FM defaults that the last records are the most recently added I will assume these are the latest products/records added.) Thanks!

This should be a simple question, but I cannot figure out how to do it. I have a table with close to 20,000 records. I have a field called "ItemNumber". I want to do a search that matches the field contents exactly. For example: I have item numbers ABC123 XYZ345 I want to do a search where if I type "ABC", since there is no exact match for item "ABC" I would like it to return 0 records. In other words I want to match the full contents of the field, not a partial match. My search script goes into Find Mode, sets the ItemNuber field to the global search field and does the perform find. In addition, if it returns 0 records, can I script to supre…

Hi. Does anybody know how to have a field or function setup so that I may type the first few letters of a word and not only does it do the AutoEnter feature, but actually limits the list (record/entry count) to data that meet that criteria? Furthermore, have it so it's not just the leading letters but any position of those letters of an entry in that list. For example. I have a list with 50 names in it, starting with various letters. I start to type "SMI" - it jumps to names like SMITH. I'd like it to only show items in the list that have "SMI" in it anywhere - suh as GOLDSMITH as well. Is this possible? I know it's tricky. My way of thinking is that it tak…

Hi, I have a database in filemaker 8.5 which contains customer record. Email is a text field and contains customer email address but when I try to search on email field (from filemaker ->going in to find mode), it doesnt bring any result, however there are records matching the typed email address. If i remove the @ sign from email and search then it brings the matching result. Can any one please tell me the reason for this and how i can fix this. thanks

The simplified scenario: a layout with fields: item, brand, price, unitprice. I would like to write a script whose purpose is to find other records that match the currentitem and then to display only the item, store and unit price on another layout. I want this find to be executed with a button on my original table, so I want the script to know what the active field contents for item are. I am new to filemaker and am going nuts trying to do this very simple thing. How do I pass the active field contents for item into the find function within a script and return the find set???

working in Filemaker 8.0v2 I'm looking get a found set which consists of all records that have matching content in 2 fields within that record, i.e. [color:gray] Include in found set where (content of field_A) = (content of field_ This is for a manual find, no need to script it. This seemed to me to be something Filemaker would have a obvious function for (or symbol to use in a find), but searching through help and doing trial-and-error with various symbols didn't yield any solutions : . Am I just missing the obvious, or is this trickier than it sounds?

I have a table called Auctions that has both an ItemNumber and a ScanID in it. ScanID's are unique to ItemNumber's, so if I have 10 auction records with the ScanID 12345 they should all have the same ItemNumber. Somehow I've gotten lots of auction records that are getting incorrect ScanID's. I need to figure out how, but right now I simply need a way to find all of the problem records. I can put a ! in the ScanID in Auctions to get a found set of records with duplicate ScanID's, however, I don't know what to do from there in order to filter so that I only see records with duplicate ScanID's but different ItemNumber's. Any information would be greatly …

hello again after a long absence trying to hone my Filemaker skills now that my company has F8 - but I guess I'm still a newbie so excuse if I ask a question that has been answered before (although I've just spent a half hour skimming through topics hoping to find a 'simple' answer that might help my problem - but no luck ) so can I ask if anyone knows how to help with the following: I have a database with 2 fields I need to bring up in a report - I need to run the report every quarter which looks for the date1 field completed but date2 field blank -I think I can work this bit out myself as follows If(GetAsNumber(date1) ≠ "" ; If(GetAsNumber(date2)="" ; "alert…

Hi all I want to do a search on a found set. I have attached a file to explain what I am tring to do. Thanks for all the help in the past. Lionel double_find.zip

I'm trying to search for all records between (inclusive) two time fields. Two other fields are included in the search; date and userId. I'm finding that the date and userId filter out other records correctly, but the time range filter doesn't. Filter is: Set Field [EventRecords::Event_Time; "Events::gEventStartTime...Events::gEventEndTime"] Any ideas? Event_Time field is indexed.

I am new to FM and am wanting to produce a report that is sorted by a range between two dates. I have tried to no avail to have maybe a custom dialog box that would gather the dates and them import them into the search field (purchase date) and then search. I know that I can manualy type the dates into the field seperated by "..." but I would rather it be a little more automated. Any ideas?

I have a 'date received' field (defined correctly as a date field) that I use to identify whether a client has settled a bill or not. Using the '*' should show all records where this field has a date (ie the client has paid) and using '=' should find fields that are empty (i.e the client hasn't paid). Unfortunately the finds are not working. Any suggestions as to what could be going on? All other fields in the table and file (be they number or text fields) work fine with find commands. Many thanks in advance.

my solution is shared through a fm server and there are 10 people working on it. for acquisition purposes some of the users need to put together an appropriate set, searching and narrowing the search, etc. After they found the set they want to work with, they will work on it e.g. during two weeks and after that they will start with another set. Is there a way to store temporarily a found set? Of course I can't add a field everytime someone starts a new task. What I thought of as a simple and more or less useful solution: simply put together the set, then as a first thing, one adds a specific expression in the log with (replace field content), so afterwards one o…