Finding & Searching

I currently have a "PARTS" table with over 120,000 records... This makes the database unbelievably slow (that is, tables that used records related from this table)! What I want to do is write a script that sorts record by modified/accessed date; any records that have not been accessed in 3,6,or 9 months (drop-down value) I would like to duplicate them, put them into a "BACKUP PARTS" table (that will not be accessed other than manually), and then delete the found set. Is it possible to sort records by a access/modification date?

Hi, I've set up a database that allows you manage contacts and companies through a marketing process. Within this we allocate "notes" by which you can add tasks to do. Now the problem rises when one tries to look at all "notes" due before a certain date. I enter find mode and type <=24/05/2007 into my date field to search for all "notes" with a to do date on or before the 24th may 2007. The results i get are all dates that have the first two digits less than or equal to 24. Any clue on fixing this??? Cheers Elliot

Hi, I have a a table (supplier Line Items) that show items from suppliers, a bit like a catalogue and i would like to select some, maybe even all of the items and be able to create a purchase order. I can do a search and am able to find all the items I have selected (say 4 items out of 100), the next stage is to copy each item and paste into my purchase order line items table. Would a loop script be the best way to go? Does this make sense?? ha ha Thanks as always in advance. Roy

I'm using FM 8.03 and the database is on a server. I have two tables joined: people and address. If do a search for the city with records from the Address table (in other words, not relying on the related table), all is fine. When I do a seach for city from the people layout, I'll get the correct entries, BUT ALSO I'll get some incorrect entries. For instance, when I search for first name beginning with P, and in London, I'll get some people from Glasgow, Birmingham, and various other cities. But when I did a test field using the Address table, then all was fine - so it has something to do with the linking between tables. This works the same whethe…

When I use Find to search for one record by name it is finding all the records in the database rather than the specific record I am looking for. This is on a server edition.

HELLO FRIENDS AND EXPERTS. I have a url that verify if a user signed up to my website based on two fields. pswrdCalc is one field after a USER CAN login with just supply that one field content. HOW DO I ADD ANOTHER VARIABLE TO SEARCH BY IN THE SAME EXPRESS. LIKE THE USER NAME? I want to search like user=tom pswrdCalc=123. The expression below is only for pswrdCalc how can i add user to search by as well. Thanks &-Op=eq&pswrdCalc=[FMP-CurrentFind][FMP-FindValueItem][/FMP-CurrentFind]&-Max=1&-Find">

I'm trying to run a report that displays all thos individuals that have account balances greater than zero. The search is taking place in a calculated field 'TotalOwed' which takes a person's TotalCharged and subtracts their 'TotalPayments'...both of these are Summary fields. I have no issue showing the report with everyone's balance but it gives me everybody in the database and not just those with TotalOwed > 0. Are calculation fields not designed to respond to finds like this? My script looks like this: Perform Find (TotalOwed > 0) New Window Go to layout Adjust Window Sort Records Enter Preview Mode Again, this gives me the report…

All I want the find to do is search a customer table and pull up the customers info. In this scenario there are two tables being used, searching from one to the other. Entering "Find" only seems to search the current table the layout is designed around. I would appreciate any information you can share. BTW.... I have looked on the forum here and also FMP help and can't seem to get it to work. I will probably do this in a portal field too, is there any difference when working with find in a portal? Thanks Dave

Greetings. I would like to create a script that, among other things, displays all records for the current month, as well as another script that can display all records for the previous month and one for next month. I would like FM to determine the current month so that I don't have to manually enter it. I have a single date field that shows the date of an event. I would like to be able to make buttons for "This Month", "Last Month", and "Next Month" that will operate scripts to do those searches. Any ideas?

Dear FileMaker Gurus: I have a Sales DB hosted on v.5.5 Server. 9 regions: NE: RegionCode1 Ea: RegionCode2 SE: RegionCode3 So: RegionCode4 Sw: RegionCode5 We: RegionCode6 Nw: RegionCode7 No: RegionCode8 Ce: RegionCode9 On the portal, there are "Region Name""Region Code""Customer Count""Annual Sales""Sales Rep Comments""Significance No" fields. Sales Rep.'s entered their comments on sales prospect on the "Comments" field and labeled them with "Significance Value," from"1" to "10." "1"would be displayed on the top row of the portal. I was asked to export "NE" and "SE" regions' data to a TAB file, regardless their "Significan…

So, after weeks of rebuilding my database - this time correctly, I have successfully gotten to the point where I am stuck again. Here's what I'm trying to do. All I want to do is be able to select a specific association from a list of associations and then view a list of everyone who is part of this association. Here's the file that helped me figure out how to register the association. If you can help, I would really appreciate it. All I need is for someone to show me how to do it once and I'll figure out how to adapt it to my Database, I just need to see it. Thanks! Sort_by_Association.zip

I have a portal (in the table "Quotes") that shows related (by "QuoteID") records from another table, "QuotedItems". I want to duplicate these records only, and display them on a new table. What is the best way to go about this?

