Finding & Searching

Hello, I am trying to find a way to grab the information from two fields and calculate only those begining and ending fields from a summary report. Example: The report is based on the following fields; Date field, Unit#, Kilometres start and kilometres finish. My search criteria is based on january 01 to December 31 for each of the unit#'s (there are 250 units)and sorted by Unit#. I want only the Kilometres start at the begining of the report and only the Kilometres ending, last record in the report for the specific unit#. Now I need to calculate it so it shows me the total mileage for the Unit# used but I don't want all the mileage calculated for the remaining of th…

Hi people, I can't figure out how to solve this issue: I have 2 fields: LastName and FirstName... what I want to do is that when I finish writing the name, FM warns me if there is another person with that full name. I mean, how I set the validation settings?? Example: I have 3 clients (John Peterson, Michael Samuelson, Sean Targus)... If I create another record with the LastName "Peterson" and the FirstName "John", I want to FM tell me that there is also another guy called John Peterson. THANKS FOR YOUR HELP PEOPLE! YOU'RE THE BEST!

It appears that if the "Left" side of one field pair is global, a portal Find will *not* filter on that pair (even though it does display properly). So, you Find too many matches this seems to make sense, if the Global does not exist in Find Mode. It just seems that FileMaker should warn the user of this Greg

I'm creating a database for deliveries. There are two tables: Schedule and Address. I currently have a portal setup where I can type in a new address via the portal but I would like to change this so the portal is for viewing purposes only and when needing to add a new address there will be a button with a script (add new address) in the script I have the following: Go to Related Record (opens a different layout) New Record Set Field... Here's my problem, I wish to set the id number of this new record to match the current schedule and I can't figure it out!!

Hello! I have multiple records with a aTriage field that contains True/False In another table I want to have a drop down list that only shows the records where the aTriage is True. any ideas? thanks!

I was able to hard code a script "find" in FMP 5. But in FMP 8.5 I am having trouble. I cant seem to create 12 scripts associated with buttons to search for 12 different time periods on the same field. Hope someone has a little help on this question Thanks

Hello, I was wondering if FMP10 as an option/feature that will let you input a barcode?

I work with state government. My business is required to have daily time sheets broken down in 15 minute intervals throughout the day. My staff also has to indicate whether they were performing administrative tasks or direct care tasks during that time period. I have to report how much time was spent performing each type of task. My time sheet is one record. Each field allows for 40 repititions. I can't figure out how to total each type of task for each day and ultimately for the year. Can someone offer some suggestions?

I'm having this need come up more and more...I have a user that finds a set of records, and then I need to find ANOTHER set of records that omits content from a field of the original found set. For example... Fields: Name Address City State School Hobbies I do a find for everyone that has a CITY of Columbus...I get 45 records back. Now, I need to query the DB again - new find, and omit the school name from each of the 45 records...so FIND R1->Hobbies=Music, FIND R2->OMIT School from found set 1, record 1, FIND R3->OMIT School from found set 1, record 2, etc. I can't wrap my mind on how to script this out - and surely don't…

Hi I'm working on a FM9 DB (Mac and PC) that generates a number of different reports using multiple sub-summaries. My client wants to be able to turn parts of the report off (disabling sub-summaries from the sort) without using the built in FM sort dialogue (ie they want a layout with checkboxes for enabling / disabling different parts of the report). Is this possible? Secondly, they want empty sub-summaries to be removed from the report automatically (ie if a report runs and a sub-summary part is empty, then it should be left off all together, so excluded from the sort). Any help would be appreciated )

I have a database of plants where users can search on a large number of fields. I want to be able to "save searches" so it works like "smart playlists" in itunes. Users can then go back to their custom lists of plants. I am trying to determine the best way to do this. Would I duplicate my Plants table, call it UserSearches, and then 1. enter each new set of search data in browse mode (or select a previous Usersearches record) 2. run a script that --places each UserSearches field value into variables --goes to a Plants layout --enters Find mode --enters the variables into each of the Plants fields --conducts Search Or would there be a better …

Hi all! I've got a nutritional supplements db. With some advice from bcooney (thanks!) I've got it set up with separate tables for Supplements, Areas (i.e., health areas that supplement might support), Doses, etc., plus join tables with appropriate entries and a relationships graph to tie the tables together. So far, so good... mostly. However, I've run into a problem. In the Supplements table (titled "Supp") I've got a field that indicates whether or not that supplement is currently part of the regimen. The field is named Current, and if the supplement is currently being taken, I enter a "y" in that field for yes, otherwise the field remains empty. Then if I w…

