Calculation Engine (Define Fields)

I am using the bps_SE templates and have a challenge on the service order page. I am trying to populate text in the notes field based on the customer invoiced. How can I use cas statements to populate certain text based on the customer that gets billed? case "abc company" "put this in the notes field" case "xyz customer" "put that in the field instead" any ideas?

I have been tasked with analyzing approximately 50 FMP databases running on FMP Server 9 (not Advanced). I have FMP client 9 on my local computer. I do not have a lot of experience with FM, but I have experience with SQL and relational databases. I am trying to pull the data field names, data types and comments for the databases into a spreadsheet. I have discovered that I can print the field information, but I don't see an option to export it as a CSV for import into Excel. I did find a database that was a data dictionary "proof of concept" in these forums, but I am not certain how I would use that to get the data I need. Is there a simple query that I can …

I am attempting to create a field that produces percentages by comparing the content of two other fields. I imagine I need to create a script but am unaware how to utilise the correct information. Kind regards

This threw me for a loop. I have a simple substitute calculation. I want to exchange the HTML %20 in a text string with a space. I have escaped the "%" with a backslash and that works fine... but when I substitute "\%20" with " ", the "20" part doesn't substitute. I get the following result: ...something like abcde\%20fghi turns into abcde 20fghi. I want it to become abcde fghi. (If it matters, this is Filemaker Pro 10 Advanced.) I don't get it. It's a text string. It should treat the numbers the same as any other text shouldn't it and remove them... or am I missing something obvious?

This is a follow on to a question previously asked and answered: A calc field that uses Sum (relationship::numberfield) will total the related records. That is, the records included in the total depend upon the relationship. If the relationship, for example, is Client to Invoices by ClientID, then it'll only include the total of the Invoices for that client. I have 3 tables: 1) Visits (made/month); 2) VisitDetailLines; 3) Businesses. The VisitDetailLines Table is a child table of both the other two tables. There is a field in the VisitDetailLines Table (YesNo) that either contains a 1 or 0. My goal is to be able to total the number …

I have set up the following fileds: ITM_item::c_UniqueValue_Category | Calculation | Min ( _ID ) = itm_ITEM__selfjoin_Category::_ID ITM_Item::s_UniqueValue_SUM_Category | Summary | Total of c_UniqueValue_Category I am using the result of ITM_Item::s_UniqueValue_SUM_Category to allow a script to navigate to the correct subsummary report layout. Where there are there are ≤ 3 unique occurrences in the field Category the script will navigate to the Item detail report, else navigate to the listing with Category & SubCategory Items to further drill down by SubCategory. This is in a separation model with an interface and data file. The trouble is th…

Is it possible to limit the number of new records that are created on a layout before the complete layout MUST be cleared? I need only a maximum of 14 new records in rows on the layout and I have information listed in the header and footer that stays the same for all 14(maximum) records, however, I don't want the user to be able to create 15 records without having to generate a new document (requisition). Any ideas?

Hi I'm new to filemaker and I don't know if this topic has been addressed here or not. If so could you just link me to the thread, thanks. My issue is as follows, I have Multiple rows of one order in Excel, each with repeating and or different info in each cell. I want to combine all order rows with the same order# into one record in filemaker. I figured out how to combine the data into one order, but because the product info is repeated everytime info changes I just want to show all unique info once. I have an excel formula =IF(AA2=AA1,"",AA2) AA1= PROD_CODE AA2= PROD_CODE How do I make that into a calculation in Filemaker? Thanks for any help.

Is there a way to use the content of a text field (field names typed in as well as & "text" strings) and place it to be used AS THE calculation to generate a text result? I have a calc field that generates a text string of various fields and some added punctuation and spaces. The user grabs this text to paste in another application. The thing is that this formula may change over time. And instead of having the client "open up" the database to change the calculation, I thought I could create a layout where they might be able to edit a text field which then is used as the calculation. I would provide them with the field names and syntax as well as instructions …

Long time lurker. Always found answers before by search. Can not this one. If in wrong place my forgive and tell me where to take it. File with reservation records, start date and end date. Find group of records and export to chart program. Program colors duration per record. It has 10 columns meaning 10 years with 12 boxes each column meaning months for 120 boxes total. I need to change dates to right numbers in this grid. Here is stuck. Samples are ResID 24 start 3/15/2011 end 9/22/2011 ResID 66 start 5/19/2011 end 2/14/2012 Start of chart is oldest year of records so 201100. this must be first line of export not my idea and it is done easy. All …

Looking for a way to do this. Here’s the simple explanation. F1- One field has a 5 letter code (‘DW009’) F2 -Another field has a block of text (“Please delete ‘AB009’ and then quit”) that is the same on every record There is a code in the block of text(‘AB009’) that I want to replace with the code from each of the F1 fields. Couple of things. There are 150 records to change. The block of text is much bigger and is actually a SQL query that needs to be specific to each code in field 1 (F1). I was thinking of a calculated Replace but it started getting ugly fast. The calc would be: Go get the code in F1 and replace this text “DW009” wi…