Hello, I have a button with a script that is intended to perform a Find using a value entered into a custom dialog. This script generates an error alert rather than performing the Find. As shown in the screen captures in the attached zip file, 1) the input field is assigned to button 1 (default; rightmost) in the custom dialog, 2) I am typing a correct value ("March") in the custom dialog, and 3) if I type "March" into the target field that is referenced in the Input Fields tab in the Custom Dialog dialog, the Find functions properly. What have I done improperly and what is necessary to correct it so that the script will perform the Find after entering…

I know I found it here before, but I can't fint it now. I have an auto enter value starting at 1 for all my records. Now when I made an import I made a mistake so there where no numers filled in. (blank) So nu I have a lot of records with no numbers. Can any body help me with a solution?

hi. i am a researcher in a developmental psychology lab and we use filemaker to store our participants information. we need to be able to search for potential participants based on their age in months. however, if we search in the "age" field for 2...9 and expect that to return subjects that are between 2 and 9 months old, we actually find that it returns all children that are either 2-9 months old, but also 20-99 months old and 200-999 months old. we are using filemaker 7 and the problem occurs on filemaker 8 as well. the field is a calculation based on the current date and the child's birth date. any advice would be helpful. thanks!

I have created an application where I restrict the records that the user can view / edit / report on based on security / privileges that use the users account name. This part works, but when I direct the user to the proper layout all the records are there with the ones they are not allowed to see indicating . I am trying to make it so all they see are their records and not the no access. I have been thinking if I can do a FIND based on user account then I should be able to get rid of the unwanted records. The field where I am capturing the account name is called - "Created_By". Any help would be greatly appreciated. Thanks

this is really messing with my mind, I have a database in which I auto input info from another database. I then need to "find" the last record entered, I have a script writen to do this. Everything works just fineup to the point where it performs the find. I get a message saying there are no matching records. The records are showing in Browse and I can do a find on any other record except the last record in the file. Any ideas?

Seems like this should be easy but I can't seem to get it to work. What I want to do is set a UserID as $ID and go to a listview on different layout and perform find UserID==$ID

My database tracks attendance and enrollments where we have many schools in one program. Not unlike the relationship of schools to a district. Our students however transfer between these schools frequently. They can be in one school for one day then another for a month then another for a week and so forth. Each time a student enters a new school a new enrollment/attendance record is created. I track the total number of days the student is enrolled in each school and then on a summary report, I report the total number of days a student is enrolled in the program/(district). I need to separate and report on students who have been enrolled in our program for 90 days or more.…

I have about 1200 projects in a database, prioritized with a pop-up field. The user gets to choose between five levels of prioriteis symbolized by a square symbol in Zapf Dingbats. For example five squares in a row is a high prioritiy, 3 is less, etc. There are also lower priorities such as a round ball to indicate a job is ready to be invoiced, a hollow ball to indicate it's due and a check mark to indicate it's paid. The problem is that since Apple moved to Unicode, the Zapf Dingbats fonts don't sort in alphabetical order so I can't priorize my tasks in order. Is there a font that can do this? If not, I'll have to use a standard non-symbol font and resort to using a lin…

Hi, I am trying to make sure all my records are always sorted automatically by project number (ascending). Also Including newly added records that have a lower project number then the previous record. I can get the records to sort but they are unsorted as soon as I re-open filemaker or switch to a different layout. How can I make sure my records are always sorted? Dutchy

Hi guys, I'm having a little brainache here and wonder if you could be so kind.... I would like to create a button that transfers to a layout with a list of selected fields in a single table, all records in which having been selected as a result of one field being a 'N' value. I have been looking into this, but it seems that I need to code the button to perform the search as well as jump to the list and I have no idea what functions to use. The basic idea is to hit the button and see all records that contain a N in the specified field. Your help is much appreciated. Cheers Rich EDIT: No worries - i found the 'Perform Find' function and t…

I am having a problem with blank record being found when I search. I will place in a search criteria and it will display records that have no data in them. I can delete the record but can not modify it. However when I rerun the search the record is back. Why do these aronious records show up? When I "show All" and scroll through I do not see them. But when I do a search say based on date (or any field) they show up. Sometimes one record sometime many records all with no visable data. As Seen below, I have 42 records in the search the first 10 are blank. The search in looking for any items that do not have a date in the complete field, I have 1071 total records…

