Calculation Engine (Define Fields)

SSN field formatted to automatically insert hyphens in the correct places -- xxx-xx-xxxx -- so I don't have to stop twice in every entry to find that darned hyphen key. Works flawlessly. However, when someone else entered ID data, she typed in the hyphens so I have some records that, when formatted, look like xxx--x-xxxx. (The calculation is: Left (SSN; 3) & "-" & middle (SSN; 4; 2) & "-" & right (SSN; 4).) Is there someway I can see the original -- unformatted, just numbers -- SSN that I typed in?

Hi, I have 2 fields in a table "Project Name" and "project_ID". so I could put "Asia" and "#78" no problem. I need the "project_ID" to be locked to the project name. So picking Asia would make the project ID field on a different layout automatically show #78 Can this be done? Thanks

Hi I am constantly coming across situations where I would like to extract the text of varying length between two text strings. I would like to use a insert calculation to get this information to a new field called. " MyINFO" EX Record contains text I used to go to the country to see all the farm animals. Now I stay home. I used to go to the city to view all the tourist sites. Now I stay home. I used to go to the beach to swim in the ocean. Now I stay home. I used to go to the bars to see people. Now I stay home. So in the above cases I am needing to extract the text between "I used to go" and " Now I stay Home." Is there a simple way to do this…

Hi all, In an attempt to speed up my FM12 solution which is hosted on a FileMaker server in a data center, I'm looking a the nitty gritty of how to speed things up for the users. We've likely all heard that we shouldn't use unstored calculation fields as they "slow things down". But HOW does it slow things down? It slows down the display of data on a layout, but it actually speeds up the creation and deletion of records. I set up two test tables in a hosted FM12 file. Each table had 100 fields. The first table, called "stored" had 100 calculation fields, pulling data from another field in the SAME table. Therefore, each field could be a STORED calculat…

I want to give the user an option to copy visit notes from the previous dated visit, into the current 'visit notes' field (useful if the content will be very similar, or identical). I plan to have this as a script attached to a button next to the 'visit notes' field. It is an optional function only(i.e. not to occur automatically when new notes for that client are entered). Basically, I'm not sure how to set up ( a calculation I presume) to basically copy the visit notes from "visit date -1" (and only if there is content in that field). TIA

I'm sure there is a way to do this, but I'm striking out. I'm relatively new to FM and at best a novice. I have a schedule. Within the schedule is a field called Class Status. This is a text field with a dropdown where the user selects the status. I have another field called Students. It is a number field where the user enters a quantity. Lastly I have another field called Student Count at Confirmation. I'm looking for that field to capture the data in Students when Class Status is changed to a specific value "Confirmed". I don't want it to change with any subsequent changes to the Students field. In other words, to take a snapshot of the Students field when the Class Sta…

Hello I have been trying to parse addresses from the internet. When I copy the text from the internet into a field called General, I get varying results of where the "phone number, the email and the website are. But in each case there is not always all three pieces of the listing. So how can I for the following example Phone: 315-678-2546 Find the line that begins with "phone" and put it in the phone field or find the line that begins with "email:" and put that line only in the the email field or find the line that begins with "website" and put that line only in the website field? The variable is that not always are there all three types of…

Hello- I have an inventory management system that tracks where in the warehouse a product is. Every day all the inventory is barcode scanned, generating a Scan record that stores the inventory ID and the location ID. The relationship between Inventory and Scan is sorted by time, and there is an unstored calc in the Inventory record that returns the most recent location. There is also a stored auto enter calc referencing the unstored one, that updates based on a Let trigger. The stored field is used for finds and sorting. My question: is this a dumb way to do this? From a normalization point of view it seems to me that the "most recent location" data should only li…

Hi Everyone On my Invoice layout, I have a field named "Status". Which when that field turns to "received" as the invoice is completed I would like the layout to be non edible, but you can still edit three of the fields, for example one of them fields being called "priceEx" Any help would be greatly appreciated Thanks

Hello, what would a calculation look like which takes all values after a dot? so for example it says .jpg in a field, it takes "jpg" and if it says .avi it takes "avi" Im using this for exporting a variable, can anyone help me please?

I have an input field, where the user can choose from a dropdown list between three non-numerica values (A,B,C), I'd like to create three fields which display the instances of the three values based on the data on the records, possibly sorted descending, so for example if the values in the field are: A,A,B,C,C,C the database should return: C: 3 A: 2 B: 1 And if the user creates two new record with values A,A, it should become: A: 4 C: 3 B: 1 How can I accomplish that?

Hello, I've been using filemaker for quite some time and tried to use it to build a "backtimer" log for Live television applications. however, after much searching, i cannot find a "real time running clock" function or plug in that allows a field to be updated with the "current time" in real time(system clock every second). a script does now work for my application as i need to be able to use the database to type notes as the time calc runs in the background. I have not been able to get a web viewer window to work because i still need a script to update the field every second. In my search i have read several times that filemaker cannot do this. I …

I am importing a csv file with a date field. My problem is that the date field is in USA format(m/d/yy). How do I change this to a European format(DD/MM/yyyy)? I have looked for a function but to no avail. Thought of the left function but this is never consistent as US dates can have 1/1/2012 or 10/10/2012. How should I do this?

