Finding & Searching

A basic question from a newbie: Whenever I perform a query that returns a found set, I can use that found set into any layout based on the same table that the found set is based upon. But if I try to use that found set on a layout based on another table, Filemaker drops the found set and reverts to showing all records. What must be done to carry my found set on any given layout and only revert when I issue a Show All Records command in a chosen script.

I have a database with a table (Books) with three values (Trans_ID, Prev_ID, and User_ID). The User_ID is an auto-entered value based on the current user's ID. The Trans_ID is an auto-entered serial value. The problem is with the Prev_ID field. I'm trying to get the last Trans_ID entered by that particular user and I keep having issues doing so. So far my best effort was to create another instance of the table in the "Relationships" tab of the Books table (Books Prev Calc), make them relatable by matching User_ID to User_ID, then make Prev_ID a calculation: Evaluate this calculation from the contect of Books Prev Calc GetNthRecord ( Books Prev Calc::Trans_ID ; Get…

Trying to report unique instances of duplicates after constraining find I have the following structure; FIELDS; UniqueID Equip_ID Location TestNumber TestDate This table records each test number that a piece of equipment performs, along with the date of the test and the location. There are often multiple tests per piece of equipment on a given day. I'm trying to report the following; 1) Multiple instances of a Equip_ID in a given time period. 2) Locations transmitting from Equip_ID in a given timer period. I want to be able to take this; Equip_ID Location TestNumber TestDate 1 NewYork 111 10-5-2013 1 NewYork 112 10-5-2013 1 NewYork 113 10-5-2013 2 Utah 245 10-5-…

Using FMPro 11 on server Two issues. Issue 1 Field set up as text. Format of data entered is 201/2013 - first number is sequential, second number represents year. Numbering restarts every calendar year. When searching for a range between 140/2013 and 150/2013 [140/2013...150/2013], the results are as if [140*...150*] - so I'm getting all years rather than just 2013. Issue 2 Second field. Set up as date format with drop down calendar. Searching for a specific date, e.g. 12/12/2013. Returns all dates as if 12 * 2013 (that's month/day/year). ___________ I've tried checking all settings/parameters but can't figure out why I'm unable to get …

I'm using the goto related record script step to access records in another file but I can't get them to sort. I have tried defining the sort I require in the define relationships dialogue box. I know I could define a sort as part of a script trigger (on layout enter) in the other file but I didn't want to do this or don't I have a choice? Another minor issue I've noticed is that I have had to select new window in the goto related records dialogue box to go to the other file otherwise nothing happens. Does anyone know the reason for this? Thanks in advance.

Okay, calculations are not my strength and I am struggling with soemthing which I believe shoudl be relatively simple. I have a searchable field which will always contain a one or two digit number. For example, if I want to search for all records containing a number between 0 and 4 I can do this by entering "<5" in the search box. However, because of the nature of my database I do not want my user to have to enter "<5" into the search box but I simply want him to be able to enter "5" which then becomes calculated as <5 so as to bring up all records with 0, 1, 2, 3, or 4. If I can do this within my "show custom dialogue Script" that would be great or can it be…

I have a button that performs a find request. Everything works fine. The problem arises when the person requesting the information does not click the button to do a new find and just types dat in the field and replaces the found data with the new typed information. Say for example the person clicks the button to start a find. The find request is to find all the people who are 12. They get the results. NOW they want to find all the 11s. Instead of clicking the button to start a new find, the person enters an 11 in the box and changes the 12 record to 11. How do I make sure they cannot enter data in the field without clicking the find button first? Thanks

Hello, I need to know if a field contains any formatted text (italics, bold, underline, etc.) regardless of its position or content. By searching discussions on this website and others, I have discovered ways to format and unformat selected text and whole fields using various functions, but I have not found a function or script step that will return a "yes" or "no" to the question: does this field contain any formatted text, that is, does it use fonts that are not "regular." Any ideas or suggestions on how to do this? (My experience level is between novice and intermediate, so I apologize if the answer is obvious to more experienced FileMaker users.) Thank…

In Filemaker 12, I have two databases. One with all records and another with mailing addresses and dates when mailed. When in a particular record (in the primary database), I'm looking to have a button that looks in the second database(the mailing database) for any records that match. If a match is found, I'd like the date mailed imorted into the primary database, into a text field (date mailed). This will tell me if it has been mailed to and when. My scripting capabilities are not up to par for this project. Any help is greatly appreciated.

I have a database that shows data based on user permissions. There are three levels: System admin - access to all Office admin - access to all records where the team leader is based on the same office User - access to all records for which the user is a team member. So, I'm struggling with filtering the data for the users. If I was doing this in SQL, I'd use an INNER JOIN to get my list of records joined by the team table, so was thinking I'd use ExecuteSQL to get the list of IDs? But what I don't get is can I use Set Field with an array? So a query like: SELECT C.ClientID FROM ClientDetails C INNER JOIN TeamMembers T ON T.ClientID = C.ClientID WHERE T.TeamMbr = 'JO…

Hi I enter a book in Filemaker Pro 11 Advanced and each record are each page of it. My goal is to use QuickFind and highlight the matched values ONLY inside the found records sets. This means if the user search "DOG" and this word appears 5 times in page 9 then Filemaker must highlight the word dog five times in record (page) 9 and NOT a global highlight of all the field like default. Some can help me to get this result? Thanks. Tom