Hi, I'm trying to manage an email address database. The records contain a name, an email address, a building and a position. I would like to be able to search for building and position and any combination. let's say I have two positions, 1 and 2 and three buildings, A, B and C I would like to write an email to: All records with position 1 in Building A (got this, this is easy) or All records with position 1 and 2 in buildings B and C (this is the tricky part where I don't know how to perform the find to accept two checked boxes and show me both) anybody? thanks a bunch M.

hi all, i'm sure this has been covered before but i can't find a way to extend a found set without losing the original record I found. Let's say I search for recordID=255 which pulls up the correct record immediately. I then want to extend the found set to include location=Overseas However, this effectively makes me go to another record than 255. I've also tried 1. search for recordID - 255 2. show all records (this keeps the desired position) 3. omit records where location not=Overseas The only way i can think of doing this atm is to loop through all the records again until i get to the one i want, but it seems that there must be a muc…

My dentist does this, though I am not sure he's using Filemaker Pro to do it. About two weeks before his patients' birthdays, his database guru generates a list of those who are celebrating and everyone gets a birthday card. I thought this would be an outstanding tool for my wife's business, as she's doing a very personal business that is reliant on word-of-mouth recommendation as much as her advertising. She has a 300 or so client databsse that is growing. We had birthday cards printed up and we're generating a list. I've just done the second such and it's kind of a pain. We have a field named "birthday." I do a search every two weeks and generate a printo…

tried to make conditional find in filemaker but I couldn't do it properly. Actually what I want is. let's say There are three fields Field 1 have values, A, B, C, D, ect.. Field 2 have values A1, A2, A3, A4...., B1, B2, B3, B4....., C1, C2, C3, C4..... Field 3 have values A1.1, A1.2, A 1.3, A1.4....., A2.1, A2.2.....B1.1, B1.2 ...... B2.1, B2.2... Now in find mode, if i want to find all related data for "A", It should display all related like A1, A2, A3... and again if i want to find all related data to A1, it should display A1.1, A1.2, A1.3 in third field.

Hi all You've been kind to help me out before, so here we go again: I have a field called "Surname". For some reason I cant "find" it if the data contains of two surnames (ex. John Paul and searching on "Jo Pau") I guess it has something to do with the way the data has been imported in the first place, because if I rewrite the data (or remove the blank and hit space once again), the problem disappears. I have a similar(?) problem with unnessecary blanks trailing the surname. I would also like to get rid of these. Does anyone know how to solve the problem an easy way? Best regards Lasse

Hi, can't seem to get my script right. Have set 6 values in a Status field (E/PI/O/M/C/F). For each record or project one can only fill in one of the above mentioned values. Now I would like to filter all records with a value E, PI and/or O in a separate layout (list view). Made a constrain found set: Project::status [E] AND [PI] AND [O]. But than non are selected or found because there is no record which contains all three values. I know I am doing something wrong here but somehow I am not getting it. The AND should be OR??? Thanks in advanced, Dutchy

I'm sure this topic must have been covered before, but I wouldn't find anything directly relevant... I have about 3000 records, that I sort before working on them. They are sorted by Company (in one table) and then Firstname / Surname (in another related table). The Sort is currently taking about 15 seconds the first time and then much quicker. Similarly I have 500 emails held in 1 table that are sorted by date, this takes about 10 seconds to sort. Compared with the rest of the solution these times are really long, is there anything I could do speed up the sort? The solution is hosted on FM8 server that serves about 10 users. Am I right in thinking that th…

Hello, I come from an Access enviroment. Is there a way to perform a simple query in filemaker Pro 8? I am trying to do a simple group by and sum but I cannot find any function to perform this operation. Any help would be appreciated. Thanks.

Hi, I am a newbie to FM. I have a simple database as a list of contacts. I would like to sort the list and separate the results, from where I will then convert it to a mailing label layout. This way I can sort the relevant names and generate mailing labels without printing out the entire database. For example, say there are 100 names in my contact database. For the purpose of creating mailing labels, I want to sort 40 (or any number, it's arbitrary) onto a separate list to then generate the labels. Basic sorting seems to only reorder the entire database. There is probably a Script to achieve this, but I can't quite figure anything out. Thank you.

I am trying to make a search box in my client database layout, but am not sure on how to do it. I want to have an empty field where the search string is entered and by pressing a button the search is performed while still in browse mode. Thank you

I am trying to write a script that performs a find using values entered in checkboxes. For example, for a field which holds languages spoken (English, French, Spanish, etc.) how would I perform a find for all records listing French? Many thanks in advance,

I have recently switched from MS Access to Filemaker Pro 8.5. Our office is now using both PCs and Macs. In Acceess I could search for "null" values in a relational database. That is searching for those entries that were in one table, but NOT in the other table. I have created a relational database that tracks membership for a certain organization. There are different levels of membership kept in a field called "Status" in the primary table called "Main Table". This table contains basic name, address, status, etc. There is another related table called "Dues" that stores information on membership dues, date, check no. etc. I have no problem extracting information …

I'm very new to FileMaker. I have worked a little with other databases. I made my first FileMaker database which is conversion of one of our old databases. It is a relational 2 table database. My key field is ProjectNumber for the two tables, Each project has 2 Project Numbers one for contract work and another for extra billable work and a status field with shows that the project is open or closed. I have ProjectNumber set as a lookup in the other table. I need to filter out all the Extra Work Numbers and the Closed Projects out of the ProjectNumber Lookup so it shows only the Open Contract Numbers to avoid duplication of project names. …

Well as the title explains I am new with Filemakerforum.com and from what I can see I am excited to get started here! Who know maybe soon I will be capable of answering a few questions as well! Anyway to the question! I am currently designing a database which in all effect is an invoice system that has several related database which the invoice page pulls from, mainly customer information, job site info, product info and trucking comp info(the database will be used to manage material hauling). I would like to bill my customers weekly for all of the invoices that they accumulate through the week! It is possible that the cust may place 10 orders throught the …

Our company tracks TV programs. Each record has information like show name; producer; distributor; assessment notes. There are created date and modified date fields. Sometimes we only have show names to start with and only pass this show information on to clients when all the information is complete (exported to a PDF as they don't use FileMaker). There are hundreds of shows and I want to be able to select them based on 2 found sets. 1. the most recently created AND the most recently modified. (I have no problem doing this.) 2. Manually de-selecting some records from this set (this is my problem). I created a single check box for each record (which I did using a single bo…

I have a feeling that this question is answered all over the forum, but I can't find the right way to phrase my search query. If someone could just shoot me a link to the right answer, I would be grateful. I have a file of data from hand written work logs with the following pertinent fields. Date Name Hours worked A single person will have several entries per day, one for each project they worked on. JB 2hrs TR 1hr JM 2hrs TR 6hrs JB 4hrs JM 2hrs JM 2hrs etc... I want to be able to count how many different people were at work, (ie, how many unique names) in a time period.(records in a range of dates) I have wri…

I'm looking for a way to see what the user just searched for. Currently I have a layout with about 10 fields in it that can be searched. I would like to find out what the user actually searched for, to then show them on the "results" layout "Your search for 'X' came up with the following results." I could probably just loop through the fields on the layout with isEmpty then perform the find but I was wondering if anyone has a better idea. (what if the fields change.. I would have to go back into the script and change the loop to now look at the "new" field and delete the old ones.) I know filemaker "stores" the find.. for use in "modify last find" but I ca…

I have a parent record of Names and a child table of Contacts for those folks. I also have a related data masterfile for contacts so I can track which contacts are active. I have been trying to create a table occurence using a global in the Names Parent set to Active which relates to the "Active" field in the master, if the contact is active the field is set to active. I am trying to use this to to make the drop down list of contacts show only the active. I have used the debugger to show the values of the global and the value in the child db, and they seem correct, yet the drop keeps retrieveing the active and non-active contacts. I am missing something fundamental, b…

if I have a record with 30 fields and i wanted to know which field had the largest numeric value (i would like to know the value and the name of the field containing it) how would i go about doing so?

Hello all, In adding a feature to my db for summing clients outstanding dues to a predefined limit I'm running into a snafu. I would assume that this has to be done using a script. Here is my first attempt, which is probably WAY off! Any help would be cool! THANKS Dave

I have a separate table for clients and projects. made a portal to incorporate just the name and address of the client into the project layout. Problem is that I have hundreds of clients of which some have extremely long names. I would like to keep the full name to be seen in the field but making a search easier by means of a [color:red]search key? For example: the search key being the abbreviation of the company name. I have made a separate field in the client table with the abbreviations of the companies if they have any. Can I create a search key, or something similar to make the search for a client easier? I hope my description of the problem is not too vague?? …

I have looked in the forums for this information but I did not see anything that would help me or maybe I am just going about the wrong way. I have many groups of people and I need to be able to search for all of them from one line. Here is an example: Group1 has persons ABC Group2 has persons DEF Group3 has persons GAE In the data base they are defined as person1=a person2=b..... and so on and so forth. I would like to search for all people from one line in search mode. Please let me know if this is possible. Any help would be greatly appreciated. Thank you.

I am attempting to create a POS system for an automotive repair facility. I am the owner of the facility (not a dedicated programmer). This is how I decided to build the system: I created 6 databases: -PeoplePlaces (for contacts - customer and vendor) -Vehicles (to hold vehicle information) -SalesTransactions (to act as invoicing)...also this database has a table called SalesTransactionDetails (for line details) -PurchasesTransactions (to act as purchase orders)...also this database has a table called PurchasesTransactionDetails (for line details) -PartsLabor (to hold Parts and Labor info for use on SalesTransactionDetails and later, invent…

Goodmorning... is it possible to have a search field? for 3 different fields? I have a list of specific "categories"(value) that can describe what a company sells. One company could be in more that one category.(it could sell 2 or 3 different products) so I have 3 fields that can describe a category of the company. Can I make a single search for these 3 different fields (that will be filled by words of the same value list)? I hope this is clear and makes sense... thank you ps. this value list has more than 100 entries so the order of entering the category in the 3 different fields could vary

Hello. Can anyone describe how I can setup a script to find all records that contain data in one particular field? I do not want to retrieve records that do not have any data in a defined field (null) I think the "IsValid tablename:fieldname" function plays a role here but I am so brain dead on where to go from here. Thanks much!

Friends: I search for some records, find five and select one (by clicking on it), then I change my layout to a detail one for editing that record. The question is: How do I omit the other 4 records in the selection so that when I print, via a button, the others wont print as well? Thanks amigos! Migs

I am trying to writeords that a script that finds records thatmatch criteria that the user enters with a checkbox set. I have a contact database with a field for groupmembership. Each record can be a member of a group. group1, group2 etc. I have a checkbox that list the groupnames from a value list. I want to create a found set based on the groups selected from the checkbox. Some how I need to write a script that extends a found set according to values checked in checkbox

What am I doing wrong? I have a list (created via the list function) of documentIDs. I want to script a search so that each record with the documentIDs is found (then that found set will appear in a portal in a related table). My script (loosely coded here) Set variable ($DocumentIDs =list) Set variable ($loopCounter = 1) Set variable ($stopLooping = valueCount($DocumentIDs) Enter Find Mode Loop set field docID GetValue($DocumentIDs, $Counter) new request set variable ($loopCounter = loopCounter + 1) Exit Loop if $loopCounter > $stopLooping End Loop Perform Find I am getting really wierd results, even though when I monitor the va…

I am working on a solution in which "Client" is a parent record, and "Service Call" is a child of "Client". There may be one or more Service Calls related to one Client. How do I get FM to show data from the last Service Call for a Client? It defaults to showing data from the first Service Call for that Client. (Each Service Call has a date field called "Service Date", which indicates the date of that Service Call.) Thanx, xanx

Good Morning All, I hope your weekends were good. I can test to see if a window is open using Select Window. However, if the window does not exist, how should I test for that? I could close the window (whether it exists or not) and reopen it, but that seems so non-elegant.... Thanks much, Royce

I'm looking at Filemaker as a replacement for an old database program I use and have it on a free trial basis right now. One of the most important things I do is print reports based on multiple criteria e.g. if price >= 300000 and (zip=aaaaa&zip=bbbbb)then print it out I repeat these same sequences each month for print out, based on that month's particular information. I've figured out a lot in the past few hours on how to build data bases, import, export, print reports etc but I can't figure out how to perform these finds and then SAVE the find criteria for use in a different database next month. I have a number of sets of criteria that I …

Does anyone know of a way to compare contents of a field to the contents of the same field in the previous record? I'm having trouble employing Get(RecordNumber) since the record number remains constant and doesn't change when I sort. I know it should not work like this but it does. Any ideas?

Hi, I am working on a huge application which was already existing, I need to change couple of things, but I wonder where they are in the huge ocean of script. Is there any way I can search all the scripts for few words ? Like how we use "Find" in VB or other langauges. This will really help me save lot of time going through each and every related scripts and subscripts. Please save me. Thanks in advance. :

How is it possible to add a option to the search field that when its empty and you press return or enter to show all records again? Also is the plug in the same as event script plug in? If so would it be possible to use the event script instead of the other one? Thanks TMAS Spotlight Link

Hi gurus and experts I have a question that im trying to solve with little to no luck. I want to be able to search my database to grab all the data based on a specific team in my hockey league. More specifically, what Id like to do is this: If you are viewing the stats for team ABC, Id like for it to go to the table and layout of the league schedule, and pull out the data for that team (both home and away games) automatically, without my having to hard code the names of the team in. I have a relationship between the teams and the schedule, so you wouldnt think this would be so hard, but its driving me batty. Right now, I just have a code that grabs the…

In our solution, we have several layouts which have portals containing data to be searched. Our people want to be able to perform searches for several items in the same field in the portal (for example: catalog number [master record], components [ portal records] h search: how many catalog numbers have components x, y and z?). The portal will only show one record in find mode. If you go to the line items layout, how do you link the components to a catalog number? HELP!!

Hi I have problem that I trying to constrain an found set (table). The fielded I am using to do this was developed with an value list. The field as range of place like "Otago" "West Coast" "Auckland" and other place etc what I trying to do is just show in the search "Otago" "West Coast" for example I have tried some scrips... but don't seem to work, I can constrain an found set to just show "Otago" any through on this would be great Cheers D

I am a teacher designing an annual roster of member chefs of a national association. My key problem is trying understand how to create a search field that passes the user's entry to a a dummy variable and then passes that variable to search the database using the Let()function or any other bloody function for matter. The samples and help module is great for the person who wrote it and not for a learner. I know how to setup a standard search routine when the user clicks on a button to initiate the FIND but the application I have to create is strictly information and absolutely no additions or editing is to be allowed. All data is displayed in the FORM format so…

This seems silly that I have to post for this answer, but I'm going crazy. I have a database of company records, and I want to find companies that BEGIN with letter "A". Like "Acme Brick","American Financial",etc. NOT "Jim's Heating & Air" nor "Bob's Alley". How can you do this in find mode and/or in a script? Below is an section of my script that doesn't seem to do the trick. HELP! Enter Find Mode Set Field [ Company, "A*" ] Perform Find Go to Record/Request/Page[ First ] Loop If [ not Left(LeftWords(Company, 1),1) = "A" ] Omit Record End If Exit Loop If [ Status(CurrentRecordNumber) = Status(CurrentFoundCount) ] Go to Record/Re…

Hello, I have searched the forums, and check all my fm books, but i cant seem to find an answer. Sorry in advanced if this seems like a basic question! I have a portal that lists student preferences and options. It is basically a one to many relationship. So a student can have 0 preferences, or they can have 100. My question is, how do i do a search for multiple preferences within my portal? I have tried scripts that use "Contain Found Set" and "Extend Found Set" the only problem is i can't get the exact match I am looking for. Hope this make sense and thanks!

Good morning, I'm sure there is a simple answer to this question but I need to ask it anyway. If someone searches for a particular date range to generate a report of enrollments for that period, how can I convert that date range to display each month/year in the period? For example, If I wanted to know the number of enrollments for December 2006 and January 2007 I would have to search a range (12/01/2006...01/31/2007) but the enrollments would be lumped together. I want to see the results from month to month. Any help is greatly appreciated. I'm working in FM 7 currently if that helps.

I am brand new to FileMaker, using Excel for almost everything but larger databases is demanding my migration to FileMaker. I often have a number of subject catalogue numbers (either numeric or alphanumeric or a combination) and I need to find records whose Field for Catalogue Number CONTAINS these lookup values. The lookup numbers may be combined with other numbers or alpha characters in the database I need to interrogate. Is it possible to build a script that will find all of the records that contain the subject numbers within their catalogue numbers? Thank you

Hi all - I just ran into something for the first with FileMaker Pro 8.5. It has to do with doing a find using a range in a text field. We have a field in our solution that stores a case number. An example of a case number is "S-07-00001". In FileMaker Pro 6 and earlier, we could search on that field using a range such as: "S-07-00001...S-07-00010" The result of that search would be a found set of records with case numbers in the case number field between S-07-00001 and S-07-00010 inclusive. In FileMaker Pro 8.5(and possibly 7 and 8), entering that range gives us the "No records match this set of find requests" message…

Folks How do I find OXF1-30-D-G1, OXF2-42-D-G1, etc, in a list that includes some 20,000 different variations of OXFsomethingGsomething? I have tried OXF*G1, but it doesn't work. What am I doing wrong? I used to be able to perform this find in earlier versions of the program, but I know FM changed somethings in 7. 8.5 Advanced

I'm so lost it's not even funny. My apologies ahead of time if this subject belongs some where else. My boss has asked me to look into creating reports and I have no idea what I'm doing. We use FileMaker for our purchase orders right now and I need to find out how to generate a daily report, a weekly report and a monthly report using the creation date, order number and client's name. Please keep in mind that I'm sooo not tech-savy and will probably need step-by-step instructions in super simple terms. Anyone? Please? I'll love you for life : Thanks, -trina

My data consists of street names, like "Kingston Hwy" or "Nelson Rd." There can be multiple records with the same street name; for example, 36 records with "Kingston Hwy." I need to keep the database unsorted so that its original order is preserved. (I could create a new field and fill it incrementally so that the database could be resorted using the new field to return it to its original order.) Can I create a new field called UNIQUE that will mark (with a character) every unique street name? For duplicates, it's okay for it to mark the first record among the duplicates. So for the 36 records of "Kingston Hwy," one record would be populated with an X in th…

Hello, I have two data sets, one with ~300 records (Table A) and the other with ~1,400 (Table , both containing unique IDs (i.e., each ID in Table A occurs only once; likewise for the IDs in Table :. Some of the IDs in the 300 records are missing from the 1,400. Both tables have certain fields in common; e.g., gender, country, education level. The goal is to search Table B for each record's ID in Table A. If a match is found (again, some IDs in Table A are missing from Table :, copy data from fields in B for which the corresponding fields in A are blank. Could someone please explain exactly how to script and/or calculate this in FMP 8.5 advanced? Or, if thi…

I have a simple problem (I hope). I wish to validate e-mail addresses. I just want to do a simple search that tells me all the addresses that do not have the form: "[email protected]" I was hoping to accomplish this by doing a search, something like *@*.* ...however, the @ is a wildcard from what I understand. Any ideas how I can do this?

I'm using Global fields to accept a start date and an end date for selecting a range of dates for printed reports. The scripted find request uses this syntax: Enter Find Mode Set field DisplayDate (to this result:) GetAsText(MAIN TABLE::G_DateStart & "..." & (MAIN TABLE::G_DateEnd)) The find works, however the found set never includes the last date. For example, if I enter "01/01/07" as the start date and "01/31/07" as the end date, I get records through 01/30/07, but NOT the records generated on 1/31/07. This same pattern holds true no matter what the end date may be. I have tried adding a "+ 1" in the calculation like this: GetAsText(M…

I have two tables: patients and visits. A layout in the visits table gives a sub-summary report of the kinds of visits and actions performed over a given period. I need to find the actual number or unique or distinct patients based on the found set of visits (as one patient could have been visited more than once in a period). A Summary field of the Count variety works in the patients table, but is useless in the visits table. Help please! (for a good non-profit cause)

Ok, I've been staring at this for a few days and have pages of notes to myself about possible ways to do this, but I'd like some input on whether I'm overlooking some nice clean way to do this. I'm working with a table "Timesheet" where employee timesheet records are stored, and table "ReportPreferences" where I'll store user preferences about the types of reports they run most frequently. In "ReportPreferences", I've got a field "EmpCode" and a field "ActivityType" each of which are formatted as checkboxes from value lists stored in other tables (Activities and Employees). If a user selects multiple criteria in one or both of these checkboxes, how can I be…

I have two global number fields. Our Invoices have standard number TotalDue and standard number BalanceDue. I want to find all invoices who were short-paid by 2%. But, because of rounding of the decimal, not all invoices are EXACTLY 2%, thus the decimal number range search. Some of our large chain stores require extending decimal to 5 places! I've attached a test file. I decided to use the relationship approach (unless others have better idea). But it bothers me that I couldn't perform the 'Find 2% Range' script. Data is entered as .0195 and .0295. Range is gPercentFrom & ".." & gPercentTo ] but it doesn't work. For future, how can I find on a dec…

I have 3 files in FM8.5 for this scenario: 1) GUI (File Refs, TOs, relationships, scripts) 2) Data (Tables, Data ,TOs to support calc fields) 3) Print (TOs, relationships, scripts) I need to be able to move a found set from the gui file to the print file so that when a user goes to print a form or report, the found set will mirror what they were viewing in the gui. I need this to be IWP compatible if possible.

Hello, I have a simple database, and one of the fields is a "date" field. I have to make reports from month to month, how can i use the find tool to find only the records from that specific month. Thanks!

I have a layout which is designed to show a list of records. All have different or the same due dates. I need to group some records that are simular so I highlight this by giving them the same colour(color). Obviously when I have sorted by date order these colour could be all over the place. Is it possible to then sort these records then by date but secondly by the colour? I know I could have a seperate field and give the same numbers and sort by that but as a visual aid... the colours work much better. Or, I could use a seperate field where I group with numbers from 1 to 10 as part of a value list but would it be possible to give each number a different co…

I appreciate any help on this long-winded request. I'm very new to Filemaker, but I'm setting up an application that allows a teacher colleague the ability to search and filter State Standards by a number of criteria. The workflow consists of the following: (Please see attached screen shot) teacher selects grade level (drop down value list) teacher selects standard type (radio button value list) teacher selects domain type (radio button value list) teacher selects students proficiency level (radio button value list teacher clicks on SEARCH STANDARDS button a portal to the right (on the same layout) now shows all related records based upon the multiple filter cr…

Hi Folks, I have a fairly simple database with one table. One of my fields is 'Date', which is represents the date on which I created the record. I'm trying to write a find script that finds a date range. The date range will almost always be the records between today and the past 7 days. How do I write this? Thanks!

Hello FileMakerers, I'm struggling with the lack of a wildcard symbol specifically for letters as opposed to digits. # represents one digit; @ represents one of any character; but there is no symbol specifically for one alphabetical character. One example of how I want to apply this is in searching for valid UK postcode formats (I could also use this in the definition of a calcualation field to return 1 or 0 for the postcode field being valid). In Access I would use a string along the lines of Like "[A-Z][0-9] [0-9][A-Z][A-Z]" Or Like "[A-Z][0-9][0-9] [0-9][A-Z][A-Z]" Or Like "[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]" Or Like "[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]" Or Lik…

Anyone know how I can find records with an image (in a container) present? I have to do this on version 6, unfortunately. Any ideas? Thanks, Chris

I have a transaction layout with 10 independent transaction sections. I want to be able to provide an invoice with transactions completed in a certain date range but with 10 different transaction dates I'm not sure how to do that. Is there a way to do a search of 10 different date fields at once for a particular range (ex. 12/1/2006...12/31/2006) ? Silly question, I'm sure but if there's a way to do it I would be greatly appreciative. Thanks

Hi, I'm having a problem conceptualizing an SQL query in FM. How would you do the equivalent of a SQL Group BY and SUM in FM? Let say I have a single table: FILES (pkFileID, fkDayID, FileCode, WorkedTime) pkFileID --> Primary key fkDayID --> Foreign key FileCode --> A Code such as "GE-TR-123" WorkedTime --> A number which represents the work time on a file. In SQL would do: SELECT Files.FileCode, SUM (Files.WorkedTime) as TotalWorkedTime FROM Files GROUP BY Files.FileCode This would give me a list with the amount of work time for each FILE. How can I get the equivalent in FM? Thank you

i have a database of about 7000 records. Each record is made up of 2 fields - ID# and Date of Svc. Here is my problem: I want to make a mailing list and i must isolate all duplicate ID#. How do I do that. If I use the ! symbol, i must manually delete all entries for an ID# and just leave one. That will take forever. I am sure there is a much easier way to do that. Any help would be very appreciated.

This is a problem I've had with FM6 on and off for a while. I have a value list on which there are about 20 values. One of them is called 'Pensions Distribution' and the other is called 'Pension Incentive'. When I search for 'Pension Distribution' records with the value 'Pension Incentive' are returned. I once called FM about this and got no explanation - except the suggestion that I shuld scrap the field and the associated value list and start again....Any ideas as to why it's doing this? PMDA

I find myself doing a lot of copying business names and pasting them into Google to find phone numbers. Is it possible to do a script to execute when I click on the business name in FileMaker that will make this task quicker?

I have a scripted find function that limits a found set to only include records the user has created. The 'Find Matching' context menu (right-click) ignores this function and creates a security loophole, as it will search across all records. I cant change the Access Right privileges Menu Commands because it removes additional functions that i need. Can this be "Find Matching" be modified or removed all together (with still leaving the scripted Find menu function)?

I am making an IWP site using filemaker. I currently have a form on the website (it's a form to enter lease information for a storage building.) This layout is tied to a table called newLeaseTemp. This table has a field called MailingZip, MailingCity, FeeTax, and FeeTotal. I have another table called ColoradoTaxRates. This table has all of the tax rates for Colorado sorted by City and Zip Code. Basically, when the user enters a zip code and a city name on the form and clicks a "continue" button, I want it to auto populate the FeeTax and FeeTotal fields. I have a relationship set up between the tables for the city and zip code. I just dont' understand what…

2nd time around using FM. Last time was before Claris owned the product. But trying to create a display in a list view that could be used in other solutions I am creating. Did a bunch of searches here in the forum trying to find what I assume is a simple answer. Tore apart all the examples I could find that came with 8.5. Looking for a direction to go down and hopefully I can figure it out my self from there. Example, 102 sales reps with all kinds of other data we track other then address and phone number. One being sales year to date. Looking for away to show top sales people on a list view, which allows for the layout we want vs tables, and easy return to I think it is …

I would like to be able to search by preferred customer. In my contacts module I have a preferred customer drop down list with Yes and No in the list. Is it possible when searching for customer to show only preferred customers?

Hi I have a database setup it is just a edited version of the Business Productivity Kit. What happens is when I add a contact lets say John Murphy everything works fine. But in this database I need to add maybe five or six different John Murphy's with different address's. When I go to sales orders and lookup John Murphy it only shows one John Murphy even though there are 6 in the contacs. Any ideas how I can show all 6 John Murphy's???

My boss has requested a report of all records with null fields, and a list of all null fields in each record. For example: Record151 has fields X,Y,Z null; Record235 has fields J,K,L null. Any ideas on how to do this without having to "hardcode" each field name into the script. There are probably about 150 fields per record (database keeps records of doctor credentialing data). I was thinking I could do some kind of script that basically used CASE and a loop to check each field of each record and use Get (ActiveFieldName) and Get (RecordID), but I was hoping I wouldn't have to select every field name in the CASE calculation by hand. Is there a way to chek eac…

It is my understanding that you cannot do a find on a summary field. Is that correct? Is the only work around writing a script that loops through and omits records that you don't want?

I have a DB that has duplicates in it. I need to be able to display the top ten records with most duplicates. Not sure if this enough info. Working on other issues right now, just trying to get a jump for when I dive into this one.

Greetings All! Forgive me, I am having difficulty transitioning from the 5/6 way of thinking to the 7/8... I had a find script that worked really well; when a user entered the parameters they were looking for into one or more fields, if more than one record was found then they would get a list layout from which to choose which one they wanted. The script involved the Status(CurrentFoundCount), and I used an IF/Else statement that would calculate when the current found count was greater than 1, it would display this other layout other wise display the master layout. I've tried using the Get(FoundCount), but I'm running into a wall. I tried using the Get(F…

What is the simplest way to find serial numbers which are odd or even in a database? I'm not a programmer, so I need a simple method or something I can cut and paste. Thanks!

I'm not sure if this is the best forum for this question and my question may sound dumb but here goes... I want to have two arrow buttons (one for back and one for forwards) on a layout that allow the user to flip through the records in a file while in browse mode. I didn't see a function for this in the button setup dialog box. Any suggestions? Thanks in advance for your help :)

Is there an easy way to pass a parameter to a script that can be parced to perform a find and update on a field value where the field name to be updated is a parameter? In otherwords I would like to pass Primary Key Field Name to Update Value I know how to do the "find" based on a primary key but updating a field based on a variable isn't clear. The reason I am having difficulty is the "Set Field" script step requires an explicit selection of the field to be updated but I want to do a "calculated" field to update based on the passed parameter. Is there a better way to do this without a bunch of IF/ElseIf/Endif script statements?

I am using a repeating field “Shopping Cart” to let customers note down whether they want to add a particular film shoot location photo to their ‘album’. This is not a full fledged Shopping Cart solution but does the job well except for one problem. I have a script put a “Yes” in the Shopping Cart repeating field to put the photo in the customer’s Shopping Cart, where the repeating field is 1 if the account name is 314001, 2 if the account name is 314002, etc., as follows: Set Field [Locations::Shopping Cart[Get (AccountName) - 314000]; “Yes”], and another Script puts a “No” in the repeating field (it is a radio button), using the same approach: Set …