I need to calculate the number of months between two dates. Not the number of days, nor the number of years, just the number of months. Can anyone tell a poor old (still thinking in FMP 5) man how to do this? Thanks

Hi every one, I need to calculate the number of years and months between two dates fields. August31 = 8/31/2013 DOB = 9/1/2009 I need the calculation to show: 3Y 11M I appreciate any help. Thanks

Hi, This calculation stopped working for some reason. ValueCount ( WindowNames ( Get ( FileName ) ) ) > 1 I clearly see; and count two open windows on the datafile ... So I put this: ValueCount ( WindowNames ( Get ( FileName ) ) ) in a message box to see the results and I get "0". Huh ... Odd Can anybody help me here. It was working, I would not have used this in my script if it didn't work ... and all the forum post say to use the above calculation to count open windows on the datafile. Anyway, any guidance would be grateful. Thank you. Tom :-)

Hi All,  How can i calculate rank, based on a single field. I have some duplicate records. Although, i want to rank the student with the highest marks to lowest marks.  Please see attachment for help.  Thanks in advance.

I tried to get the next month name with a calculation field but I only got the current month name with this: MonthName (Get ( CurrentDate )) I thought adding "+ 1" at the end would move the result to the next month Thanks for your tips

I am using FMP11 Adv. I want to do something similar. I have Multiple vendors for products but not every vendor has all of the products. I want to find the average price and then mark the avg price up. I would also like to have a double check that the Sell Price is above the highest vendor price so I never sell for a loss. Example Product Sku Vendor 1 Vendor 2 Vendor 3 Avg Cost Max Price Sell Price Mouse USB 1001 9.97 13.42 10.41 11.27 13.42 14.08 Keyboard USB 1002 …

I have a field called “ImportString” its a Text field that is Indexed, Can’t Modify Auto. On the layout I have this field set up with a Value List that looks up in the same field “ImportString”. I have the field set “ as a Drop Down List and Browse Mode, Find Mode are checked. When I try to make a selection in browse mode I get a box saying “Not Modifiable” When I tried earlier I did not check “Can’t Modify Auto” in Database mode and when I included the type ahead function it actually changed the text in the “ImportString” field, which was an obvious disaster. One other thing you should know is that “ImportString” is the field used to import information li…

I am currently working on a file where I would like for users to be able to drag and drop records between two portals. I am running into a problem with identifying the portal that the user has dragged the information to. When the user makes a selection for what they want to drag, a script trigger is run that captures the portal row number of the record that they are on. Then, when the user drops the selected data into a field on the portal, another trigger is run that places the information into the portal. The problem that I am encountering is that when the user drops the data into the portal, the related record always defaults to the first record of the portal. …

help me im new at filemaker and i want know how to calculate these fields Example 100 the other fields should be 110 I need two fields for this Cost and Cost Markup Percentage

For some reason the Get(ActiveFieldName) function is not working in any of the tests I've done. I click into a field, but the calculation field with the function in place never changes to reflect the name of the field that has the focus. It just remains blank. The FileMaker website says: Get(ActiveFieldName) Purpose Returns the name of the field that has the focus. Thanks in advance for any help. I'm sure it's something I'm overlooking. Alex Get (ActiveFieldName).fmp12.zip

Hi, i have a complex lot of calculations that I am trying calculate in number fields. In order to not have them in an unstored calc (performance), what I did is: let ([ trigger = timestamp ]; Calculate ( TextfieldinwhichIwrotethecomplexcalcperrecord) ) Works like a charm if e.g. I let it calculate 12, returns 12. Now what I would love it to do is: calculate $$global to return 3 if $$global equals 3. Unfortunately filemaker 11 wont do that. It calculates all kinds of complex relationships (I copy them from an unstored calc field). However to make the calcs more accessable I started generating global vars to do the trick. …

So right now I have a checkbox called nurses. Based off this checkbox I need to be able to do two things. First is count how many times that checkbox has ANYTHING clicked in it. The second is that I have to create a formula that says If (anything in checkbox) run Calculation X. The problem isn't the later half, I've figured that out. I just don't know how to tell FM to look for any check in the box. Any ideas?

In short, I have a portal in a layout related by a calc field that is not the primary key. I use conditional formatting in the portal for when the foreign key does match the primary key in the related table. That works. What does not work is a calc field in the portal table, placed on a portal row that is "foreign_key = primary key", it always shows a 0 even when the keys do match. Stranger still is that in the data viewer, the calc does work and updates correctly for each portal row I select. The longer version is that I'm working on a contacts solution where I have company records and individual records in the same table and addresses for each in a related table…

I have a field (Status) that if it = "Received" un-check the browse box. Can this be done by calculation using conditional formatting. TIA

This is for a church where CD's of the sermons are sold, and some people pay in advance, and I need a system that tracks sales and the credit balance of those who pay in advance. I have 2 tables - 1 x Customers 1 x sales The customer table has an "Amount" field - this is the credit amount paid (say $60.00) - not all customers have a credit amount. The Sales table has a drop down menu with "Payment Status" options are "Credit", "Paid" , "To Pay" The Sales table also has an amount - as some purchase more than 1 CD per week (CD cost = $1.00). I would like it to credit the "Amount" field of $1.00 for each CD sold to a customer (2 x cd = 2.00 etc) - so…