I have my database currently corted by (effective date) jan-dec and also by a month sort calculation that separates these dates into months. When a user enters this layout the script automatically runs and sorts accordingly. I'd like for filemaker to scroll to the top of the current month say September, so the user immediately sees all current accounts. What calculation would I use? I've tried the get current date function and all it scrolls to is a date in april. Any help is greatly appreciated. Thanks! Matt

I hope one of the many knowledgeable people on this group of forums will be able to assist. I have a database for our HR department. Within the database we retain proof of identification of employees for Anti-Money Laundering (AML) and I need to create a report that shows when identification has expired. I'm trying to create a script that will search for a) active employees either passports or drivers licenses (as the type of document) and c) expiry date < today. My problem is which command or function to use to carry out the find requests and the correct syntax for the request. The layout I've developed for the find is Find Layout for AML and the layout for the r…

Is there a functionality within FMP that will replace misspelled words, or do I have to write a script for that? Thank you, Fed

Hi Guys, I don't know if this has been discussed before; I always try to search before posting, but I 've not been able to locate anything that would substitute the feedback in the forums, so here it goes: What are the best practises for indexing fields? Indexing makes for slower responses and larger files, but since my database is comparatively small, and size is not an issue, I 'd like to know how many fields I should be indexing, and what the rest of you are doing wrt to it. As an aside I 'd like to ask why the minimal indexing option is greyed out in my user interface.