Hello Community at large, I have a problem that has frustrated me for days now. Chief Complaint: I can not figure out how to sort by day of the month only (within dates covering only one month) Details: We have a Demographics type data base and I need to be able to make lists of everyone with dates in the upcoming month (there are actually four date fields I have to search through). I currently run a search like 2/{1..31}/* to find all dates that fall within the month, and then view them all in a layout I created for this purpose. But the results need to then be organized by the day of the month. The problem I have run into so far is that FMP seems to …

Hi, I have a script to find records in a database. The layout for the find contains several fields. I would like the results to display differently based on which field was entered in the find. For instance if I search by PO number I would want certain script steps, if I search by a product description it would want different script steps. I have already set it up so that based on how many records it returns it can go to a different layout, I am now trying to figure out script steps that would capture what field was entered. Would I use several instances of IsEmpty to eliminate what fields were not entered? Dan

In a script, I'm setting a global date field with a date value entered in the Show Dialog script step. I then want continue the script to find all records with a date that is equal to or greater than the global date. I first thought I could simply use the enter find mode script step, set the date field to ≥ global date, and then perform find. But, FileMaker does not allow the ≥ character. I can get around this using copy and paste but that is sloppy. What is the best way to script this?

In my contacts table, I have an "email" field. If the email bounces, the address gets shifted to a "bad email" field. If the recipient opts out of communication, the email address gets moved to an "opt out" field. When I acquire new contacts, I would like to make sure that the new email is not in either the "bad email" or "opt out" field. How can I find duplicates in the multiple fields?

Noob question: I'm using filemaker 6. I have a database containing names and adresses and telephone nos. When selecting seach mode the cursor goes into the adress box, how would i make it go to the telephone box by default? thanks

I work at a non-profit which keeps track of volunteers/vol. hrs. I'm creating a layout which shows the total Volunteers during a given time period and the total hours they worked. I am able to get the total number of everyones hours combined. I use a summary, no problems. I don't get a correct number of actual volunteers, as some volunteers are counted multiple times. Any ideas? Thanks!

Hi all I need to run a find script that only finds unique records in the database. The only way I can see to do this is in reverse - find all duplicates using "!" then omit found set. Is there an easier way? Thanks Ed

I have done a lot of reading and searching this forum, but have not found my answer. I simply need to perform a find across 5 fields in my table. The fields are, Contact1 Contact2 Contact3 Contact4 Contact5 I need to be able to find "John Doe" in any of the fields. Is this possible to design without scripting? The only answers I can find seem to require scripting and I am a newbie and have no experience with this. If I can get some help with the script I need, that would be great. I would like to place a button on my layout that says "find Contacts", and then let the script search the 5 fields. Thanks

I am an MS/SQL developer and I was requested by my college to take a look at a FM database. I have not done anything with FM and I am trying to learn it as I go. The problem that I am having is that My primary key in one of my table sorts the data ascending then it starts to go out of order here is an example: 08GW108 08GW109 08GW11 08GW111 08GW112 08GW113 08GW115 08GW116 08GW118 08GW119 08GW12 08GW124 08GW13 08GW136 08GW137 08GW138 08GW15 08GW17 Notice that it goes in order before this point then it gets jumbled up. How can I keep it in numeric order even though data type is (text). My second question, does anyone know a good book …

Hi, Back again with another question...hopefully this the right forum for this query. I have 1 DB with 2 tables named bookings & clients. When I add a new client entry in the bookings table (i.e. a client books a tour), there typically is a traveling companion(s). When completing the booking in the bookings table, I can add the traveling companions names that in turn via a portal will add the traveling companion(s) name(s) to the clients table. The question is how to check for/prevent a duplicate travel companion name(s) entry into the clients table? The 2 tables are related via clients ID. Any quick thoughts to point me in the right direction on how to proceed? …

Hi all, I have 2 date fields, StartDate and EndDate. I would like to use these fields to use as a find for invoices by typing the start date and typing the end date and when the user tabs out of the end date a script would return invoices for only invoices between those dates. I already have all invoices showing in a list view and would just like to shorten it. Is there also a way to show only the current months invoices when the user first goes to this layout as the default and then let them chnge the dates as above? Thanks in advance Milton.

Hello If anyone can help me it would be greatly appreciated. Basically I am trying to redo our companies database(modelmaking company)and I have got a problem. I have 2 tables, a client table and a job sheet table and what I need to be able to do is lookup the company name from the client table then input all the company info, address, telephone numbers etc, into related fields in the Job sheet table. The old database had a pop up menu on job sheet but I don't like it because the list is so long, it takes ages to scroll through. Another problem is what to do when you have more than one person working for the same company. I wanted to have a separate record for e…

I am working with records documenting antique objects. The documenters have given their estimates of the earliest and latest decades in which the objects were made. i.e., they may have entered 1840 in one field and 1860 in the other. In some cases, they have only entered dates in one field, evidently believing that to be the right decade. I would like to search for all records >= earliest decade and <= latest decade. Is there a way for a novice to do this?

For finding records starting with "213" in a field number with 6 digits in FM one would use "213###" and it would return ALL invoices starting with 213. Now that we have an ODBC connection to our MySQL db the wildcard search returns an error: "This field is defined to contain numeric values only. You must enter a numeric value". My question is: Which wildcard character should I use to replace the "#" when reading MySQL db through an ODBC connection? I unsuccessfully tried "%" and "_".

Here is the preliminary real-life description--the specific logic question is at the bottom: We get "submissions" of soil samples and we perform a variety of tests on them and write results on printouts that are generated when the samples are brought in and i run a "print tests" script. In all but one case, each test has one layout that is printed and taken into the lab for writing results onto. That layout is repeated on the printout for each sample for which the test was ordered. So, the printout will have rows to enter data for many samples all on one page. All of that works perfectly in our current db except for the "but one case" above. In that one case, the "…

Hello, I'm developing a solution to organize my office, but I'm having a little problem with one script, that I believe is very simple to accomplish. I have two tables Projects and Jobs that are related with each other. I can create many jobs that are related to a specific project. Project and jobs have a status field, that can be Open or Closed. My script is to set the status closed to the Project, but for that I must be sure that aren't any open jobs related. The script works OK when there's an open job there, it displays a Custom message saying that are some jobs open and they must be closed before I close the project. The problem is when all jobs are cl…

Hi I'm sure this is very simple to achieve but i cant find a solution. I am learning FMP with a view to dumping MS Access so i am trying to recreate an Acces DB in FMP. What I want to do is filter a record set on the contents of a field then use that record set to populate a dropdown list. I have a table containing account details which includes the account type eg Debit Credit Cash Bank Equity etc. I have a table which holds transaction details and a layout for the entering of those details. On the layout are 2 dropdown lists one for where the payment was made to (credit) and a dropdown list for where the payment came from (debit). How do I …

I have a database where searching a text field for *N gives the same results as a search for *N*. I want to select records with with N as the last letter in that field but I am returned records with N anywhere. I believe that this might have something to do with indexing since if I set the indexing language to Unicode the problem does not occur. Are there any problems with doing this to the indexing or is there another way to solve the problem. Thanks.

Hi, In the hope that there are no stupid questions... How can I determine the repetition number of the field which matched criteria entered in Find mode? Since Find returns records which have a field whose contents match the criteria, and not the field itself... and if the matching field is a repeating field, and the matched criteria is only contained in a specific repetition of that field. Confusing! Some context for you: I'm building a "work journal" solution, wherein the records simply store the username, date, and a repeating field of tasks accomplished by the user on that particular date. I would like to add a search box to the UI. When doing a fi…

I have two filemaker databases (main and products) with a related field. Is there a way to search the related field and return only what was searched for? When I do, lets say when I search "appliance" in the related database, it returns "appliance" but other products in that field as well.

Hi, When in find mode, on the toolbar at the top there is an "Add New Request" option...is ther a script step that does the same thing, as I can't find one. Thanks, Jim

At the moment I'm a little stupmed as to go to a specific record. I have RecordID field with auto serial number. Whit the scripts that I've made so far they do find the record. But searching "1" brings up any record with a 1 in it. Is there something I'm missing? Maybe perform find isn't the way to go. I'm sure this issue has been coverd before, but my search's have been fruitless. Regards, Mike.

For the sake of a simplified user interface I'm offering users a global field to insert their search string and then applying that search among three fields. Works nicely. Finds all "Robert" entries in both first and last name fields and any records where "Robert" is included in the account name. I've taken this further. Find all "Robert" entries which are also "active", a boolean field. This also works. However I'm stumped on finding all "Robert" entries which are inactive, that is where the boolean field is empty. I'm unclear what the scripting strategy should be in this case. Assistance appreciated.

Problem solved.

Hi, I have two tables: Table 1 “Unknown” Table 2 “Known” These tables are not joined, due to the fact that their data is not really related. However they both have a “Date Entered” and “Entered By” fields. Is there a way to join these two tables so that I can do a single search for a date range and entered by without having to do it for each table, as I do now. Thanks, Jim

Hi. If I use ! in the Find Mode, will this find all of the duplicate records in my table? If so, can I delete the "found" duplicate records in order to have a clean, no duplicate database? Thanks

I want to perform a find script based on a date field. The date range would be greather than or equal to today's date and less than or equral to today's date plus 30 days. I have been using the "specify" function in the perform constrain found set script, but do not know the proper syntax. All I can do is find records greater than or equal to today using >=//. Can anyone help out with the other part of the equation, the less than or equal to today plus 30 days part? thanks in advance dta

I have been handed an Excel file with 13000 lines of data. About two thirds of the lines are duplicated. I have converted the file to FMP, and need to delete the duplicates. What I would like to have is a simple (?) script that would read the records one at a time, go to the next record and test to see if a key field (a client ID number) is the same, and delete the record then go on testing and deleting through the whole file. Can anyone help me here? TIA

Hi I run a script on a button to perform a find This finds the records based on the users criteria then takes them to a new layout to display the results. On this layout i would simply like to add a line of text that says "Displaying X of X records found" just as it does in IWP. Is there an easy way of doing this or am I looking at using calcualted fields to do this. I know there are certain symbols you can use on a layout to display username, version, last modified etc I was wondering if the same is true for current record of current found records? I dont know if this makes a difference but it is a hosted database on FMSA 9 with clients perfom…

I'm sure I am missing something really simple here so apologies for the level of this question. I have a form which details transactions. I'd like to select all transactions for a particular year. If I go into find mode, it's easy (I enter the year I want and up pop all the related records. However, I want to do it with a button so the user picks the year from a drop-down and then on the button press it finds all related records to that button press. My difficulty is I cannot see how to pass the variable from the year selection to the find script. I can run a find script that pulls back the records where year = 2008. What I cant work out how to do is run a scr…

To ascertain 8 different numerical results for a report is there an easier way than I'm currently using. The exercise is to count the number of records after each 'find' has been completed. For example I need to find the number of records that fall between a specified date period (easy of course), so an initial find is completed for records within that specified period, say 117 are found. Within those records there are 8 different criteria, some within the same fields and others in different fields. First I need to find the number of records that may have a 'P' in a field, then the number of records that may have a 'F' in that same field. Next, I need to find the num…

Does anyone know if there is a way to correct the search field similar to how Google will show your results, but also provide a "did you mean..." if you have an incorrect spelling? Say for instance I search for "Gren", is there a way that I can have a script in my FileMaker search that would ask the user if they meant "Green"? I thought I saw this somewhere, but now I can't remember where. Another way to go about this...is there a way to have a FileMaker search "Powered by Google" like this forum itself? Thanks for any help.

I have a form letter going out to instructors. In the letter each instructor is given the names of students working for him/her each semester, the courses each student is working, and the hours they are allowed to work, given their pay-grade. The form letter is sorted by instructors and semester. In the body of the letter there is a spot giving student info. I tried doing this letter using sub-summary by "instructor", but that doesn't seem to work correctly. Please help tks Amy

Hi all, Each client/prospect (about 12,000 records) in our database is assigned to a salesperson. Users in the sales group should only be able to see their own clients but other groups are not limited. I tried achieving this by editing the sales privilege set to be able to view records where the AccountName of the user logged in matches the salesrep username assigned to the client. This does work but without doing a find, the total # of records returned is the full 12000 (not the 150 average you would expect for a sales person) and all of them are browseable but most of them just display "No Access" for all the fields. Isn't there a way to just eliminate …

Hello, I have a parent table (students) with a portal that displays records from the related table (courses). There is a portal on the Students layout that is based on two criteria: 1) (the very common) foreign key to primary key AND 2) a global field in the Students table that allows the user to see only filter to display only records that are type "A" or type "B". This works just fine and the filtering works as expected in the portal. However, when the user sets that global field to set the relationship to show only one type of record (e.g. type "B") and then does a search it returns a found set that includes Students with only type "A" records, and…

I have an enrollment database for classes and I need to inform people if they are in the class or on the waiting list for the class. As classes vary in size each class has a maximum number field and I have set up a summary field in the student/class join table to count the number of enrollments when sorted by class name. A third field then calculates if the summary is greater than the maximum number field. If not it gives "Enrolled" and if so it gives "Wait List". The problem arises when I want to find the records of students who are on the "Wait List". As summary fields are unstored I cannot find them. Is there a work around to make the data stored.

HI, I am at a complete loss about this. Here's what I'd like to do: My simple records: record 1|xxxx record 2|xxxx record 3|xxxx record 4|yyyy record 5|yyyy I would like to look for occurences in the 2nd field. eg xxxx and where this occurs I want to have the same serial number for that found set and then on the second set yyyy increment by and repeat all the way down, incrementing by 1 for each found set so it ends up like: record 1|0001 record 2|0001 record 3|0001 record 4|0002 record 5|0002 etc etc. It doesn't even have to be in the same field. The result could be placed in another field. I'm stumped …

I have developed a productive scripted routine to generate finds. However, sometimes no records are found which meet the current criteria. If the user started with a subset of records I'd like to return the user to that former set, rather than dump them back into all records. Is there an efficient way of storing the current set of records at the beginning of the routine so that this set could be restored if necessary?

Hi all, I've been trying to figure out if this is possible: I've got a list of tasks assigned to people and have a search button to look for outstanding tasks for a particular person. Completed tasks have a timestamp field filled in with the date/time of completion. The find script pops up a box asking for a name, finds all records with that name and then constrains the search to omit records with data in the timestamp field. Is there a way of "unconstraining" this search to show the full list including completed tasks? I'd like to have a "show all" button on the search results page. Regards, Taig McNab

Okay, this may get a little confusing, so I'll try to be as descriptive as possible. The company I work for is changing our warehouse's storage method. Instead of simply labeling every available slot with the Item number of the item it holds, we are instead giving every slot a unique number (based on its location in the warehouse). Basically, we are slotting our warehouses. Along with the textual number, we are also giving each slot a barcode. This code holds only the number of the slot. It's exactly the same as the textual slot number, except it's in bar code form. This is simply so the bar code can be scanned instead of typed in or written. We are u…

I am just getting my wings in FMP. This question might be painfully obvious to some of you but is causing me to scratch my noggin. Basically, I have a database with student numbers and scores in 8 different subjects. The database has in excess of 8000 students. I want to search for a class (say math) of 30 students and get their score in math. So at the end I have a list with 30 students numbers and their scores. I can set this up for one student and it works fine. But I don't want to search manually for 30 students. How to do this for a group of students? (most likely the list will be coming from txt or xls file). Help!

Hello, In a script I'm using, the data is constrained using the Constrain Found Set feature. On OSX this works fine, and if there are no results found no records are displayed. However, in Windows, if no results are found the records are displayed before the found set is constrained, which means the results are wrong. Is there any way around this? Thanks. :)

Hello folks, Is it possible for user that have a specific privilege (owner) showing them only (and when I say only means only) a subset of the records I have in a table? Basically only the records that have the user's username as a "managed by" field. Everything must be web-compatible. Thanks for your help and have a nice day!

Hey, I have two related tables that both can be searched. In the results table I'd like to show the found records of both tables in one single list. As far as I know, I need to have a third table for this; a table that gathers the results from table A and table B. However, I am stuck on that and am looking for a hint where to start. Any idea? Thanks, Mike

Is there something I'm missing in FM9 limiting the return of a Find to ONLY three records? No matter what I'm searching I am only getting three records from a table (containing many more matches). This layout (used now in FM9 has been modified from a FM8.5) and I'm using portals to read a MySQL db.

Let's say I have a table with two fields - Name and Number. The Name field is is a combination of first names and last names. The Number field is just a phone number. Here's what I want to do - In one layout I want to have a search field where I'd enter part of the name, either first or last name, then press a button and either it will go the only matching record or populate a portal with the possible matches. I believe I should be able to do this with a self join but I can't figure it out. I looked at http://www.kevinfrank.com/demo-files.html (his contact finder) but I can't quite understand how it works. Any advice would be greatly appreciated. …

I have a script the finds records within a user-specified pair of dates. Now I want to Extend Found Set to include any records for which the date field is blank -- but the Edit Find Request won't accept "" for the date field. It says it must be a valid date. What is the proper way to designate a blank date field in a Find or Extend script step? Thanks in advance for any assistance.

OK, this should be very simple and I am embarrassed that it is giving me such a problem. I am setting up a find function to find customers with invoices 30, 60, 90 and 120+ days past due. I have a form letter set up for each age range which automatically fills in all relevant data (which is working great. The issue I have is in getting the find function to narrow down the invoices in the ranges that I specified above so I can send either a 30, 60, 90 or 120 days collection letter. I have a field set up (Oldest Invoice) to display the oldest invoice for each customer ........Max ( Invoice Age ) I then set up buttons to perform a find in the Oldest Invoice field. …

I would like to find records based on the Account Name used to log in to the database. Is this possible? More specifically, I have a Find script set up and working that finds records associated with a particular program in my organization, but need to add a parameter to that find script that constrains the found set based on the Account Name. I would be very grateful for any help this forum could offer. Thank you very much! Eric

Hi, Sorry for posting this...but I just cant find an answer via searching topics. I thought I had done this in the past, either I had not or I forgot how (I havent used FM in quiet some time). Anyway, all I am attempting to do is find all records older than a year and then delete them. I have a Date Entered field to show the date the record was created and that is what I need to do the find on. Thanks, Jim

Hello, I have a database with a text field non-unique. I would have a script that, when entering something in that field, test if there is already another record with that field value. If YES, go to the most recent record; if NOT, create a new record with the field filled with the value not found. I have try to write a script with the Find mode and the Get(FoundCount) but I am unable to succeed.¿Can anybody help me? Thank you

Hi guys Ive just moved from Access to FM currently experencing a bit of a learning curve but on the whole Im finding it a much better program. Anyway my quandary, I want to be able to search one table twice with different search criteria to return diffrent results in the same layout. For example I want to search for the entry number 400 and 230 at the same time so both results are visable on the screen at the same time. Any one have any ideas how to do this ?

Greetings all! I'm a bit of a noob here, and I'm trying to work on a project for a customer. I have a table that has a couple entries ID Date Lookup Cost Process Type My process type is stuff like cut, drill, sand, a whole bunch of stuff. Actually its a foreign key to another table that has the descriptions, but i can't see that matters. My customer wants an easy way to sum up all their process types by a date range. I made a report up that has sub-summaries based on the process type. I added a field to my table that is a summary field called 'total'. It sums up the total of cost and restarts on the basis of each sorted group. I put it i…

Hey there, I'm pretty new to FileMaker, but I have a fair understanding of relational databases. I am in the midst of try to restructure a database that has all of it's data in one table... over 450 fields. I didn't make it. The database is about 5 years old, and it's for a weekly children's program. They keep track of the kids attendance in fields attached to the main table. In a testing file, I've rebuilt the database so that the attendance information is in a separate table. The new table consists of a date, the child's unique id, and an "attendance" boolean. This information is then also displayed in a portal so that it is easy to add an additional…

I have a database with two main tables - individuals and companies. We just discovered that every time we delete an individual record, FMP deletes ALL records with the first and last name (even though each record has an individual serial #) - So if I create three records with the first and last name fields as 'Jane' 'Doe' and delete one record (either through a button or through Ctrl E), all Jane Does disappear - even though they have unique Contact ID's and are associated with different companies. I don't have this problem in the Company table - I'm a relatively advanced FMP user, and I can't for the life of me figure out what's happening, here. …

It's amazing how many Google results I got which were the exact opposite of what I'm trying to do here. I built a script which, depending on user input, generates a found set which may be as few as a dozen or as many as a couple thousand records. There is a "name" field which is populated on every record. What I need is to omit every record from the found set which does NOT have a duplicate. In other words, if the pre-processed found set returns (in the "name" field) John Mary Peter Peter Beth I want to omit John, Mary and Beth but keep the two Peter records. I tried this with the Constrain Found Set script step (using "!" as the criteria)…

This has got to be simple, but I'm new to FileMaker (and database solutions in general). I've been reading this forum for about a year while building my previous databases, but this time I'm stuck. Searching the forum has yielded no answer this time, so I registered and am now posting for the first time. I have a table (x_numbers) with a primary key. It's related to another table (rfq_routing) using the same value as a foreign key. So, one record in x_numbers has many child records in rfq_routing. The x_numbers are requests for quote. As the pass from department to department in the company, a new record is added in rfq_routing, which displays as a portal…

I would like to know if is there a way to stop the roller of the mouse from moving from record to record.

I need to have a field that pulls a weekly sales total for a salesman. I have a field that has a total billed amount for each customer but need the total amount for the week for any given salesman that i do a find for. Whats the easiest way to do so? and also the same concept for the year.

Hello. Does anyone know if it is possible to use the constrain found set but to specify criteria as a variable? pseudo code example ... action omit records | criteria mytable::myfield: [$$variable] What I suspect is happening is the literal "$$variable" is being entered into myfield rather than the contents of $$variable. I'd prefer to get this working rather than using enter find mode, set fields and omit records ... I'll spare you the details. So, can you use a variable to constrain a found set? If so, how?? Cheers Tim

Hello, which web-compatible way is the best one to handle the zero returned records from a finding ? Thanks and have a nice day !

I am wanting to know if theres a way to do this. I'm new to filemaker and still learning a lot. I have a database that has over 8000 customers. Say if one day i do a found set of records for whatever reason. A customer calls in and i need to access the same database to check out the record for that customer but if i try to find that record, i just lost all the work i did for that found set. So now once im finished with that customer, i have to go through all that work i just did to pull all the records in a found set again that i did earlier which creates double the work. Is there a script i can write that will save the found set for later and all i need to do is run it a…

Context: 1000+ records, Each record contains a unique # in field1 I have a random 70 +/- records at any given time that I need to quickly pull out and then edit or delete. How can I find them without going through a list 1 at a time??

I am set a variable to Get(ReecordID), Later I want to return the that record. The recordID could be 28700 in a set of records that only has 1000 in show all mode. I can't use Go To Record (By Calculation) Get(ReecordID) or By the variable. The number is too high. Anyone know how to navigate to a record by RecordID?

I have a VERY basic database with tables for my music students' info and the school-owned instruments. In the Student table, there is a checkbox set from a value list which displays the different ensembles that are offered. When I perform a find by checking "Band A", I get those 42 kids. When I find by checking "Jazz Band", I get those 29. But, when I find by checking "Band B", I get all records. I should only get 49 of 92. I've already verified my value list to be sure it's error-free. I've unchecked and rechecked a few kids into Band B (and the inverse for some of the Band A kids). There are a couple who do both, so radio buttons are out. An…

Hi, Finding all related records within a date interval is easy, assuming you are searching inside a single date field in the related table. You just specify StartDate...EndDate. I need something more difficult: I have 2 related tables: 2 date fields in the child database (StartDate and EndDate). A one to many relationship. Every child record has a date interval. I need to find all parent records that, given a search date (single date )or interval (StartDate...EndDate), have no child records in which that date or interval is present. And I mean parent records: the search result must show parent records. All searches are scripted and carried out from the…

I need to search for 4-8 items that will appear in 4-8 fields, but may appear in any order. Some of the items may be repeats of each other, or they may be unique. The DB is a flat file listing of the vacuum tubes used in antique radios. The DB has a field for the count of tubes, then eight fields for the tube names used. If it is a 4 tube radio, the tube count field will have an entry of 4, then only the first four tube fields will be filled in with data for the tube names. The tube names are alphanumeric but for simplicity I'll use numerals only. Example. I have a five tube radio using these tubes: 47, 47, 49, 50, 51. I want to find only entries that have five tu…

Greetings; I am trying to find out how you go about making filemaker so that you click on a button that opens a window (list) and in the list is a range of options, for example "Find everything without images, Sort by Name, Sort by Category, Find Items that are by wholesaler" and when you click on the option it runs the script. I have seen this in a early version of filemaker but can't seem to find it again, in that version they were using for sorting i believe.

I hope this is the right place to post this...if not, I do apologize. First of all, I'm a bit new to File Maker...I've have experience with some other databases, but I've never used file maker before. The company I work for uses file maker, and I have something that I would like to do. First of all, I have created a database in FM that has quite a few records. What I want to do is this: I want to be able upload a file specifically, an excel file, then have FM search every part number in one column from that file against the data in the FM databse. In other words..I want to upload a file into FM..FM will then take all the part numbers in one column…

I would like to set up a script to find records for the current month only. For Example: If I am running the script in September I would like all of the found records to be from September Only. How would I set this variable up in a Standard "Perform Find" script step or is there another way to do it? Maybee I can incorporate a calculation into a find somehow. Note: I do have fields setup within the record that are timestamped. This is the field data I will use for the finds. Thanks

I'm new to this forum, so pardon me if I am posting this in the wrong area. I have been using Filemaker for about ten years, and I'm basically self-taught. I am confortable creating databases, relationships, layouts, calculations and scripts. I've been able to muddle through on my own... until now. Ten years ago we began using one of the Filemaker example templates as our order entry system. The mistake was not setting up a true customer database at that time. Now, 10 years and 30,000 sales records later, I need to create a customer database. This means finding all matching sales records for each customer, based on name, company, address, or some combination. …

I have successfully setup 3 related fields that display value lists based on related content of another field. In other words, the value list of field1 contains all field data, field2 contains only related data from field1 and field3 contains only related data from field2. This works great in browse mode, but not in find mode. I can get field1 to display all field data, but cannot get field2 or field3 to display any value list. Is it possible to set up this type of relationship to work in find mode? If so, how? Regards

Hi, I need to be able to do the following find and am not sure if it is possible. Step 1 - do a find on Acts in a database and bring back a list and a few other bits and pieces and print out a report with details of these acts, Step 2 - then click continue and jump over to the Events database where I want another report to be run which shows the events for the acts which where selected in the find above on the act database. Is this possible?

I have run in to an issue that only occurs on windows platform PCs. I have tried it on 3 machines so far and have the same problems on all. when in a database, do a find (control + F) enter a value in the first field, and then add a new request (Control + N) and nothing happens when you use the key command. If you select "New Request from the Request menu, it works. All the macs we have in the office work when this action is done, but the windows machines just dont want to. Has anyone experienced this or know what the fix could be? Any help is appreciated!!!!

Hello, Can anyone help with this problem please? I have a basic database which is numbered by unique record Id's. I would like filemaker to auto enter (lookup) all data to be entered into a particular layout (about 15 fields) but only when I enter the unique Id into a particular field.. the rest by the time it would be entered manually.. If anyone can advise what is the best way to approach this I would be most grateful..

I know this was discussed before but I couldn't find a solution to my problem. I am trying to filter a table to show non zero balances where Balance is an unstored calc field. I know that searching an unstored calc field isn't a good thing but I don't know how else I can get the info I need. Please see attached Any help will be highly appreciated. To rectify: There is a mistake in the attachement: the seach is on Balance field (the calc field) and not on the TotBal field (the summary field). Thus The problem is: the seach is slow when I search the Balance field in Transactions. sorry the mistake. slowsearch.pdf

I have a database that keeps track of inventory. I have a field [location] that has only 4 options. How do I get a count of the records with each of the 4 options? I would like to put a global on a layout that would tell the user how many of a certain type of product is in each of the 4 locations. Any help would be appreciated! Thank you, Tony

I needed all records which have text in a number field. No prob, says I ... we can find text in number field easily. Not so. Attached is a small sample file to play with. If there is a comma or a space within the text, we cannot find it. I am trying to find (specifically) data like this: Long, Lucas (LLUCA01) Smith, Wil Nelson, David I can find it with *,*. But it got we wondering about why the following examples won't work: I can search for a and find a but not a, nor a, L. I can search for ? but it doesn't find 34a or a 34 or Long, Lucas (LLUCA01). I can search for a, L but it doesn't find a, L nor a, nor a. I assume the principle is som…

I have an indexed field: (Model Number) which houses data such as: "Freud 99-036" or "Freud 44665" and so on. 120k records in the database. Whenever I search for an item with a hyphen in it, it severely hangs, like for 2-3 minutes before returning the results. Is there a solution?

Hi I'm new to this forum and I was wondering if you can help me. I'm not going to go into too much detail: I have a database that searches a referenced file for a date range, then copies all the primary keys from the found set to a local table. The primary keys in the local database are related to the primary keys in the referenced file. The local database has a layout that displays data from local database (primary keys) with some fields from the referenced database (NB: The layout is set to display records from the local database). I preform a search on that layout for: 'S01 - Warranty Replacement' and I get back blank fields, fields with the wrong info ec…

Hi all, This is a really noob question but hey... I'd like to be able to produce a report of all quotes or invoices produced between 2 user specified dates, inclusive. Is there a simple script that I can adopt or will I need to pull more of my hair out? LOL Many thanks for your guidance on this one.

Hi Can anyone help on how to make make FM search for duplicates in 2 fields at the same time. Meaning a duplicate is defined by a match on 'Street' and 'housenumber' at the same time. Example: A duplicate is when two records have the same pair of data in two different fields, I.e. 'Street' in record1 is similar to 'Street' in record 2, while at the same time 'Housenumber' in record1 is similar to 'Housenumber in record2. I thought I could do it by searching '!', both in the field 'Street' and at the same time '!' in 'Housenumber'. But that didnt really work. Can anyone help? Regards Lasse

Greetings again -) Trying to get records to sort like this: Region, Price B, $6 B, $7 B, $9 C, $5 C, $6 C, $8 A, $4 A, $8 A, $9 That is, list the regions in order of which has the highest lowest price. The lowest price in B is $6 which is higher than the lowest prices in C and A. So I want to put the prices in order for each region, then put the regions in order according to the one with the lowest price. Can this be with Sort? BY using a summary field somehow? If not, with a summary report? Been struggling with it. Thanks

Hey everyone... I am looking for some general advice / best practise on scripted search using globals. Now and then I read that one way of performing a search is to enter search terms in browse mode using global fields and then perform the find. I wonder what this means exactly. Does it mean that for each field that I want to make searchable I create a global field on a search layout where the user enters the search terms when in Browse mode? Then, after clicking the Submit button he gets put in Find mode and the script fills the actual search fields with what was entered in the global fields? So, I would be grateful to receive some general advice on this; …

Greetings, Trying to list just the records with the lowest price for each item and having much difficulty. Example records: ItemID, Price 1 $1 1 $2 1 $3 2 $1 2 $2 2 $3 I would want it to just list: ItemID, Price 1 $1 2 $1 omitting the others. Thanks

I have a database, I pull a current records list of a group of things. My question is when I pull this and open a new window to show the list. I have a button to take me to the layout for more information on the record. But it continues to leave the extra window on the screen. If I close the screen in the script it takes me back to a record that I was on when I pulled the list. Is there a way to create a button to select the record in the open window then close the window on the selected window and that record be in the window that i pulled the list with. Thanks in advance. I hope I have explained it well enough.

Greetings, When I use Find to find duplicates (records with the same value in the same field), it finds *far* more than the duplicates. I have about 100,000 records and it finds about 99,900 when it should find about 1000. I can't figure out what is going on. I click the find button and put an ! in the field I want to check for duplicates in. Help

I've got a calculation field that contains account balance information. The calculation references a summary field and several others. I now understand that you can't do a find in a summary field because the information isn't generated until the find is completed, thus I can't perform finds on my balance field. Is there a way to "capture" the information from the balance field into a non-summary field where finds can be performed. I've got it working by creating a new number field and then running a looping script to set the new non-summary field,but it is SLOW. There has to be a better way to do this.