Hello , i want to know if is possible to always go to field to specific field. i have many field in the database and i dont want people to go to that fields , example if they select whatever field automatic goes to the field i want they modify . even if the click on the layout automatic go to the specific field . whaterever they do always point to that field please let me know thank you very much

First off, I used to use FM all the time - years ago - and could usually do whatever I needed... seems I have lost my FMskills though. Here is what I have: 3 tables table 1: all orders placed: order number, order date, customer name, oder total table 2: is all customers: customer name, shipping address, billing address, total number of orders for that customer table 3: list of people catalog was sent to I need to create a report that will show me total sales from each state, total sales from each catalog recipient from each state I think once I can figure out this report I can duplicate the thought process for all other reports I need to generate. …

I have FM12Pro adv and FMServe 12 pro. and Win7. ------------------------------------------------------------------------------------------------------ Trying to perform a CASE and REPLACE but not working so good. I have a Script,which should replace (number) Field "FrUSA" in Database " Allfreights" linking "Shport'fields" both sides. ============= Case( T01_ACCOUNTS::Shport="Houston";"800"; T01_ACCOUNTS::Shport="New York";"1200"; ) ============= (this is just an short example of Shipping ports,total file upto 600+ destinations in same script.) If I make a Trial Script with only one line : T01_ACCOUNTS::Shport="Houston";"800"; then all other destination are…

Is there a way to insert a non-breaking space in a calculation? For example, I have a merge field in a calculation with a doctor's name. Sometimes the text looks like this in the layout: ...according to the report by Dr. James Smith. or with Mr. or Mrs.: ...was determined to bje owned by Mr. John Doe. I'd like to keep the Dr. and the first name together on one line. I know that in WordPerfect, for example, CTRL-Space does the trick. Thanks, Kevin