So I've just discovered the way global fields are handled when hosted on a server (if they filled out by a client, the values are only for that session and they disappear on database reload). I've seen a couple of places that mention one of the work-arounds which I'd like to try. But I can't quite figure it out since the advice seems to be for much older versions of filemaker - is there a way to do the solution below in FM11? "One caveat: with FMServer 5.0v2, with the new feature that allows Defining Fields if you're the only guest, if you go in as a guest, and while you are the only guest you also open the Define Fields box, any global values you change during tha…

New to FM and a novice. I have a field tracking Defect Class, i.e. Critical, Major & Minor and another filed tracking # of finding. I want to sum these findings based on class. I tried If (class="critical", Sum (# of finding),0) but it didn't work. Will appreciate if someone can help.

I have summary and calculation fields that are on a layout that is in a different table than the summary and calculation fields are for. They work OK with the exception that you have to click in them to get them to update when information that they summarize or calculate is changed. Is there any way to make these fields update automatically when any of the fields that they summarize or calculate from are changed? Thanks

Hi, I am hoping someone can help with this seemingly simple formula. I need to know the exact midpoint of various given dates and times. Data is given as follows; start date 03/10/2011 @ 14:26 and end date 3/17/2011 @ 13:22 I can do this by crude formulas but I bet there's a simple way. No offense to FM power users but in Excel it's ridiculously simple. Thank you in advance for any help.

Hi All, if a Sum field is placed in a table showing information from a related table does it always show the sum of that field for all records in the table or can it show just the sum of the found set and if it can be configured to show just the found set, how?

Hi, I did a search on this forum for my problem but the only solution that seemed to come close to addressing it doesn't seem to be possible to implement in my version of FileMaker (9). Quite simply, I have a check box and I want a calculation that will cause another field to display the date (mm/dd/yyyy) when that particular check box was checked. I want the field with the date to be modifiable as well. Thank you for the help!

How can I set up a simple checkbox that will evaluate to true/false or Y/N ? And how do I find for that?

Anyone know how to create a calculation that could be inserted after a found set is sorted and find the most current date in the found set? I have a list of employees with their daily job charge entries listed. The report that is produced sorts the jobs and seperate disciplines and also summaries the total hours for each discipline, however, now I need to have the report show the last day an employee charged to this specific job and list that on the report in a field "Last Charge As Of".