Hi, I have a database with 3 layouts. what i want to do is go from layout 1 to layout 2 or 3 and when i go back to layout 1 I am on the record i was viewing. Set Varaible [($Number; Value:Get (RecordNumber)] Go to Record/Request/Page [No dialog;$Number] This does not work! any ideas?

When I search a non-indexed number field for the number 2, it returns both the number 2 and 3. Any ideas? I am using FM12 with FMServer 12 on a windows 7 PC.

Greetings~ Wonder if someone can help me with this one? I am having a navigation problem. Basically, trying to find a related record from one related file to another. The related record I want to find is in another file, within a tab, and a specific record. What I have so far in this script is: Open File ("CAD Work Orders") <-- works ok, this is the related file I want to do the find in, it opens and script stops here Go to Object {Object Name: "WORequirements:ProductionTab"} <---this is the layout, WORequirements, (in the other file) and tab name ProductionTab Go to Related Record (Show only related records; From table: "PartsTracker";…

I have a search layout with many fields that may be used for search. I need to add search for the price range. I.e. I have a field named "price" and if I switch to find mode and enter smth like: 100...150 in there it works. However I need to make it more user-friendly: add 2 fields to search layout with price range: FROM and TO. I tried to make smth like: Enter Find Mode [] Set Field [db::price; db::start_price & "..." & db::end_price] Perform Find [] However it does not work. How can I script this properly?

I have a search layout and 1 field in it. Also I have 2 tables. I want to enter some data to the field and then script which will look through the specified fields in both 2 tables to find this data. After this I need to get the results (all the records that were found) on one layout. How can I do this? I.e. the results should look like: table 1: record 2, record, 3, record 10. table 2: record 3, record 15, record 16.

FileMaker Noob here. I need to create a customer call list from a couple tables in my filemaker db. I have my main customer information table which has all land owners and tenants in it. The way our customer billing system is set up, a piece of property has a location number but can have two different accounts tied to it. So, my table includes that location number, but can have 2 records under with that same number. I have another table that I create records in from a .csv file that is exported from another system in the company. It exports that location number. I import that .csv into my database and link my two tables with that location number. What i want to d…

I'm working on a fairly complex database. I need to find all persons that have attended conferences to extract their address data for mailouts. The issue is that in the People file I search via a related field drop down, value 'conference attendee' (relationship from the Delegates file) and the results displayed and what is exported are not accurate. The database contains 30K records, it returns 14K attendees on the search, but about 300 of these are not attendees but some other item on the dropdown. Then when I export the data, it exports 19K records. The extra 5K records are blanks! Any idea what is going on here?

Hello Folks, I have this very old database, 10 years old. It is 120Mb big, and runs today on Filemaker 10 in an old G5 computer. The DB contains invoices, customers, products, shipping companies, etc... When searching the sentence "Rio de Janeiro" in invoices, it shows me the correct result, hundreds of invoices. When searching "Rio de Janeiro" in Table "customers", shows me one result only (wrong, there are hundreds of customers under Rio de Janeiro). It seems that the search stops at the first record found and returns me only this record. The problem repeat for all cells, like customer name, etc... Can you please comment, What coul…

hello, I'm Dedy from Indonesia, Jakarta I using FM pro11 for Video ID datebase. First I have 9 Tables and many fields how to set if i want to seaching the Video ID in many tables?.... and i want to seaching or find start date to end date. thanks regards dedy

Hi I couldn't find a forum that relates specifically to sorting so am posting here. I need to sort URL's, but some of them are very long, so I split them into 100 char chunks, and then do a sort on up to four of these chunks. It works, but it's slow! What I want to do is to somehow uniquely encode the whole URL into a number or some other representation that correctly and uniquely allows me to sort on a single field with the encoded value within it. Code() could work, if I add up the 5 char Unicodes it produces for each char of the url left to right maybe, but there is a chance that in doing this I will not end up with guaranteed unique values? Any ide…

Is it possible to turn off the magnifying glass in Find mode in FMP 12? Unchecking "Show field frames when record is active" doesn't remove the magnifying glass like it does in FMP 11.

I have a solution, which I would prefer to not display the toolbar (because it looks nicer, restricts functionality, etc.). I have a button to enter search mode, where the user can then enter their find request. Once here there is also a button to create an additional request so that is not an issue. My issue is that if the new request is to be an omit, how would one do that? In the toolbar there is the option to include/omit, but obviously as I am not wanting to show the toolbar this function is not there. Is this possible without using the toolbar? I can't find anything to change the request type.

I am sure this is a structural issue on my part, so if that is where the changes need to be, I'll start with that. My solution involves checking out equipment to students. I created a holidays table with all global fields. To make it easier for the operator, I created a separate field for each known national holiday (approx 7) and a popup calendar, so they can set the values. I also created fields (8) for the start and end of each term and last, there are (5) custom dates fields, again each with popup calendars- Is there a better way? I was able to create calculations fields and scripted finds during the checkout process to set due dates, so they don't conflict wi…

My solution involves assets to checkout to students. One portion is to allow the students to reserve items. My tables: Students, Assets, Transactions, LineItems and a Utility table. This is where I need some guidance. Within the LineItems table I have severval date calculation fields based on the length an asset can be checked out, if we are open weekends, school holidays, breaks, etc. As a students attempts to checkout or reserve an item, I need to check and see if the item is reserved (or it is a holiday or break etc). I had been doing this by creating a new record first (so it will assign a due date) then doing a find against that - and delete the record if…

Hi all I hope this is not a really dumb question since I cannot find a definitive answer anywhere I am trying to create a retrieve password option from a web page. And since I am a beginner at FileMaker this is really confusing compared to how I would do this ftom php /SQL. BTW my code for adding,searching and retrieving records from PHP is working so I know I have connections to the database. Basically I would like to $newPerformScript = $fm> newPerformScriptCommand($layout_name, $script_name, $parameter); $result = $newPerformScript->execute With $parameter = to the users email address And $script_name = to the script on the fm database …

I am trying achieve two things: 1. Either use a drop-down or pop-up menu on a 'Client' layout that allows users, particularly for Filemaker Go, to select a client and then populates the other fields on the 'Client' layout. This info includes address etc. The layout also has a portal attached to it which lists staff contacts for that client. 2. I have been searching through the internet and forums for a solution to write a script for a button that can turn on/off the Browse mode field entry box. It would be preferable to have an edit button on the layouts rather than changes being allowed when viewing a record Do solutions for these issues revolve aroun…

Source: I have a database of zip codes. Trying to Achieve I will have at text file of 12 zip codes that need to be found. Current Method: I click on find, enter one zip code, do a cntl-n, enter another zip code, cntrl-n, enter another zipcode... until done. Problem: This works but can be very time consuming. Today I had enough: Today, I had 35 zip code. 20 minutes later, I decided there has to be a faster way. Not sure where to start: I have been thinking about this, but not sure how to begin. Do I do a script, relationship, global, etc. Data: I have attached the Filemaker data and my text file of search data for reference.{see zip file--zipData.fp7 and…

Is this a bad idea? i can't seem to get it to work.

I made 3 text objects that perform a script with the "Go to record" script step. The "Prev Record" text use the "Go to record [Previous]" script step, and the "Next Record" text use "Go to record [Next]" script step. I need to make that the input field below "Go to Record #" text, can take you to the record when you type a number and click enter. The reason i need it is because the users should not see the Status Area. For example, you type 5, click enter, and this take you to record #5 I used this script: "Go to record [Get (RecordNumber)]", but this comes with a dialog, i dont need it, and if i choose "no dialog" doesn't works. What can I do ?

Hi, I'm new to the forum so apologies if I have put this question in the wrong section. What I am attempting to do: I want to create a 'home page' that each consultant sees when the database opens on an iPad. The home page should have two pop-up menus, Client and the other Contact. For context, each Client with have multiple Contacts (generally employees and contractors) The consultants in the office want to be able to navigate to a Client they select from the pop-up menu on the 'home page'. The same process applies to the Contact pop-up menu. Many thanks if anyone has ideas how to achieve this.

Hi all, forgive me if this is in the wrong place. I could not easily figure out where to post, apologies in advance. I was wondering if there is a way to have a records sorted in order of "date/time" last viewed. The idea being to have a list which the user can easily see records they were last dealing with and to use that list as hot links to those records. An example of this functionality is how Microsoft word gives you a quick find list of recently created documents. Kindest regards Ozziggy

I am trying to use the Get (FoundCount) function - I have defined a field with that function, but instead of returning the found count it returns the total records count. Anybody know why?

Using Filemaker 11 on Windows, writing a script to run a report. Script is working if I only search for a single group or all groups along with either one, two or all three of the types. How can I extend the find to allow the User to search for either 2 or three Groups? There will be the same Types selected for each group. I have captured the list of Groups as a varialbe with each value separated by a <cr>. thanks, Jim

Hey there, New to the forums. I'm looking forward to learning from all of you. Now to the issue: I'm trying to sort records based on an Account Name within a layout. For example, my current layout: Notice the multiples. I would like to change it so Account Name is displayed only once. Multiple records would be shown on the next page. (so after 1 year, I won't have to scroll through a thousand records) Sort of like this: Or this: Adam May 18, 2013 May 19, 2013 Admin May 19, 2013 May 23, 2013 test bot April 20, 2013 April 21, 2013 May 19, 2013 ..etc Haven't had any luck so far. Please let me know if you have any suggestions…

There is probably a simple solution for this, but I can't seem to find it. I have a field called "category" and a field called "editing." I want to find all records that: Do not have a value in the "editing" field. (The "editing" field is empty/null) Do not have a value in the "category field OR the category field matches a variable $curCategory In a script, I am calling a "Perform find" to do this. Right now I am using an "Omit records" with "editing = *", and am trying to figure out how to code the rest of the logic. I know that one can use just "=" to find empty records, but for some reason, this is not finding all the records that have no values. With any …

Hello, I can't seem to figure this out, and I can imagine the solution is quite simple, but... The database is for an Events Mangement Company; they have many records of Events. Each Event is designated a status (Received, Confirmed, Declined, Invoiced, Closed, etc...) Sometimes I need to display Event records that are match one or more statuses. I wish to use a Checkbox option so that the user can click/unclick the desired statuses. So if the user clicks "Received", "Confirmed", "Invoiced" than the user will see all the Records that have one of these statuses. Any guidance would be much appreciated. Thank you.

I have several inspectors who inspect residential septic systems three times a year. I need to find the property ids for the inspections where the property id is the same, the date is greater than 9/30/12, but the inspector is different in the two inspections that will show up for each property id. I can import, export, add fields or whatever I need to do. I am the administrator of the database so anything goes. Thanks!

I have a script that searches the "set" field, that works properly. The script is this: Show Custom Dialog ["Search Cards"; "Input the search criteria you want!"; Magic::Quickfind] Perform Quick Find [Magic::Quickfind] Set Field[Magic::Quickfind] If I put in a name of a deck it finds every card in the deck. Then I have another button after I am in the found set to search by color. This is were the problem lies. It will take the color that is already showing on the record in view and use that and it will work. But if I put another color in, it starts showing me all the records that I have in the entire database. I have 23,106 records so many times the count gives me up …

Hi all I'm having some trouble here. I'm trying to get Find and Replace to search for blank fields or positive values within an "order" field, within a found set, and replace those values with "0". Find and Replace is running within a script, and I keep getting the error: "The Find/Replace Option "Current Field" requires a selected field." Before the Find/Replace is run, I have the script "GoTo Field" called "order". However it's not working. How am I supposed to tell the script what field to Find/Replace in, and what is the calculation that would go in the "Find What" field to find blank or positive values? Thank you!

I am relatively new to Filemaker so please help. I have a Filmaker solution that imports data regularly from various SQL sources. At certain times I require to delete all imported data in the Filemaker solution and then re-import. The Filemaker solution is hosted on an FM Server and contains no forms, only tables (These tables are accessed via various other FM solutions). The import is handled via ODBC and is scheduled using the schedule function on the server. All this works fine. When I require to delete the imported data, I use the 'Delete All Records' function in the script proceeded with the 'Perform Find' function as follows: Perform Find [Restore] Del…

Hey guys. I have a 'general inventory' table which my system uses as it's primary source of information (we are a rental company.) When we don't have an item, we subrent it from another rental source. Currently when we receive a subrented item it is added to a separate table called 'subrented inventory' when a new subrental order is created - however this currently causes an issue with our staff looking up inventory in general as they're entered in to two different tables. I'm in the process of migrating this table into the 'general inventory' table so finding assets is easier, and a 'vendor' field will define if the item is ours or another companies. This presen…

Hello everyone. I'm getting back into FileMaker development after a long hiatus. I'm currently developing the Search screens for my solution and am scratching my head over something I think should be obvious. Either I'm missing something obvious or what I want can't be done. I have a Projects table, a Users table, and a User Roles table. User Roles is a join table. I have a portal on my Project view that shows User Roles. After the user creates a related User Roles record for the project, he or she picks a User from a pop-up menu and a Role from another pop-up menu. Simple enough. There can be an unlimited number of roles and users can be assigned multiple rol…

I have a field with unique values which are our job numbers. When I search for a value 12101206 in a field called jobNumber, my result brings up 12081707 and 12101206 in my found set. If I do a search for 12081707 is says no records found. The field options are as follows... Indexed, Auto-enter Data, Can't Modified Auto, Always Validate I don't even know what I should ask here. When I perform the search it is bringing up a record with a different value then what I searched for. Any ideas?

Hello I have two tables, "investors" and "deals". Investors have a set of criteria that they want - entered as a checkbox Property Deals have a set of criteria that they meet - also entered as a checkbox I would like to be able to go into a deal record, input the criteria and then run a script that finds any investor who have at least one criteria matching one of the criteria checked for the deal. The two value lists are the same. More criteria will be added so I would like to not hard code the find criteria. I guess I need to do something like (pseudocode) Copy the fields checked into an array or list variable Go to investors form Enter find mo…

If I am in my main CMR layout referencing CMR file Then if open popup New window.. reference to same CMR file Here I have several Find & Filtering routines, operator can select for showing CMR file .. that find seems to works find viewing top find/scroll records THEN.. close pop up... Back to Main , showing all records.. that's normal Can i some how re use that same FIND Found count,,, last Find to same set of records Thanks

Hi All, I created my mega data base and now I want to change something that requires me to remove a field I created. Can I do a find or something to see where and if this field is currently being used in any other formulas and scripts? Or do I manually have to search everything and hope I catch it? Any help would be appreciated. Thanks in advance, -AL

I am not sure where to put this question. It has more to do with sorting than finding. I have a list of tasks for my cabinet shop. These are events that show up on an ad hoc basis that we need to remember to take care of. The priority for each task may change daily but the task remains on the list until it is complete. I use two fields for this: TASK DESCRIPTION & PRIORITY. The priorities are basically: Do this 1st >> Do this 2nd>> Do this 3rd etc The problem I am having is in my sort. I want to see the list to sort with Priority 1 > Priority 2 > Priority 3 tasks at the top and unassigned priority tasks at the bottom. Rig…

OK, I have a calculation that allows/disallows access to records in Table A of File A. Table B in File B has a relationship to Table A. Based on the criteria being used in Table A of File A, how can I extend the Record Level Access protection to Table B in File B - because they can't see the values in Table A due to Record Level Access protection. Is there a way to say 'If you can't see the related Record(s) in Table A, no access"??

 I am using FM 12 03 Advanced  I have the script above. it is supposed to produce a report that shows all the DECEASED (DOP) members Omitting the deceased members whose spouses are also deceased.  Leaving out the OMIT RECORDS second part of the FIND works. But, when I add the Omit .... part it fails.  DOP is a date field that holds the deceased member's date of passing.  I also have a SpouseDeceased field that holds "Yes" if a checkbox is checked.  The script above seems like it should work. But, of course it doesn't.  What am I doing wrong?  Thank you  Ron Â

Hello , i have 2 tables one inventory and one invoices . i would like to know how get on the inventory table the new totals after updating with the invoice table ? for example : i create a new product on inventory REF: 2020 with quantities by size SIZE1: 10 SIZE2: 10 SIZE3: 10 TOTAL: 30 now i go to invoice and create a new invoice selling REF: 2020 SIZE1: 2 SIZE2: 2 SIZE3: 2 TOTAL SALE: 6 now i want a field on the inventory table that discount those 6 pcs from the total of 30 .... telling me that i only have 24 pcs in stock . here i attached you a sample file . i really appreciate you help , Thank you very much …

So I imported a table with a ton of records ID numbered starting with 1 and incrementing 1 at a time. I screwed somethign up when i imported the related records, and I just need to go delete all the ID numbers in the new table, and start over so record 1 ID number is 1, record 2 ID number is 2, etc. I know there is a quick way to do it in table view, but I can't remember... Thanks, any help would be greatly apprecaited.

Hi Is there a way to set which field the cursor drops into when pressing the standard find button on the status bar? There is a field on my layout that i search in 90% of the time but it is not the first field on the layout so when I press find I have to keep clicking in my search field box? Thanks Mark

I currently have a list of invoices with customer's names (eg John, Eric,etc), i would like to make a "calculation" that can mark it as the customer's first invoice. Lets say John has invoices Invoice No 12 issued on 5/Jan/2013 Invoice No 28 issued on 10/Jan/2013 Invoice No 32 issued on 2/Feb/2013 and Eric has invoices Invoice No 18 issued on 8/Jan/2013 Invoice No 25 issued on 17/Jan/2013 Invoice No 40 issued on 5/Feb/2013 What would be the best way to mark Invoice 12 and 18 as "new"?? using "Summary Field" using Minimum but this only works for current found sets. So any ideas would be appreciated! Thanks.

A Curious thing I have recently noted while using Filemaker 11 Advanced: 1. I enter a question mark (?) into a text field. 2. I then enter the "Find" mode. 3. I then enter a question mark (?) into the text field and execute the search for the question mark. The result, I get a Filemaker intercept telling me "No records match this search criteria". 4. However, If I enter a question mark surrounded by quotes ("?"), the search is successful. Is this a known problem (to others) for which I need a work around for scripting purposes?

One of my users pointed this out to me, that if you do a range search on a date fromatted field (does not work on text formatted) and leave the year blank, it will search only the current year..! Could not find this documented anywhere. just an FYI..

Hi folks, I'm planning to create a button on an existing layout that will take the user to a new window (all on FM Go) where they can enter a date range to search for invoices. I.e. Set a start date, set an end date, then show a list of all invoices in that date range. Still a relative noob, but particularly to setting up custom searches. Firstly, I'm after ideas on the most efficient way to step this through, and of course details on the steps themselves. No experience with calculations as yet. TIA

Hard to think of a good title for this. I have a table with Agents and Referrals. The data shows in subsummary parts which are sorted by Agent. It's easy to get a count when Agent = Referral. However, is it possible to get a count of Referrals over the whole found set when sorted by Agent? For example, Smith was the Agent for 10 records. Smith was also the Referral for 8 records, 3 where Smith was the Agent and 5 where Jones was the agent. On the subsummary part, sorted by Agent, I can get the 3 for when Smith is the Agent and Referral. How can I put on that same part that Smith is the Refferal for 8 records in the found set? I was thinking of may…

Seems like I used to know how to do this... And I'm sure it's been answered, but I can't seem to come up with the right search string. I've got a table w/12,000 records in it.Ton of fields, several unstored calcs. It sorts rather slowly using the Sort command. Solution, I thought: set up a self-join TO with the sort set up in the relationship. GTRR to the TO. It does seem to be quicker. (The self join is by a numeric field crossproduct relationship) But... it doesn't retain it's current location in the found set. That is, I want to sort with record X selected, and after sorting, still be at record X. How to do that?

Novice needing help. A lot of my text fields contain lengthy text blocks. Is there some way to execute word-processor-like searches WITHIN a text field, resulting in highlighted occurrences of the string, or some other means of quick identification? In case clarification is needed: Say, I want to see every occurrence of the string "elections" within my database. I start by doing a basic Find for "elections" in the appropriate field. Now, I have a found set. But, since my text blocks are very lengthy, it takes alot of eye-balling to find the the string "elections" within the entry as I browse each record in the found set. Ideally, I'd like each occurance of the string…

Inventory Database with fields : INVENTORY IMAGE, LOCATION and STATUS. The STATUS field has three options : AVAILABLE, SOLD and ARCHIVE. It is important to be able to sort and view Inventory Records by LOCATION. My goal is to be able to Hide the Items marked ARCHIVE. Which can be done with a simple script "Perform Find & Omit Multiple records". My problem is that when ever Inventory is sorted or viewed by LOCATION, the OMITED records (marked "archive") reappear. Any suggestions ?

I have a table of 200 clients related to a table of each client's visits. The visit table has the date of the visit plus other fields not involved here. These monthly visits numbering 1000 range across all visit records from 2003 through 2013. I put the visits in a portal on the client layout. It all works fine. What I'd like to do is show only those records that have visited recently. The business rule is: If the client hasn't visited since Jan 2008, he's abandoned us and is not longer interested. Of course I'd not delete those abandoned cases but I want FM 11 to show me only those records with a visit from Jan 2008 to now. I've tried doing this with the simple FM …

I want a script that will allow me to produce a specific field criteria within a user defined date range. I'm getting hung up... any suggestions would be greatly appreciated. Below is a sample of the script: If[isEmpty(TableName::gDateStart)] Show Custom Dialog ["Start Date must have a value. Please enter"] Exit Script [] End If If[isEmpty(TableName::gDateEnd)] Show Custom Dialog ["End Date must have a value. Please enter"] Exit Script [] If[TableName::gDateEnd < TableName::gDateStart)] Show Custom Dialog ["InvalidEndDate"; "End Date cannot occur before Start Date. Please reenter"] Exit Script [] End If Go to Layout [LayoutName] Enter Find Mode […

I'm attempting to create a "results" panel on one of my forms which is displayed after a find is performed. Getting the foundcount was easy, but I want to also display the foundcount of unique values in the found set. I have a damaged field - the field either contains a "Damaged" or "NotDamaged" value. I want to return the results of each. I have created two fields - _found_count_damaged and _found_count_notdamaged. I'm struggling to find the correct formula to display these results accurately from the found set: Count( _damaged) = "damaged" always returns a result of 0. Can someone please point me in the right direction? I'm also attempting to display the omit…

Hello, I am creating a "Staff table that will help me schedule part-time staff. Each staff member has limited availability. Right now I have one field called "Availability" and I am populating with the Checkbox option with all possible shifts: "Sun Day"; "Sun Eve"; "Mon Day"; "Mon Eve"; etc.... When doing a search for the available staff on a certain shift, it seems to be working well. However, part of me is wondering whether to have a separate field for each shift with a "yes" checkbox option. I don't have enough experience yet to see the inherent danger or benefits involved with the different methods. Can anyone enlighten me on the pros/cons? Or pe…

i currently have a script that opens a new window to allow the user to search for records. currently, upon the perform find script running, the user is taken to a layout in List mode that displays the returned results. I'm attempting to add a checkbox to my find window that the user must check to be taken to the other layout in List Mode, but if it isn't checked then the results are displayed on the original layout (in form mode) after the find window closes. the (global) field that contains the "show results in directory" check box is in a _globals table, however the checkbox isn't editable in find mode. when the text field isn't made a global field then it becomes …

hey all. i have an inventory table containing 15,000+ records with each record categorized by department in a "department" field. each department has a separate layout which corresponds to the needs of that particular department. depending on what layout the user is on, only the records tagged for that department need be displayed and/or be searched. i don't want records from different departments appearing on the wrong department layout (i hope you can follow me so far). i understand this will probably involve a script that searches and omits based on the department tag for each inventory record, however it's imperative that the records not tagged for that particular…

Hi, I am trying to solve a problem in filemaker 11. I have a database that has multiple orders from customers. However, the product ordered can be repeated multiple times across the customer orders. Herein lies my problem. I can have two or three users, each with a pile of product that can have the same title, but is for different customers orders. They run a script which allows them to scan the product barcode and it searches the database for the first outstanding customer order matching the barcode. Problem being, that if two users have the same product and happen to run the script at the same time, its possible that they both find the same customer order…

I have an Inventory table where each item has an Asset ID - a number which either begins with HDCR or RAD. Sometimes we rent items from other vendors which we call Sub Vendors. When a user scans the barcode of a Sub Vendors' Asset (which wont be prefixed by HDCR or RAD) in to the Asset ID field in Find Mode, I want FileMaker to prompt that it isn't an item that we own, and ask if they would like to add it as a new item to the Sub Vendor table. Basically, is it possible for FileMaker to reference the first part of the Asset ID to see if the item is ours or not? A typical Asset ID would be RAD10110 or HDCR56774, etc.

Hi guys,  how can i implement google image search type functionality.  Ex.: I have a table named, Product, where I have 1000 of records having product image and its description.  when i type in search box and click on search button. the images related to search should be displayed as a result  shown in google image search. Suppose when i type red mercedes, then the result should be displayed  as such in image attached:  Please help me how to execute this in my layout.  Thanks in advance.

A bizarre bug has appeared in one of our databases. When doing a find on emails (to correct or update them), the find consistently produces no matches unless the "@" symbol is removed. For example, say the email address "[email protected]" bounces so a search is made for that email address so it can be corrected or flagged as bad. The field for email addresses is a simple text field, nothing fancy. If a search is made in the email field on "[email protected]" it will find no matches. Unless the "@" symbol is removed no matches will be found. But "fred" or "fred emails.com" will return a correct match. I realize that the symbol "@" is a search operator, but I don't recall …

Hi Guys, We have a layout on FileMaker where we can search for all sales between a specific date range... What we want to do is click a button and it loads another layout with a pie chart on, showing all the sales group by a source (e.g "Google")... Is this possible? Thanks, Scott

Hi All, I have a DB that shows what a bidder has won at an auction. I show it in a portal on the Bidder Page (below). If it is a certificate, I have it checked as "Yes" on the catalog item on the catalog page. I am trying to figure out how I have a "Custom Dialog" box pop up if the field is Yes in the portal. I know how to do it on a page, but not when it shows in the portal. Any help would be greatly appreciated. MG

Hello, I am new in filemaker. I have make a database to save some guitars informations and I would like to search how many guitars I have saved in January, February etc... At this time if I search between 1/1/2013 - 1/31/2013 then Filemaker told me: No records match this find criteria but I have saved 8 guitars at January 2013. How can I solve this problem? I have uploaded my database to dropbox if you want to see the problem https://www.dropbox.com/s/fo6f8qcbfq0b1e4/Projects.fmp12. Sorry for my English, it's no good. Thank you in advance

Ive got a db with two tables... table "information" with a bunch of data entry fields and an autoenter modification timestamp table "exports" that records when an export script has run. export fields pk, exporttimestamp, exportedrecords, and summary max (exporttimestamp) information is related to exports with and X type join on two timestamp fields. (yes i know i dont need this with the go to layout steps) The issue is with the export script where ... go to export table TO. show all records set var $lov = summary field (2/2/2013 4:55:11 PM) go to information table TO for find and export enter find mode set field modification timestamp "<" & $l…

Hi all, I need a logic how to implement a scenario. Scenario: There are multiple products with product id and image. There is a project section where i use products in it. When i click on add product button, i get to see all the list of products that are unassigned. Suppose, a product, A, has been assigned in a project, X, for a period between 10/2/2013 to 15/2/2013. Since this product is busy for this period, now when i will create a new project for 16/2/2012 to 20/2/2013. Then, this product needs to be shown to add product in this project. So, can you help me how to implement this scenario. It's urgent. Thanks in advance.

Hi Everyone We have set of records in name field. like Jack, mini, jack, janson, dallas, robin, rack, robin.... totally 20 records. I need a script or find options to disply the unique values, that means i dont want to see the duplicate records like jack,robin I think we have option to omit this duplicate records, Please help you with your intelligence

hi everyone, Â Â Â Â Â Â Â Â Â Â I want to add features of spotlight in filemaker,to search the records in all the tables of particular field of a filemaker file.I want the spotlight as MAC.

There is possibly a simple and obvious answer to this but I am stuck! Is there a way of retaining the focus of the current record after performing a new find? In my example there is a script which displays a subset of the original found set (in a separate layout but the same window). Having scrolled through the subset to find a specific record I want users to return to the original set, but to stay focussed on the desired record. I thought I could use the 'go to record by calculation' script step but the resulting dialogue appears to want a sequential record number from within the found set, not a unique number such as the RecordID. Thanks for your help.

I assign records to a user using a loop and set field to put their name into the Assigned _ to field. This works OK. I then do a Find for unassigned records using "=" in the assigned _to field and use Get(FoundCount) to display the number of unassigned records. Initially this works fine. If I go into the database and do a find for a user in the assigned_to field it works fine, but when running the script to show the number of unassigned records it always adds one or takes one from another source. Any ideas? Many thanks.

The ! operator in find mode identifies duplicates in the entire table, regardless of using "constrain found set" in find mode. Is it possible to limit its scope to the found set? Or achieve the same with a simple work around? thanks

Hi, I would like to create groups of records based on a specified search criteria of the contacts files we have... I would then like to keep this "Group" as a named resource that can be used for marketing….. The contacts files have various unique defined fields that would be the search criteria. I have another table for the resulting "Marketing Group" I am wondering where would you put the group identifier… in he contact record or put the contact record identifier in the "Marketing Group" Table... we need to see from the contact which "Marketing Group's" it belongs to and from the "Marketing Group" which contacts belong to the group... we will also want to collate …

Hi Everyone: Is it possible to link a find request with a layout? I would select a layout and when I did, a find request would be triggered?

Hi, I'm new to FMP and I have a list of records that include: Territory, CallDate and other record-specific detals (products, etc.). I need to find out how many calls each territory averages in a given date range. I have a calculation to yield the business (work) days for a range, which would then be used as a divisor for my calculation: #Calls (= #territory-specific records)/number of WorkDays = Call average While I have been able to get some sub-summary type fields in one layout (trailing summary with a resetting count for each territory) I can't find how to include that territory-specific count in my calculation ... It keeps using the grand total only ... ? …

Any idea why 0.00 is greater than 0 when it is in a Text field? When I do a Replace Field Contents If ( Table::f2 > 0 ; something happens ) and Table::f2 is a Text field, it evaluates as greater than 0 and "something happens". Why?

Hello, I would like to improve my database by having a script that will search within a specific field for records that match data entered by the user in a dialog box. Most of them will be date related, e.g. search on "8/2012." I have found a lot of material on this question, but much of the advice makes assumptions the user has a higher level of knowledge than I do. I need an explicit answer. I do make buttons and write some simple script for them, that is my level. 1- User clicks on button, starts script. (I have that figured out, it's after this I need help with.) 2- Dialog box opens and asks "Enter month to search on, e.g. 8/2012." 3- Find request is perfor…

I am creating a dynamic Sales Report Generator. This will allow someone to come to the layout and select whether they wish to view 1st Quarter or just January Sales. I want it to default to the current year, unless it is in the quarter(s) or month(s) prior, then it will default to the prior year. My problem is when searching via a date field, you cannot do this: "01/01/year(Get(CurrentDate))...01/31/year(Get(CurrentDate))" nor "01/01/year(Get(CurrentDate))-1...03/31/year(Get(CurrentDate))-1" Is this possible? How I am accomplishing this is creating a script that looks at a field and then the OnObjectModify applied script runs whenever I select one of the…

Hello, Is it possible to script a find that specifies "omit records if the numerical value of field A is less than the numerical value of field B"? I can get conditional formatting to highlight a field when this is the case, but can't seem to get a script to work for this find. Sorry if this is in the wrong category--it's kind of a scripting and find problem in one. Thank you!

What is the easiest way to perform a search for all members of a value list?

Hi, I have a field "class" on my DB which gives a choice of values based on a value list in the form of a dropdown list say A,B,C,D,E I would like to add a button with a dropdown, the user selects "A" from the list and FMP gives him only the records which have "A" chosen in the dropdown list related to the corresponding value list taken from the field "class". Would that be possible? Would that involve heavy scripting? How could I achieve that? Thanks in advance for the help.

Planning to have a 'home' screen where the user can search for clients, then go to the relevant record. Options so far that I can think of; 1. Quick search field of surname, then tap a button to go to the record. 2. Have a surname field that when tapped, opens a drop-down list of all surnames, from which user scrolls though then selects and is taken to relevant record. A contacts list would be around 20-40 entries typically, but should allow for up to 200-300. (Effect on size of drop down list?) 99% of the time, the surname will be unique, however may have to consider the possibility of more than one. Perhaps if there is more than one, have a pop-up window…

Hi everybody: I have tried for find 2 separate words or letter strings in a record, but so far I can't figure out how to do it. I have many product codes by color in 2 colored items. For example "red23blue47"; same colors with different hues could be "red78blue55" How can I find all the red and blue items, no matter the hue (indicated by a number). I have no problem finding all the red or all the blue Ctrl+F *red* or Ctrl+F *blue* I want to be able to find the red##blue## I have tried everything I can think of: Ctrl+F *red*blue* Ctrl+F *red* Ctrl+N *blue* But they don't give the results. Any help is welcome, Thanks Carlos

I know I can use $Varibales to export selected records from a DB But I woud also keep the various layouts and referenced files A way to create as script which only copies the records I have selected? This would be the process 1st select certain records in a MAC DB 2nd Save a copy of the DB using one or more fields as the new name of the DB? So far the Save a copy as $Variable name stil saves every single record I could delete the itted ones but this is very long ... Regards

Hi, I have an odd problem. I've imported about 1.1 million images into filemaker pro. I've got the main image on the left and the thumbnail image on the right and they are in container fields. Here's what it looks like: http://screencast.com/t/nZ2CWMK749H As you can see there are two examples of the "damaged file" showing up as "blank white boxes" rather than as images of a book cover. Actually when I right click on a "good" image in the container I can "export" it. When I right click on the "damaged" file, I don't get the "export" choice, so perhaps this means there is no "damaged" file there and that it's just a blank container? Right now I am…

Hi Board! I have two tables, one that I have produced a found set from, which has 312 records. This is 'Table 1' I have another table ('Table 2') that just contains one field, which is also a field in Table 1. It has 65 records. I want to delete, from Table 1, any record that ISN'T in Table 2. That would leave me with 65 records in Table 1, having deleted 247. What's the process? I've got as far as having the 312 in a found set, and a table with 65 records, some of which match those in 312, but no relationships or anything like that. Thanks for your help and information! Thank you, FMH!

Hey guys, forgive me if this question is dumb. I have two tables and each with unique fields. I wanted to grab a record from Table2 into Table1. I have been looking up the GetNRecord function and after few hours of banging my head. I still cant get it to work. I am totally new to FM and not sure if i am interpreting it correctly on this function. edit: to better elaborate, I have a price sheets: (see attached) and I wanted to create another table, when entered a quantity and number of color, the correct price will show up? Any way to achieve this would be great help.