I have a number of calculated fields, each representing a paragraph. The paragraphs are used to populate a report. Merge fields are included in the calculations. Some of the merge fields are dates. They are displaying as, for example, 3/23/2013. I need them to display as March 23, 2013. I am using the following functions to display the dates as desired: MonthName (Date_Petition_Verified_1750_DAJ) & " " & DayOfWeek (Date_Petition_Verified_1750_DAJ) & ", " & Year (Date_Petition_Verified_1750_DAJ Is there a more simple way to do this? I can't seem to find a single function to accomplish this. Thanks, Kevin

Hi, I need help trying to create an elegant solution to this problem. I need to do a "CountIf" type summary, counting all related records to the current one (which are also shown in a portal). I have an Assignments table (each record has a unique AsnID) and an Individual Submission table with many records (each tied to the Assignment record through the AsnID). A one:many relationship. The important field here is that each Individual Submission has a field "Rating" that only allows "A", "B" or "C" values. I'm sure you can see where this is going. Is there are way that I can have a calculation field in "Assignments" that will count how many "A"s there are in al…

I want to creat a drop down where I choose a persons name and filemaker automatically fills in the address, telephone, etc.

I have a very basic calculation I'm using a report along with another line that totals how many times "AHCCCS" is clicked: Case( Visit Reason = "AHCCCS"; 1; 0) This works well; however, there is a slight hiccup. If I click anything besides "AHCCCS" in Visit Reason it doesn't work. It doesn't count these in my total. So if I clicked "AHCCCS" twice and "AHCCCS" & "Ultrasound" once, I only get a 2 in my field instead of a 3. Any suggestions?

Hello daily number.fmp12.zipI have included a small database with only 10 records of quotations. I would like the user to only see one record each day and ONLY one each day. I would like to know if there is a way to only allow the user to view one and only one unique record each day. I would like them in this case only see a quotation once a day and not be able to go to other quotes. I would like filemaker to keep track of the quotes they have viewed and not be able to access them after the day they were able to view them the first time. I need help to do this. I have uploaded a file called daily number to show and example of 10 records . Once the see the quote o…

I think this should be a simple calculation, but I can't get it to work. For field3, I need to copy the value from field1 if the value in field2 is 1. Details: I have a time series table, each record is a year (field is [year], values are 1972, 1973, 1974, etc.). This is a country's timeline, during which some years it is at peace. I have a field [firstyearpeace] that is a number field with values of either 1 or [blank]. If it is a 1, that means that year is the first year of an era of peace. I have another number field [peacestartcalyr] that I want to be the calendar year of the first era of peace. I think this should be an If.. Then calculation (or perhaps…

I have an Excel document of music that I want to import into Filemaker 8.5 Advanced. The Excel document was created from a Word 11 document where each word or number is in a separate cell. I have successfully imported the Excel document into Filemaker. I now need to figure out how to convert all these individual fields into a manageable database and I can't do it manually because there are thousands of them. What I have right now is 5th Dimension Wedding Bell Blues1 Abba Chiquitta4 Rolling Stones Can't always get what you want4 Spanky and the Gange Sunday Will Never Be The Same2 As you can see some Artists have one name like “Abba”, some have two name…

I am trying to build a data base for a local non-prof. They rescue cats and kittens, and try to place them in good homes. The application will track the intake and discharge of each animal. As part of this they want a quarterly report of how many animals came in, went out, were spayed or neutered, etc. I have figured how to gather the data and display it, but have run into a roadblock on the report. I cannot see what to do when the end of a year occurs. I have scritpted the 1st, 2nd and 3rd quarterly reports, but not the year end. Obviously the final data for a year cannot be entered until the last entries are known, in January of the following year. I can generate th…

I've been working on this a while, and I'm completely stuck. I work at a television station, and we're creating a new database. I'm trying to have the Episode Key (used as the filename and for unique identification purposes) be autogenerated by the database to eliminate errors. Here's how it is supposed to work. There is a single character that tells what the category is, then 2 characters that identify the show, then a 6 digit date of production, and if that all is the same, it is appended with alpha characters until it is unique. We use a b c at the end because it otherwise is immediately following a number. A valid name would be sxl022613 and a second episod…

Hi All, I have a series of records for my students. They have 12 assessments held in 12 different fields assessment 1, assessment 2 etc) I want to calculate the average result for each student - some students will have completed all 12 assessments while others will have only done some of the assessments. I want to be able to calculate the average result for the number of assessments completed. Each assessment is out of the same mark. Any help gratefully received Edddiec

I have a text field and a GetAsCSS calc field and render the calc result in a web viewer. The text styling (bold, italic, underline, font color, etc.) work as expected but any paragraph formatting (center and flush left) appears to be lost. I have a line of text which is centered, as well as indenting the first line of each new paragraph but it's all being rendered as flush left. I've styled the text both in layout mode and browse mode and the results are the same. Is this a known issue or am I missing something? Thanks!example.fmp12.zip

I have a client database which is related to the contacts database by __pkClientID Clients can be one or more individuals with the same last name (eg Gemma and Ben Smith) OR two or more individuals with different surnames (eg Gemma Kay and Ben Smith) I want to have a calculation that automatically creates a ClientCODE eg: Smith_G&B (in the case of individuals with the same last name) OR Kay&Smith (in the case of indviduals with different surnames). Can anyone help me with such a calculation, where filemaker needs to firstly determine if the surnames in the related contact records are the same, and if so, then to go with Smith_G&B and if they ar…

I have a solution that will need to be able to calculate over thousands of records. Basically I need to be able create stock reports by department for a specific date, but tomorrow I might need it broken down by brand for another date. I will only know when there is an issue in a particular area, so it will need to be dynamic. I will have up to 100,000 different lines that will need calculations but the standard method of updating a stock level at point of sale etc will not cut it in this case. Is there a workaround that to this problem or do I have to bring in the big boys with the Oracle toys to handle this? It is the only thing holding this solution up, but unless…

How can I return the actual name of a field in a calculation? If the field is "guarantee" to return the text "guarantee" Thanks

I am trying to get a simple calc to work but I am having trouble. result is a number. Field 1 is a text field, field 2 is a date field, and field3 is a number Case ( field1 = "a" or "b" or "c" and IsEmpty ( field2 ) ; field3 ; "0" ) It only works if "a" is the value. And it is not correctly evaluating whether or not field2 is empty. What is the correct context for this? Thanks, Dave

Hi there, I'm currently developing an ongoing solution where I have used decimal times. ie. 1.5 = 1hr 30mins, 3.75 = 3:45 etc. The reason I used decimal was clear to me at the time but now I would like to dispay the resulting calculation in "proper" time format. I could probably nut it out using some convoluted method but was hoping that someone has a more elegant solution. Cheers, Steve.

I want to put number (in serial) like 1, 2, 3, 4 .. etc for each row in the report. Anyone can help.

Hey Guys, I have two tables "Projects" "Sub-Projects", each master projet has several subprojects attached to it linked by ID. Every one of these subprojects has a start and end date. What I'd like a is two calculation fields in the projects table, "overall start date" and "overall end date" -start date which looks through all of the start dates in the related subprojects and finds the earliest. -end date which looks through all of the end dates in the related subprojets and finds the latest. Any ideas how I would go about doing this ? Ideally the dates are stored, but updated fairly regularly as subproject dates change quite regularly in the database…

Dear friends, nice to see so many familiar faces here (LaRetta, Lee, Vaughan, Ocean West...) after being away from my Developer Hobby for years... I've been compiling the index for my book, using three main related tables for * flagged locations in text * terms to be indexed * subheads under main terms I've successfully used the List function to get each term-entry record to include a list of all related subheading strings (where each subheading string includes a comma-separated set of associated page runs). So I can now output something that looks quite close to the formatted index I need... HOWEVER, the List function is returning a poorly-ordered list …

Hi, Question ... I know you have to commit the record when using "SET ..." but what if I'm setting global fields on a layout ? Do I still need to "Commit the Record" since there really is no record yet?? in my script step ?? Also, can multiple users be on the same global fields, log in from different computers ... as with a regular field if someone is in the fields nobody can enter any data until the user is done, is this correct ?? Older version vs New version. Thank you.

Below is an image of my Script. Problem Area:(Highlighted in Yellow) Set Field [Table::Vehicle_Description1; $&"VD&$i Goal: Variables $VD1-$VD8 are holding data. I am doing a loop $i which is a counter that increments from 1 to 8. If there is Data within the variable then I want the field Vehicle_Description1 to be filled with $VD1 on the 1st iteration. Current Output: When running the script, assuming data is in $VD1, I am getting VD1 within Vehicle_Description1 instead of what data is within the variable $VD1. What is unusual: The counter $i is working fine. You can see this with the output VD1 InShort: How do you read a variable when you h…

Ok... I'm now at the frustrated point when I know that I *should* be able to do it, but I can't find the answers because I think that I'm google-fu-ing the wrong words. I have a field that captures a Title, which consists of a variable amount of words. What I want is a flag (or a "yes") when the Title field contains specific keywords. I know what the keywords are. Ultimately, I want to apply the same thing to a description field using the same set of keywords. I know I'm gonna kick myself when I figure it out, but any help would be appreciated.

Once again, a royal brain hernia trying to figure out what I'm doing wrong. Â Please refer to, and open, the attached; go to the Report xAbsenceCount layout. I have one faux student's record in the mix, but the real file will have many different students, sorted by last name then first name. Â What I'm having trouble with is getting a total from the summarized second column of numbers, which should equal 9.0. I've tried using GetSummary, Sum, some creative sub-summary'ing of fields...nada. Â What do I need to fix to get the Att_Date2__lcn field to show a summarized total? I just know I'm going to slap myself in the forehead once I'm told what the problem is, so I…

Heres the setup: I have a number field (Elapsed Days) that displays the number of days it took to quote an insured. The field starts at 0 if entered today, and increases every day until the "quoted box is checked" at that point filemaker inserts the current date into a separate field (Quoted Date), and the original field now stops counting up each day and stays on the inserted day. I am trying to create a red bar that displays over all the fields if it meets these 2 conditions: 1. Its greater than or equal to 3 days, so 3,4,5... AND if the separate field IS empty, then display the red bar. Once the field has a date in it, or if the number in Elapsed days is 1 or 2 th…

This is the calculation I'm trying to get to work: If (IsEmpty(Quoted Date) ; Get(CurrentDate) - New Quote Enter Date) Else ; (Quoted Date - New Quote Enter Date) The first line seems to work, but Filemaker is now saying (cannot find specified field regarding the Else. Background: I've got a field (Elapsed Days) that I would like to calculate automatically the time elapsed between current date and New Quote Enter Date, however once a customer has been quoted, I have an auto enter date field called Quoted Date that I would like this Elapsed Days field to reflect, but only to calculate when there is something in the Quoted Date field, until the customer has bee…

I am developing a solution to track funds for my scout troop and have a good portion of it up and running, but I’m getting stuck on how to handle meal charges when scouts go on an outing. Basic concepts: - Many scouts can go on many different outings (Camping Trips, Skiing, Day Hikes, etc.) throughout the year. - For each outing, each scout is assigned to a specific Patrol, but those patrols change from outing to outing: A particular scout may be assigned to Patrol #2 for one outing and to Patrol #4 for another. - For each outing, each Patrol will have 3 or 4 scouts buy food for the 8-10 members of that patrol. The total cost of all meals is equally divided bet…

I was wondering if there was an easy way to dynamically indent a field inside a portal. I sort of know how to do this when there is only a single line of text but I want to see if I can pull this off for a multi line text field. I think this is called line wrap or column wrap? At one point I think I saw a custom function that would do something like this but I can't find it anymore. Any ideas? This is just for display purposes.

Hi, This is clean data issue. I obtain from and old database field, called "num" where the users put the street number or gate number, these 13 values from the index(: 12 13 14 14 16 14 Y 16 14-16 14-16-18 14,16,18 14y16 15 16 17 17y20 and in order to obtain a clean relationship data I have to transform in 12 13 14 14¶16 14¶16 14¶16 14¶16¶18 14¶16¶18 14¶16 15 16 17 17¶20 As you may imagine I need this kind of data in the child side of the relationship just to see #14 related issues in one instance and not in 6 different cases. I have been browsing BDunning and FMFunctions with no luck cause I guess that a custom funct…

What is the setting to choose where Filemaker automatically truncates a number? I am sure it is something silly that I am doing but I can't find an answer in a search anywhere. I am converting a unit of time to the SI base unit SECONDS with a conversion factor and then to a different unit with 1/conversion factor (of the different unit's conversion factor). I want to be able to control the precision of the calculation. Example: if I convert hours to seconds my factor is 3600 and then happen to convert back to hrs is 1/3600 or .0002777777777778. What in Filemaker determines this many decimal places? Thanks

I don't know if I've posted this in the right place but I hope someone can help me. I'd to take a list of names and put them in 1 field with a comma between each name. When I'm done, I need to have several fields of groups of names. For example. From this: Group 1 Name A Name B Name C Name D Name E Group 2 Name F Name G Group 3 Name H Name I Name J Name K To this: Group 1 field: Name A, Name B, Name C, Name D, Name E Group 2 field: Name F, Name G Group 3 field: Name H, Name I, Name J, Name K I hope someone can help me. Feel free to contact me by email if you like: [email protected] Thank you.

I have a web site that is presenting data from fields in a Filemaker Database. FM 11 Server. When viewing the text in a browser it does not see the carriage returns. Is there a way to embed some html/php in the field so that the browser will add in carriage returns?

Hi Guys, Need some advice as I'm not sure how to appoach this, whether to use a recursive function or whether the bog standard calculation engine with Let and position might solve what I am after. I have a field, with data similar to that below, but tons of it: 10/09/2012am entered by name on 13/09/2012 07:48:19 11/09/2012pm entered by name on 13/09/2012 07:51:22 12/09/2012am entered by name on 13/09/2012 07:54:55 12/09/2012pm entered by name on 13/09/2012 07:55:07 14/09/2012am entered by name on 14/09/2012 08:33:51 13/09/2012am entered by name on 14/09/2012 08:34:11 13/09/2012pm entered by name on 15/09/2012 11:40:51 17/09/2012am entered by name on 17/09/2…

How do I declare a variable which is the result of a Let function? The following is inside an Execute SQL step but I don't think that matters: Let([ ~sql1 = $SQL_SELECTid & $WHERE_Parentid ]; ExecuteSQL ( ~sql1; "" ; ¶; $Currnt_PREMid)) What I want to do is to define multiple ~sql statements inside the first part of the Let statement and then have their results convert into variables which I can use for other calculations either inside this step or in other steps. I tried the following but it returns a "?". The overall goal here is to make my calculations more compact and readable. Let([ ~sql1 = $SQL_PARENTid & $WHERE_PREMid //I want to have …

I have created a database to track inventory in filemaker 7. Can anyone assist me in a way to create a history file of an item being checked in and checked out. I would like to email the file I created so it can be examined. Please provide an email address if you are interested in assisting me,

I need to make a script that will fill a container field with an image but to make the container field clear again when I click on the container field again. The image toggles when clicked.. How can this be done the best way? Thank you

I need a function or script so that the date box goes red or there is a popup that says that my domain expires in 14 days. Easy explanation would be helpful as I am new to Filemaker. Thanks

Is there and easier way to write this? I need to return a result based on gender, age and Subtest4totalPtScore? Any help would be great. The below returns the correct result based on "Subtest4totalPtScore" - but only for Male, 11 years old. I need to add other ages and females. Thanks If(Subtest4TotalPtScore="";""; If(and(Subtest4TotalPtScore ≥ 0; Subtest4TotalPtScore < 5) and Gender = "M" and TestDateChronologicalAgeYear = "11";"1"; If(and(Subtest4TotalPtScore ≥ 5; Subtest4TotalPtScore < 9) and Gender = "M" and TestDateChronologicalAgeYear = "11";"2"; If(and(Subtest4TotalPtScore ≥ 9; Subtest4TotalPtScore < 14) and Gender = "M" and TestDateChronologic…

Hi, I haven't use Filemaker since Version 7 so very, very rusty at present. I'd like help with a calculation please. I have a date field that comes out as 2/3/2013 or 16/3/2013. I would like a result that gives me 02032013 or 16032013 with the calculation result as text that I can concantenate with other field text to use as a primary key identifier. Thanks.

Hi, Field 1 Value list with editable check box cat dog mouse Other... If I select just cat and mouse, I would like Field 2 to look like: cat. mouse. Not sure whether this is a calculation or a script that I need to create, either way, not sure how I get it to select the line of text.

I am trying to automate a url lookup but the URL requires a "+" symbol between all words. Is there a calc to do this? There are always different number of words? Don't understand how to do this with more than 2 words. Thanks

Hi I am trying to compare two lists in my solution. Detail description of my problem is in the attached document. Short description of this problem is that in the table Production_Lot I have two lists, Prod_Measurement_List and Packaging_Measure_ToDo_List The lists are generated from two portals containing Products_Measurements and Packaging_Measure_ToDo and the items in the lists are identical, like 9Docosahexaenoic acid (DHA) 8Eicosapentaenoic acid (EPA). Then I have the test calculation checking if the patterncount of the product measurement from each portal record of the product_measurements is found in the list Packaging_Measure_ToDo_List and vice v…

Hello, I would like to make a relationship for sold items and for stock items to know how many items I have in stock. I have two fields one for Quantity Potentiometer and one for Type of Potentiometer. I make a calculation field with with name Sold 250k Pot with function: If (Pot type = "CTS 250k Audio Pot"; Quantity Pot) and then I have another one calculation which removes the Bought 250k Pot and I know how many Pots are in stock (Bought 250k Pot - Sold 250k Pot) and work perfectly but if I go to a new record the Bought, Sold and Stock fields didn't show me the remaining stock items or the bought parts or the sold parts from the previous projects. Could you h…

I have a list of customers with multiple visit dates that could be scheduled a year in advance. I want to return the next date from today in another field in different table. eg. Cust SchedDate ABC1 02/10/2013 ABC1 02/12/2013 ABC1 02/26/2013 ABC1 03/12/2013 XYZ1 02/13/2013 XYZ1 03/13/2013 Cust ABC1 would show 02/12/2013 (today) Cust XYZ1 would show 02/13/2013 Any ideas would be appreciated.

Hello to all I have to layout that his date of a main company and i have a layout of the branches. in the main company there is record named lockup that is off or on. i what that if the main company the lockup filed is = "yes" then in layout branches it will lockup from filed price. but if in layout main company the lockup filed is = "No" i will be able to enter a price @ the Brach layout. Thank you Dani

Hello, I don't know if anybody can help me but I'm trying to specify a field to replace the value based upon a variety of parameters specifically for States. For example I attempted to do an if statement to turn the word of the state into the abbreviation " if( Upper(Self="ALABAMA");"AL"; if(Upper(Self="ALASKA");"AK"; if(Upper(Self="AMERICAN SAMOA");"AS"; if(Upper(Self="ARIZONA");"AZ"; " and so on. I also tried: Case( Upper(Self="ALABAMA"); "AL"; Upper(Self="ALASKA"); "AK"; Upper(Self="AMERICAN SAMOA"); "AS"; In both situations I end up with a Too Many Parameters error. Perha…

I'm working on a solution for a client that requires conditional hiding of a large group of fields on a layout. If Condition A, all the fields are displayed and are accessible. If Condition B, the fields are not displayed and are not accessible. The trick (for me, at least) is figuring out how to make them inaccessible. A large, conditionally-formatted field, which would be used to hide the group of fields, cannot be made to prevent access into the hidden fields. I.E., the fields are "hidden", but they can still be clicked into. A large, conditionally-formatted button, which also would be used for hiding, presents a similar issue: If no script is assign…

I have a list of people in my records that have a chronological order but there are times when I and the user will need to insert a new record at the right location in the file. (Between two existing records) Is there any way to do this with a calculation or script? I guess I am trying to build a timeline that constantly needs this kind of insertion. Is this at all possible with filemaker?

Hi... I have used FM for a long time, but have not programmed this calculation in quite a long time or dealt with many text functions. Project Overview is Name Badges...easy enough. Some names are longer than others (of course they are!) so I am using text formatting to control the font size. I know these text codes, so this is more of a general question and I am a horrible explainer, so I will try here. For example... Font size is less than or equal to 20, return 20 Font size is between 21 and 25, return 18 Font size is between 26 and 30, return 16 Etc, Etc, Etc My question is rather general and can apply to many calculations... How do I code thes…

I have a large file that contains numbers from a previous import The problem is that I would like to fill in the field of the next record with the previous number until the number changes How can I make a calculation that keeps pasting the new number into the next record same field only until the number changes and then starts pasting that number? Field is called "NumberV" thank you

I have a field called "AC_Name". I need to populate it immediately when a new record is created, but I can't figure it out. The field is calculataed as "AC_Name = Get ( AccountName )" but it does not populate until after the record is committed. I've also tried making it a text field and scripting the field to populate OnRecordEnter and OnLayoutLoad, but nothing seems to run the calculation or script prior to commit. How can I get the field to populate immediately upon entering a New Record? Any ideas? Thanks, Kevin

I'd like to create a calculation that would combine a single field from all my records. For example, I have a calculation for a person's entire name (called EntireName), bringing the first and last name together. I would like to create another calculation field that will combine EntireName for every record . . . the final field result would look like this: Peter Jones Mary Smith John Timber (Each record's entry needs to be followed by a paragraph return.) Thanks, Steve

I have a timecard solution. I'm using a filtered relationship with a global dropdown clendar date field for selecting the beginning date of the pay period week to view that weeks timecard. Our pay period runs from Thursday to Wednesday. I have set up the Employees::gWeekBeginSelect field to auto adjust to the Thursday before any date selected. Calc Case ( DayName ( Self ) = "Friday" ; Self - 1 ; DayName ( Self ) = "Saturday" ; Self - 2 ; DayName ( Self ) = "Sunday" ; Self - 3 ; DayName ( Self ) = "Monday" ; Self - 4 ; DayName ( Self ) = "Tuesday" ; Self - 5 ; DayName ( Self ) = "Wednesday" ; Self - 6 ; Self ) I would like to be able to do this without hardcoding t…

I just edited this to make sense (I hope). Basically my objective is to reduce the number of global variables in a script when their conditions are overlapping. For instance, where the comment is in the below Let statement, can I add: $$Command2 = "Yield" given the same conditions? Again, I know that I can just create another global variable but I'm trying to keep it simple given the number of lines in the actual code. Thanks in advance! $$Command1 = Let([ ~A = Case( conditions 1, 2, 3; "Black"; conditions 3, 4, 5; "White" ) ]; Case( ~A = "Black"; "Stop"; //<--can a Let statement …

Hi, I think I'm in the right forum list. I have a client who ships with UPS. Each Tracking No. could have many items. I can build out the report that list the Tracking Number separately ( Once ) with each "Description" list correctly underneath the Tracking No. 123456ABC Description Description Description Description Description 654321CBA Description Description Description Description Description Description However, my trouble is that I want to provide my customer with a count of tracking number on the invoice. Meaning: I have 11 total tracking number, 6 are 123456ABC and 5 are 654321CB…

Hi Is there a way to get only the filenames, from a file path in a field in filemaker ? The paths looks like this: Macintosh HD:Users:paul:Desktop:files_web:1801-4i.pdf Macintosh HD:Users:paul:Desktop:files_web:newimage12654.pdf I need only the filenames like this: 1801-4i.pdf newimage12654.pdf The file names may vary.

Hi all, Sorry for this question, i have a table where it has Product Code, Lot number, Qty all i want to do is sum the quantity by Product code product codes can have different lot number but i just want to the sum of each product code, many thanks

I need to calulate the mileage costs based on various rates within ranges of dates. For example, if the date of travel falls between July 1, 2012 and December 31, 2012 the travel cost value = .555, and if the travel date falls between January 1, 2013 and June 30, 2013 the travel cost value = .565 I have tried the following case function for the field <<mileage_cost>> with 2 records that contain the dates "12/12/2012" and 1/31/2013" respectively. The function returns the value ".555" for both cases, but should return ".565" for the second case. Case( travel_date > 6/30/2012; .555; travel_date > 12/31/2012; .565 )

Hello, I need help with a calculation. I am a volunteer working on a database for an organization offering meals on wheels. They have decided to sell prepaid meal cards so the volunteers delivering the meals , need not handle money. Each card is valued at $ 50.00 ( 10 X 5.00 meal) What I need is a calculation which will inform me the service coordinator that the value of a card has expired based on the meals purchased. Then If I have a conditional field based on amount left on card that shows '' need renewal'' then I could have a warning shown that this person needs to renew his card. For example:: 1 Card purchased Jan 1 2013 for Mr X value $50.00 …

Dear FileMakers I have two data loggers, each outputting data to a CSV file, which then gets imported into two separate tables on my FM DB. One data logger creates a record each second, the other each minutes. So I can splice the seconds data together with the minutes data, I am trying to create a third table with time in minutes, which averages the data from the seconds table. So I'm trying to create a third table to average the output of the seconds table into minutes. Starting at the Average table, I need to get the value in the time field (say 12:20) and to then go to the Seconds table, and find all records between 12:20:00 and 12:20:59, take an a…

Hi, I have a customers table, products table and an Invoice table. The Products table has a Stock Field(product quatity in Stock). The Invoice table has a Quantity Field (Quantity purchased by the Customer) I want the Stock field for each product to Decrease in value as the Quantity field increases for every purchase made. Can I get help with this? I am new with the FileMaker Pro Thank you.

I have two fields Date_Hired and Date_PerformanceReview. I would like the Date_PerformanceReview to update to next year once the Date_Hire of the current year has passed. Date_Hire = 1/3/2009 once the current date goes beyond 1/3 of the current year it would change to 1/3/2014

I have a time card database which employees clock in and clock out of. Currently the fields TimeStart and TimeEnd hold the exact time that someone arrives and 'clocks in or out'. The problem with this is that each employee has a standard starting and finishing time and overtime is really only meant to be paid when overtime has been requested or required on a job. Coming in 10 minutes early to work does not constitute overtime. So I would like to simplify the calculation of total hours for the day by rounding the TimeStart and TimeEnd fields to 15 minute intervals - rather than just rounding the total time spent for the day. In this way - if someone clocks in …

Hi I have a series of pictures in filemaker container fields. I have 30 records with a photo in a container field in each record. I want to play back the records randomly in a slideshow. I can get the random number of the record and set it to loop and playback. Aside from the fact that filemaker has no way to cross-dissolve on record to the next (which I would like to see added someday) I have the users flag their favorite photos and their least. Based on their votes in field "VOTE" the can choose a number of one to ten . I would like to find a calculation that would play their favorites more often than the others. I am not sure how to write a calc like that. So 3…

REWORDED QUESTION I want to see in a portal records where ANY VALUE in a field in the home table = ANY VALUE in a field in the related table. So if the home table has 'LS WF BD' and the related table has 'Hillsborough' there will be no match (in spite of 'ls' occurring) but if home has 'LS Leeds WF BD' and the related table has 'Leeds Bradford Halifax' there will be a match I can manipulate either field to be return-separated values or something, if that helps. I just can't see my way ahead right now. Thanks, Emma

I am adding a report to my Quotes database. My database structure is like this.I have 12 Worksheet tables that are used to enter items into to get prices. They are related to my estimates table where I have a field that adds all the worksheet total fields together. The totals from each worksheet are then displayed on the Material Supply Contract form. There is also a field that adds up all 12 worksheet totals to get a grand total. The problem is sometimes a customer doesn't want to buy the entire quote at once. They want to buy a couple now. Instead of having to delete all the rest of the data out of the tables, I have created a field that shows the worksheets status. If …

Hi, I noticed that using ExecuteSQL in a calculation field doesn't mark the field as unstored although I used other TO. So the question is : "Are the result stored or not?". Thanks

I have created a WIP report which lists and sorts jobs in progress based on their scheduled installation month. The report is sorted by month and shows total values for each project. I have also included a related field in the report - the hours allowed for each job (which comes from a related table - costed time in estimates). I cant work out how to subtotal the costed or allowed time for the jobs when the report sorts by month. The value in the total field just seems to be the last value in the list for that month. The calculation at present is: Sum (Project_ESTIMATE::TotalItemCostHours) is it possible to have a calculation field (or a summar…

I have two tables here. First one with film titles and dates of different channel I need to sent to. The table structure is basically like "Title Channel_1 Channel_2 ... film_1 Dec'12 Jan'13 ... ... The second table would be a chart that I want to generate from data of table one, listing each month's due titles to each channel like: "Month Channel_1 Channel_2... Dec'12 film_1, film_2 ... I used a x relationship to connect two tables. Each text block on table two will be a list of film names that goes to the channel under the related due month…