Hi, I'm trying to figure out the correct calculation/summary setup for a report I'm working on. I have three related database files (separate files, since they've been in service since FM3): Work Orders, New Projects, and Timesheets. Work Orders is a db of tasks; New Projects is a db of projects, which has a portal for the Work Orders (tasks for each project); and Timesheets is where employees enter in the time spent on their projects. I'm currently using FM 9. In the New Projects db there is a calculation field "Total Hours" which is the total time for all tasks (Work Order records) in that project. The calculation is: Sum(${Work Orders (2001 up)*}::Hours) …

I've got start and end time fields and am trying to work out a way of seeing if another time field falls within the start / end duration ie start=9:00am, end=10am, LookupTime=9:32am is LookupTime inside or outside the interval? thanks

Does anyone know of a formula that can calculate how many syllables are in a given word? I am trying to recreate what this web page is doing: http://www.russellmcveigh.info/content/html/syllablecounter.php

How can I increase the distance between the letters in a field. Or do like in the adobe reader, specify the Number of characters, and it automatically aligns letters. FM Pro 11. Thank you.

Hi All, I want to put a unique identifier for a barcode on a new products after validating that they do not already exist. The fields are defined as: Product Active_Ingredient Manufacturer SU_size Pack_Qty When inputting a new product to the table these fields are completed and if they do not exist in exactly the same format then an auto enter takes place in the code field. Can anyone assist please

I am trying to create a cf that will calculate the 3rd Thursday, for example, of a month. I suppose it centers around DayOfWeek[Date] but it is the recursion that gets me stuck. Or, is there is there a simpler way? thanks :D

Hi! The field contains this amont in this format: 33.078.50 and I need to convert it to 33078.50 or 33,078.50 I tried Substitute ( amount; "."; "") but this gives me 3307850 Can someone give me a hand?

I have FM 11 advanced on OSX I produce a 'found set' of 'late' paying members. ok I want to 'extract', as a comma delimited string, all the found set email addresses and then copy them to the clipboard so the user can 'paste' them into his email program (whatever that is). What's the simplest way to do this? Thank you

Nested Case Statement I have a database that has three tournaments. You are allowed to drop the lowest tournament. If you miss the tournament it is counted as null. if you miss two tournaments you get the value of the one you bowled in as your total. i believe I should use a nested case statement as an if statement might get convoluted. please see attachment Book2.pdf

I have a parent table with a child table and I want to be able to select only one child record in the portal at a time. So when I select a record any other selected record will deselect I don't know if I need to do some auto-enter by calc on the select field or create some sort of different relationship. I'm not even sure if this is possible. Any thoughts? Thanks for any help Anthony Select Child Record.fp7.zip

Hello People! It´s good to be back to the forum I was looking around to see how can I random numbers from 1 to 20 in some fields and follow the classic random * ((maxnumber - minnumber)+1) and for sure I got the result the only problems is that I got a 0 (zero) and only need from 1 to 20 and for my surprise the formula is not giving me a 20 neither!!!!!!! How can I resolve this problem, maybe It must be very basic maths but.... any help would be very helpful Byeeee Sincerely BluiSh

Hi all, I've been trying to get information of a PDF file that was dragged and dropped on to a container field. I have tried creating TEXT calculations for filename : GetValue ( Container; 1 ) but it returns a "?" I have also tried GetAsText (Container) and GetValue(Container) both returns a "?" However when I insert the same pdf file using a script the file get imported but a preview is not shown. The file icon is shown on the container field and all the information such as the filename and filesize is accessible! I'd greatly appreciate it if someone could tell me of a way to obtain this information (filename & filesize) when the pdf file is dragged…

Dear All, I was wondering if it is possible to count how often a particular character (i.e. 'A') occurs in a string (i.e 'AGCTATGCA). In this example the answer should be 3 (obviously). Now doing this using a script is easy, but I can't figure out how to do it in a field calculation. What I really would like to do is to be able to enter a string (its a DNA sequence btw) in >field1< which should result in a auto-enter calculation in >field2<. The calculation requires the number of 'A's in the string in >field1<. Any suggestions? Thanks, M

Hello, I'm a new user of FM and not an English speaker... so please be patient with me TIA. I have a DATE field which is the beginning date of lease. ex :01/01/2000 Then two fields where I enter a numeric value representing: First lease period in number of years: ex : 5 Second lease period in number of years: ex : 5 I would like to create a new field that calculate the end date of the leasing. In this example it should be: 01/01/2010 (no need to take into account years of 365 days) I do not know how to write the fonctions. Can anyone help Thank you Caronte II

Is it possible to perform an aggregate function using the "GetField" statement as input. For example: Sum ( GetField ( "Table1::Value" ) ) If this isn't possible, how would I perform a similar action using a one line calculation? Regards, DM

Hi Everyone. I am aware that one can import a folder of Quicktime files (or, as in this case, a reference) directly into a Filemaker DB (dragging various metadata along with it). I'm wondering if anyone knows how to import a folder of MPEG files and achieve the same result? Cheers

I have a number field in a questions and answers table that has data such as: 7.1.065 7.1.103 This means the 65th and 103rd question of unit 7.1 etc When a candidate answers a question a record is created in another table but the data is displayed as: 7.1065 7.1103 Is there a reason why it is removing the second decimal point? I understand that a second decimal point isn't normal as such but I was hoping to keep with this format as it's meaningful to me. If I have to change the field to text I expect I won't be able to sort them numerically which works as things are now.

Hello, I'm trying to find a calculation in the If (test;result1; result2) format that can find a specific word in a field. The field I'm talking about contains two or three words without knowing which is where (left, middle right) .I want the test part of this calculation to search for a word in this field and return the result I will set it to. Help appreciated

Hi All Five year member; first post! My database has >1000 fields and hundreds of layouts. In 12 years of running this database, I've not been able to overcome the following (apparently simple) problem and I've decided to put some effort into trying to crack it. On most layouts, I require each field to be completed and I'd like some validation to check this is the case. However, all layouts only contain a small fraction of possible fields, say 50 on each one. I only want to check that the fields which are present on the layout are completed/not empty. I've been messing with 'validation by calculation' but I don't see a simple way of doing this. …

I have a table that contains 397 assessment questions and answers. Is there a way of pulling out a predetermined number of records at random? Would the random function be appropriate here?

Hi there, so, I worked out how to do a Year Quarter, based on a project date ending, when my boss turned and said - err, I meant Fiscal Year. Which leaves me with working out how to show which quarter a date is in - over 3 years , last year, current year, next year For example, the date 26/03/2011 is in the last quarter of last year, The data I am producing is in the format (Jan...Dec Year): Project 1 Q1 (date = 26/03/2011) Project 2 Q3 (date = 10/08/2011) What I need to produce is ( UK, Fiscal year start to Fiscal year end) : Project 1 Q410 (date = 26/03/2011) Project 2 Q311 (date = 10/08/2011) Project 3 Q212 (date = 29/…

Hi all, I have a text field that can hold data in the form x and y, where x is digit and y letter. It can have several different combinations: yyyxxxx, xxxxyyy, xxyyyxx etc. I need a return in the form: when there only digits, like xxxx, xx-xx (a dash every 2 digits) when there is a combination, like yyyxxxx, yyy-xx-xx (letters, dash, 2 digits, dash, 2 digits) xxxxyyy, xx-xx-yyy xxyyyxx, xx-yyy-xx Any hint of possible formula ? I use already a cf to separate the letters from the digits, but can't have a return when there are only digits... TIA

I am in the process of trying to make filemaker read. I need a calculation to list the words according to frequency preferably from a field from all related records. I also need to exclude a custom list of common words, ie A, The, An, Etc. the output i need is as a carriage return value list in one field. I know i can script it but is it possible to calculate it? Similar to the below but witht he ability to exclude words of up to a particular length and custom words. http://www.writewords.org.uk/word_count.asp Can this be done with filemaker or do i need to use java?

Hi All, Is there a calculation I can use that limits the amount of text that a user can type into a field - say 50 characters? Thanks in advance Milton.

First off I am a newb, so go easy. We use Filemaker for our scheduling of orders in a screen printing business. All orders are entered into schedule in a list format. One of the fields is GSP (Gross Sales Profit). Each order is given a GSP based on a formula we determined. What I have been asked to do is get a daily GSP for each Dept on orders entered. I create a report each day based on a CTRL Find for that date. Then I sort by Departments. I would create 5 fields with this for each dept to have on a daily report. I created a summary field that totals the number of orders per dept each day. But i cant seem to figure out how to make a new field with a …

Hi everyone. We have many dates in our database which are partly unknown. I wish to maintain the functionality of the 'Date Field' but also enter a signifier to indicate whether a day month, or year is unknown. It might look something like this (if an Asterisk were not an operator): **/03/194* (believe me, we have such odd dates in our system) So - the question is, is there a neutral operator one may use to insert into a field such as this which will be accepted and not turfed out? (getting the old Revert Field pop-up) Thank you very much. DHHC

" DesktopPath: " & Get(DesktopPath) & " UserName: " & Get(UserName) & " UserAcount: " & Get(AccountName) & " HostName: " & Get(HostName) When I use the DesktopPath function I get something like : Macintosh HD/Users/DanielPaquin/Desktop What I am interested in getting is the information contained between /Users and /Desktop i.e: DanielPaquin I've tried UserName and AccountName and found out those were in relation to FMP. I then used HostName which gave me the name of the MacMini server, in this case being ARWEN. Is there a get function which would give me only DanielPaquin In the meantime I will continue searching. …

If 16:00 - 14:30 = 90 minutes how can I write a formula based on two time fields that returns a result in minutes. e.g. 3 fields Time_out - Time_in = Elapsed_time Essentially I would like 16:00 - 14:30 to = 90 instead of 1:30:00 which is my FMP result. If I get the return of 90, I can then divide it by 60 to get a result of 1.5 which is my ultimate desire. The expression above returns 1.5 hours instead of 1:30. Any help would be greatly appreciated. Thanks, Steve

Hello, I’m doing a database and I found some strange issues, I tried to found some answers in the forum and on google but without any success, so I appeal to your kindness to help me with this. I have a database with 8 main tabs (1 real and 7 buttons), I do this because i want the other 7 tabs to work only with activate button (checkbox). After active this button you can go to another layout (for the 7 tabs). 1. First problem: The tabs works like objects so the goto object should work with me, but only works with tab 6 the others tabs go to the main layout, strange, because I did the same things in the other tabs. 2. Second problem: I have at main layout (pc0…

I have a field which I have set up as a calculation to pull in data from several fields - it is designated as a text field. One piece of data I want is numeric - contains a percentage. In the original field it is set up as a number which present itself as a one decimal point percentage e.g. you have to key in .125 to arrive at 12.5% as it is reprented in the originating field. Now, if I use GetAsText in my new field I obtain 0.12. If I use GetAsNumber it is also 0.12. How can I obtain 12.5% ? Thx.

There appears to be an error in the "Fixed number of decimal digits" option under "Format as decimal" on FM's Format > Number... setting. Here's the scenario. I use FM to generate price grids that MUST be accurate to the penny. As an example, here are three side-by-side calculations that produce different results when the above setting is invoke. (The "x 12 / 26" portion of each calculation is used to change "per month" figures to "biweekly" figures, or 26 times per year.) A. 9.01 x 12 / 26 x 3.00 displays 12.475385 when "Fixed number of decimal digits" is set to 6, and it displays 12.48 when it is set to 2. Normal rounding dictates that the 5385 forces the 1…

I am currently using one of the layouts in filemaker to print checks. my check layout has a calculation field that converts the numerical value of the check to a verbal representation of the number ie: $100.56 is translated into One Hundred Dollars and fifty six cents. I will refer to this field as numtranslate from now on. I would like to create a calculation field refered to as "checkline" from now on, that uses the value from numtranslate and intelligently adds a variable number of hyphens to strike through any remaining white space on the number word line on the check automatically, without having a fixed number of hyphens present so that it does not dist…

Hi all I have an issue where my layouts are starting to get sluggish on load and there is noticable flickering as scripts are executed. I'm unsure what element of my construction is causing this to happen. I am developing on the Windows platform. All my tables are joined to two TOCs of themselves. One an equality join which checks for uniqueness (which I could do without and use native validation) and one a cartesian cross product join which is used for my archive. I have four calculation fields in each table. cActive = not Archived Automatically create indexes when needed cCountRecords = Count ( ArchiveTOC::SerialID ) Unstored cCountArchive = Coun…

I am creating a staffing database and need to compare the number of positions from one year to the next by category. I have 25 buildings with two categories of employee per building and a report that sorts by building (BLD) and job category (JOB). I have one Summary field that is the Total of FTE (full time equivalence)so if there are five people in BLD BE in JOB D7 and one works half time (.5 FTE) the field (SUMFTE) summarizes to 4.5 That works fine to calculate and report the current number of staff by building (BLD) and job category (JOB). The problem is I need to calculate the difference between last years and this years staff by building (BLD…

Hello, all. I have a field which contains a name and an address. The lines are separated by carriage returns. I would like to populate a second field with only the name from the first field. Is there a simple calculation that will return everything up to the first carriage return? Thanks, Kevin

HI Fellow FM'ers, You're always so helpful and respectful to us lay folk that I hope you can assist on the following. It seems like it should be easy but I can't seem to find the solution If I have a set of records (let's say 30) and each record has a LOCATION field, I am trying to create a google map URL syntax in which I need to get the various locations from each record. So in broad terms it would look like "Location(from record 1)&","&Location(from record 2)&","&Location(from record 3), etc. " -or- "Tulsa, Houston, Dallas, etc" I've tried a List(Locations) but that only gives me a single value. I'm kinda stumped on this o…

I am not sure if I am posting this to the correct forum. I was wondering if there is a way that I can perform a DNS lookup on a domain name and have it return the IP address.

I apologize for my ignorance. I've searched the world over and have yet to find a solution to this that I can comprehend. I am using Mac OSX I have a simple database for client contacts and job information. On my hard drive each client has his own folder with many Word documents, etc., relating to that particular client. I'm trying to find a way to be able to link to each client's folder from within each client's particular record so that it is easy to review and/or open whatever documents are needed at a particular time. I thought I could write a script to open a URL (in this case, the URL would be to the particular client's folder),but, I don't know how to w…

I have a FirstName field and a LastName field. From it I want to create an automated field called EmailAddress. The email address is First Initial Last Name, so John Doe is [email protected]. I know how to string together the parts but don't know how to pull that first initial. Could anyone lend a hand? Thanks kindly!

Hi, I have a Table of records called "Jobs". These jobs have a field called "Status". Status has 4 entries: 1. Pending Move In 2. Renting 3. Pending Move Out 4. Moved out I would like to have a ratio calculated that shows my "Pending Move Ins" minus my "Pending Move Outs". So if I have 5 pending move ins, and 4 pending move outs, I'm +1. I need to know how to calculate this basically: Count the Number of records whose status is "pending move in" - the count of records whose status is "pending move out". I'm sure it's not a difficult process, but I'm struggling to figure it out. Thank you in advance for all the help!

Hello all, I'm new to the forum, but I've been developing FileMaker solutions since version 2. I have a database in which each child record in a table links to a parent record with an associated container field. Inside each container field is a PDF file. When printing the contents of the container field, the PDF looks fantastic if I print from a Mac and terrible (low resolution; very blocky) if I print from a Windows machine. The helpful gang at FileMaker, Inc. informed me that there is nothing they can recommend to improve the quality of printing the PDFs on Windows. Does anyone here know of a plug-in or workaround to get my PDFs to look nice when I print …

Hi, I am trying to use nested If statements to calculate age brackets (young, middle-age, old) based on an age field. This is my current script: If(Age <= 35, "Young- 18-35",If(Age <= 64, "Middle Aged- 36-64", "Old- Over 65")) The Young and Old categories are working just find, but the Middle-Aged just show as a question mark. Anyone have any suggestions to modify the script? Thanks! Tegan Stanford University Using FileMaker Pro 6.0

Hello Time-Experts my problem is as follows: I have one table with invoices I payed. They are for services in e certain time-period, let's say from February till October, on one invoice-record, In the second table there are my Customers. They worked with me for other time-periods, let's say, one from January till March, one from March till September and one from September till December. Now how can I calculate the intersection-times? I'd like to see my original invoice with a portal where I have listed all those customers which will pay a part of the invoice. Something like this: ------ Invoice A | Service from 02/01/11 till 10/31/11 | (273 days) …

How would I get a portal row number? IE. There are 15 rows total but only 5 contain data. I want it to display the portal row number when there is data in the row like 1 Data1 2 Data2 and if the rest of the rows are empty it would not show. I tried Get (PortalRowNumber) but that returns only currently selected row otherwise it returns 0. Thanks.,

I know this is easy for most-but I am struggling. I have a survey with Yes, No, Na check boxes. I want to have FMI count the number of "Yes", count "no" and count "na" while skipping the empty answers for multiple records. I am guessing there is a way to use a variable or global field or something. Right now--I am creating 3 different fields for one question. The actual answer as text (checkboxes). Then a calculation field for giving a numeric value to the answer (if (benefits = "yes";1;0), then summarizing the Total of Benefits. I know there is an easier way.

Im trying to calculate the total amount for a field for the current found set but i wont work! What i have at the moment is a calculation field in my parent table that calculates an amount per record. I want the total of this amount for all records in the found set. I've tried doing this in the child and parent table of the relationship as well as summary fields. Summary fields works, but for ALL records and not just the found set... Could any body please give any advice?

Hi I am relatively a newb with file maker and I apologize if I should know the answer to this question or if my database is way out of wack. Is it possible to have a calculated field that is a combination of a field on the current record and a field on the related portal record? And if it is possible how can I do it? Or do I have to redesign the database.

Dear all, I guess that this is a really easy question but I have been looking on some books and on the internet with no success. But well, take into consideration that I am both a newbie and a social scientist! The problem is that I have a text field filled with the content of newspaper articles. I would like to "highlight" a specific list of words on this text field (e.g. swiss, Switzerland). Ideally I would like to change the style and color of these specific set of words. I am using FM Pro 9 and it seems to me that I cannot rely on some of the "custom functions"-based solutions proposed. I have tried previously with Conditional formatting, but I am not a…

how can i break down a file name that i can get in a Variable1 the file name and in Variable2 the type?

I am trying to shorten the extremely long UPS tracking URL that we are emailing to customers. Is there a way that I can use a service such as http://snipurl.com within FileMaker? Also, how are you supposed to search the archives for a term like "UPS" when the site prevents you from using a search term that is less than four characters?

I need to know how to insert the time and date everytime I update the notes. Would be great if this could be in a different colour. Hope someone can help.

Ok, so I'm pretty new to FM, so bare with me. What I'm trying to do is set up a text field that checks to see if the text in field ShipTo_State = TX, if it does then I want it to auto fill another field that would have "8.25%", however if it doesn't have TX then I'd want it to say 0%. I'm trying to use: If(ShipTo_State = "TX"; Taxed = "8.25%") With "Taxed" field as my output text field, but if I put in TX in the state field, I get 0% in the "Taxed" Field, but if I put in any other state, it leaves the "Taxed" field blank. Any help would be appreciated! If you need more info, I'd be happy to explain further! Thanks, Andrew

I am looking for a way to display transparent PNG files, from the web. Currently I have the image appear via webviewer, but the background is white. Can you have a container calc a url?

I'm trying to think of the best way to do this and if its possible to do this through lookups.... We have a table which the user can enter the type of product they need, why they need it and when they need it (products). But the user selects a type from a value list which loads from the table product_types. This table stores the unit cost of the item. There is a cost field within the products table which on auto-enter looks up the cost of the item from the products_type table. This is fine, however the way the department uses it is they enter all there requirements first (into products) before entering the price (into products_type). Therefore I nee…

I have a problem constructing a database to generate a certain kind of report. I am running FM Server 9. These are the bare bones of the process I'm trying to figure out: The database uses a record for each job. Jobs start on the first day of one month and end the first day of a later month and each date has its own field. Each job has an estimated value, which gets frequently updated. Job length varies, and in fact, start dates and end dates change all the time. Start dates and end dates range up to 24 months or as brief as 3 months. We have many users logging in to update all these fields every day. For estimating purposes, we simply divide the TotalAmount by…

I am sure this has been answered thousand time, search sum, found set, is hard to narrow down. I have my search returned in a list view. some records are tagged with 1. Those are the records I would like a count within a script. Can this be done without relationships and extra fields?

Hi, I just started using FM yesterday (I used to use MS Access) and I got stuck. I have a table with data on stocks (e.g., price, date, ticker). For example: 12/31/2010 AAPL 300.25 1/31/2011 AAPL 325.73 2/14/2011 AAPL 350.11 I have three questions. 1) If i'm setting up a new calculation field, how do I retrieve a value from a field within the same table and using two criteria? For example, for the 3rd record (2/14/2011) above, let's say I want to get the price value where date=12/31/2010 and ticker=aapl which would be 300.25. So, for the 3rd record, the field would show 300.25. 2) Continuing with question …

I hope this is the right place to place the tread, the forum is incredible big. First of all I apologise for my little-used English, I’ll try to explain as best as I can. Say a padel/chess/whatever association. I can calculate the Monthly Fee from an annual Budget, member x, $xxxx, and then apply it to the months owed, but the number of months I get now is the total from the beginning date to current date and budget’s amount changes every year, so, I need that total, but separated by year, and I don’t know how to do that, my knowledge in FM is still weak. Sample records: Say Member 1, date of membership 09/01/2009... if his monthly fee is 2009=8.…

I have filed a bug report on this. GetNthRecord is completely unreliable, whether used in a script or custom function. It will return data from the current record, NOT from the Nth record. This script fails if you do not concatenate the empty string before the call to getNth. Tests with a recursive custom function also fail. Set Variable [ $$r; Value:"" ] Set Variable [ $k; Value:1 ] Loop Exit Loop If [ $k > Get(FoundCount) ] Set Variable [ $$r; Value:List( $$r; "" & GetNthRecord( Zips::city; $k)) ] Set Variable [ $k; Value:1 + $k ] End Loop

I have a solution which generates RTF word files from text fields that the user fills in. I would like to be able to insert images into the files. My idea was to have the user paste images, tables etc into a container field. I could then grab the raw data and insert it into the RTF stream with appropriate tags. Assuming the data from the container field would be usable, how do I access the raw binary data form a container field? Is this possible? Cant seem to find much discussion on accessing container field data and can't get any standard calc functions to reveal it. Am I missing something here? One way I suppose is to get the image file contents using T…

I have a simple Invoice database which has a CLIENT table, an INVOICE table and a PAYMENTS table. They all have to be separate tables. After creating an invoice for a client, I can then create a payment for that invoice. If it is a partial payment then there is a Balance Remaining. All I am trying to do is create a text field which would tell me if the invoice has been paid or not (yes or no). Because the balance remaining calculates from the payments table by adding payments and subtracting from the invoice total, I cannot seem to get the paid field to work. I have tried a custom function as well. Can someone help? Thanks

Hey guys, I have several fields that I am trying to combine into one field that would provide multiple answers based on the choices of the other fields. For example say I have 3 fields with radio buttons. First field is "How many times have you voted" choices are "None, 1, 2, 3 or more". Second field "What party do you affiliate yourself with?" choices are "Republican or Democrat" Third field "Have you ever worked for the government" choices are "yes or no" Now I have a calculation field that I want to return one answer for all of these questions.. (i.e. if "1, Republican and no" were chosen then it would show "Voted once, as republican, never worked …

Hi. I have a simple text field containing these values: "1¶ 2¶ 3¶ 4¶ 5¶ etc..". It is created by a value list (check boxes) containing these numbers. I want to create a calculation, probably a repeating one, containing field with a date plus each of these numbers. For example: 3 fields day...date...result (repeated field, date, d-m-y) 0....1-1-11..1-1-11 2....1-1-11..3-1-11 5....1-1-11..6-1-11 I tried to make it with the "extend" function, but it only calculated the last value. Anyway I don't like using repeating fields, but I can't think of an other way. It is a database that holds names and other data and use a portal to show the dates of an ac…

I hope someone will be able to help me with this calculation. I'm trying to calculate employees Lenght of Service (LOS). Example, if an employee 1 started in 1994 and Employee 2 started in 2001. I want to calculate there total years so employee 1 would be at 17 years and employee 2 at 10 years and have it calculate each year. How do I create that formula. Any help would be greatly appreicated.

Hi- Is there a way to set a repeating field to automatically import ALL the values from the last record visited? By default only the value from the first repetition is entered. If the default behaviour can't be set, can anyone supply a script that will accomplish it? I'm working with FP pro 9 Thanks, Jondb

I am a doctor and working on a research project. I have patients who have a lab value measured several times a day. If the value is above a certain level, we start a medication and we adjust the dosage several times a day. I record the lab value in LabValues:value and the time when the lab value was measured in LabValues:timestamp. I record the medication dosage in Medication:dosage and the time when the medication dosage was adjusted in Medication:timestamp. I want to find the last lab value that was recorded before the medication was started and color it red. I cannot figure out the formula to do that. Can anybody help? Thanks Andreas (Using FM11 on a Mac)

So I have a "last updated" field for my customer records. It is a manual enter date field with a drop down calender. The only problem is that the program that I imported this field from uses an unusual date format, ex: (01-Feb-11), so most of the records are using that format. I'd like to get all of the records onto the standard (dd/mm/yyyy) that filemaker uses. Is there an easy way to do this? Thanks in advance!

Hi All I am experiencing trouble when using a calculation field to calculate the total number of 'live' in my database. Everything works fine until I archive all records in a table so as there are no 'live' records in the system. The scenario is as follows: I have created a table that has a field called Archived which holds a boolean value that is true if the record has been archived or false if the record is live. I have created a calculation field called cArchiveKey which is always set to True. I have created a TOC of my table called Archive. The Archive TOC has a (self join) relationship with the main table TOC so that MainTable::cArchive…

Issues with Set Field script step with repeating fields. I have a repeating field in one file that may have one of more values per repetition in this one field. I am trying to pass this values from one field in one file to another in another file. The fields are both repetitive fields with the same number of repetitions (6). The original field is global which makes it possible for these values to be passed through the relationship between the two files. I have tried the following: - Using the Set Field script step and the calculation is pointing to the field containing the values. Done one per repetition. - Using the E.g. Set Field[6] script step again an…

I have a database for my cabinet shop that calculates dimensions. Cabinet box depth is a function how deep the countertops are, minus countertop overhang. Drawer box depth is a function of cabinet box depth minus 6 millimeters. If the cabinet box depth is 574mm then the drawer box depth is 568mm. I would like to always constrain drawer box depth to increments of 10mm with rounding always going to the next smallest increment of 10. For example: 568 mm would become 560mm 552 mm would become 550mm etc. How would I go about rounding the result so that it always expressed as an increment of 10?

Hello everyone, I'm kind of new in FileMaker and I'm trying to create a database for my in-hospital patients. I have two tables in my database (among others) Admissions and Days. Each patient can have many different admissions and each admission can last for many days. Based on the date of admission I am trying to create a calculation which will automatically add records to the Days table. So if a patient is admitted lets say on Feb 2 2010, every day until the discharge date a new record will be added in the Days table (records = days of stay of the patient in the hospital). However, if the admission of patient is registered two days after the actual admission lets say…

HI all, I have a maintenance database I want to try and do some calculations with. The calculation I want to occur only relates to one table in the DB. To simplify things: I have tbl_Maintenance which has 6-7 fields in it, one of which is "Cost" another of which is "Location". What I want to do is come up with a new summary layout that would add up all the costs, per location, for dates that are chosen. I see this possibly happening in a couple ways: 1. In a column report, user enters in Find Mode a date range of 1/1/2011...3/31/2011, then chooses "A" for Location, and all the costs for location A would show up in the calculated field "Total Cost…

Hi Forum. in Filemaker 11 I have several records with Name and Surname like this Paul Müller Peter Muller John Müller Simon Mütz If I search for all with surname "Mu" (without the quotes of course), *all* 4 records are found, including those with Umlauts. However if I search for "Muller" it ONLY finds the Muller one. Is that logical? What logic does Filemaker apply here? Should it not find three records if I search for Muller? like this: Müller Muller Müller Any light that may be shed on my darkness is much appreciated. :)

I need some clarification on how TextStyleAdd works. I read this page already but it doesn't seem to function the way I'm understanding it described there. It says "TextStyleAdd(“Plaid”;Italic) returns the word Plaid in italics.", but it actually returns the word plaid and any other words following it in italics. So I guess I need to use additional functions to make it work on individual words but I'm not clear on how to do this. Specifically, what I'm trying to do is create a price field that has the dollar and cent signs styled as superscript and no style on the numbers. The original fragment of code I'm working with looks like this: "$" & price ;…

is there a way of setting up a "countdown" of the number of available characters (or words) left in a text field? I am setting up a catering function sheet, and one of the fields is the full description of the function - menu, room setting, decor, cocktails, etc. For meetings, this can require just a paragraph or two. For receptions and dinner functions, however, the descriptions can be two or three pages. My problems are; (1) that I have to issue printed reports (file copies for the function dossier, for the catering files, a working copy for the banquet manager, etc.), (2) separate reports for the stewards, cooks, setup crews, bartenders, etc. These are easy …

Hello! I have a calculation which takes a supply on hand, and subtracts a daily use amount by day. How do I stop it from subtracting once the supply runs out? To make matters more complicated, the calculation already uses an 'and' statement, as two other variables are involved (ie in A = a certain value, and B = a certain value, then do this calculation). I can't add another 'and' to an 'and' statement, eg if A = certain value and B = certain value and self is < 0, then 0. Hope this makes sense. Thanks for any help you can give me. Pat

How can I get from the Calculation function the value found in DestinationFolder ? tell application "Finder" set DestinationFolder to (path to home folder as text) end tell Thanks!

Hello guys, I have a small problem... I have two tables Table 1: Want to sign out (YES) Table 2: Signed out (NO) My question is: If table 1 is "YES". I want that table 2 sets to "YES" when i pass or in the month (example: march) I have tried some things but it didnt work (worked with time to test) (This is the script in table 2) If ( Want to sign out = "YES" ; If ( Time ( 0 ; 5 ; 0 ) = ( Time ( 0 ; 5 ; 0 )) ; "YES" ; "NO" ) ; "NO" ) Can someone help me with the script Regards, Serhat

Greetings from a novice. I have a file called 'Products sold', and every time I sell a product, a record gets added to it. So if I do a Find on records created in the last week it might show: 1 x product X 3 x product B 2 x product Z 1 x product Y 1 x product X 1 x product Y 1 x product B 1 x product Z and so on. What I want to be able to do is: 1. Choose a date range for records added to this file 2. Add up the total number of sales for each of the products in that period (above it would show 3 of X, 2 of Y, 3 of Z, 4 of Basically, just see how many of each product I have sold. A Summary field of products sold doesn't do it,…

I have a checkbox(text) on a layout that gives the user an option of selecting up to 4 items. 99% of the time only one option is selected, and the other 1% of the time the need may be to select 2 checkbox options. What i am trying to achieve is to have a separate calculation field to display only the chosen checkbox item. I tried the following, but i do not get a value displayed in the calc field: Case(Checkbox_Text_field="Checkbox_Option_1";"Option_1_Selected"; Checkbox_Text_field="Checkbox_Option_2";"Option_2_Selected"; Checkbox_Text_field="Checkbox_Option_3";"Option_3_Selected"; Checkbox_Text_field="Checkbox_Option_4";"Option_4_Selected") …

I have a nutritional database I downloaded from the US Government in text format. I have foods table with an NDB_No as the key. A nutrients table has key Nutri_No, and related to foods table by NDB_No. The nutrients table is related to nutrient_def table by NDB_No and Nutri_No. I can create a filtered portal on a foods table layout that includes a list of all nutrients for the food with the same NDB_No and Nutri_No. So everything from Daily Fiber to Total fat show up on the filtered portal for that specific food item. Great. But the data needs massaging. For instance, I'd like to filter the portal on the sort order for nutrients in the nutrients_def e…