Calculation Engine (Define Fields)

Hi, I'm a Filemaker newbie and I'm trying to run some reports based on employee timesheet data. I've attached a sample of my database in the hopes that someone could steer me in the right direction. It's important that I be able to creat reports based on data for each employee as well as by work activity. I've tried numerous things, including reading other threads on this board, but I can't seem to figure it out. We really want to be able to track employee overtime as technically, employees are only able to accumulate 21 hours in their bank at which point they are supposed to take time off. Thanks in advance. Timesheet.zip

I have been using Filemaker for some time now but this has me stumped. I have tried to keep this as brief as possible. My database is used for sales invoices for my mobile phone shop.and I use a traditional sales invoice table along with sales line items and product tables.Either every year or 18 months, a contract customer will get a discount off their next phone upgrade. I have a field in 'sales line items' that holds this.I have also set up a layout and script that prints out a letter showing the handsets a customer is entitled to (based on their discount) with an image using portal from products - this works fine. I now want to show all the handsets in the range…

Does anyone have a handy calculation from writing out numbers (as in writing checks)? I used to use macexample plugin but it doesn't seem to work with v9. Thanks, Michael

How would I do this? if field c is "Yes" then transfer value of field a to field b if field c is "No" then transfer value of field b to field a any help is greatly appreciated

I'm trying to find a way of having a field automatically record another field's modification date. I have found several threads on this with the following solutions. The only problem is, is that these only work if the field gets changed continually to a differnet value. In my field, the data can only be one of two values. In the solutions indicated below, this works only when it gets changed to the value that wasn't there when the field was defined. Let ( trigger = dpo calc ; Get ( CurrentTimeStamp ) ) or Case( dpo calc; Get(CurrentDate); Get(CurrentDate) Would someone have an idea for getting around this problem

How can I track changes for each record? (Who and what the changes are)MArilee

Hello and thanks in advance. I have a field (PurchaseMinimum)and I want to change the numbers to letters for security purposes. I have this code but I get an error that says I have too many separators... Any help is appreciated. Substitute (PurchaseMinimum,1,"L", PurchaseMinimum,2,"U", PurchaseMinimum,3,"B", PurchaseMinimum,4,"R", PurchaseMinimum,5,"I", PurchaseMinimum,6,"C", PurchaseMinimum,7,"A", PurchaseMinimum,8,"N", PurchaseMinimum,9,"T", PurchaseMinimum,0,"S" )

I hope I'm posting this in the right place, and now I'll get right to the point. We track payments for ads sold over a period of time on different web sites. Right now I can create a report that shows me that Site A earned $3100 for Campaign X that started on 5/23/2008 and ran through 6/22/2008. If I order based on start date that would show up in May's report, but really those dollars aren't all attributable to May. The $3100 is for the entire period, meaning they earned $100/day. I would like to generate a report that would show May: Campaign X $900 June: Campaign X $2200 Given the data I have, I need to find the number of days in the…

Hello All, I have a database for tagging documents (for later use on a website.) I have 3 tables: Documents (ID, etc.) Tags (TagID, tag) DocumentTag (Document ID, TagID) On a layout from the Documents table I have a portal showing all the possible tags from the Tags table; based on a constant relationship. Right now I have a plus sign on the left of every tag and a delete sign to the right. It's redundant. I have another solution where I was able to show red or green arrows based on whether the current stock price was lower or higher than the closing price. That was easy, because the information was in the same table. Here every time I add a t…

Is there a function that works like the Get(filesize) function only for a specified file instead of the current db?

I am hoping that someone can help me use xmChart to actually create a chart. I have copied the calculation functions from one of the examples into my calculation field but filemaker won't accept it because it says that it can't find the "LineChart" function. Here is what I coppied xmCH_DrawChart( "OpenDrawing(600;400) SetThousandsSep(/",/") /* set up data */ ChartData(" & List (Mydatabase::date) &":"& List(My database::value)& ":" &") /* set up chart */ LineChart() Does anyone know why I am getting this error message? Tar

I have a FMPro v6 Image DB with 2,602 records (1 image per record) wherein the module data size is 1.98 gb. When I converted it to FMPro v9 -- the same module goes down in size to 270mb. Yet the functionality seems to be the same. How is this possible? What the difference between how jpgs are contained in FMPro v6 versus FMPro v9? Thanks for any enlightenment!

Hi I have someone else's old solution which is now in 9. Having a problem with a global field. Say the global was 123456 when it was hosted. It is now calculating everything off that 123456 as opposed to updating it and moving forward. Have unhosted, deleted it, changed it to text, then rehosted and changed to global and added a number to no avail. It is not a serial so I cannot use that. Ideas? tia

Hello there, everybody. I'm trying to use the Get(FoundCount) calculation function to record the result of finds. I have a layout that shows records from a table called ClinicalProvided, but it's a search layout that also searches on fields from related tables. If I put a global field called FoundCount_G to calculate the found count in the ClinicalProvided table it always reports the total number of records. The same happens if I put a global field in the table where the search fields are recorded. Am I clear? I'll experiment some more and get back to you with more information.

Hi there, I am trying to have a field in table 2 reference and take on the value of a field in related table 1 whenever table 1 changes in order to dynamically establish another relationship for table 2 on the right side of a different table occurrance. I know I can't use a calculation field for the relationship, so I though that maybe a number field with an auto-enter calculation would do the trick, but it doesn't. Any idea how I can have the field in the right-hand side of a relationship take the value of a field in the left-hand table as it changes without making it a calculation field? Or, perhaps there's a way to use an unstored calculation field on the right-hand …

Hi all - new to this forum and (mostly) new to filemaker I am trying to do the following: I have one reference table with values like this BatchID; SerialID; Start; End where BatchID is a serial number for a set of entries, SerialID is a unique ID for each entry and Start and End are integers that specify a linear coordinate for the record. E.g. BatchID; SerialID; Start; End 1; 1; 100; 200 1; 2; 1000; 1500 2; 3; 110; 230 I now want to use records from a second table and find out whether a given value in the second table has coordinates overlapping with those of a record in the reference table and if yes with which batch. E.g. for the r…

Hi all. First time poster, new to FileMaker. I have a table containing 350 records. One of the fields—let's call it "Bar Code"—contains data that is displayed as either five numbers (e.g. 01234) or ten numbers (e.g. 0123456789). What I would like to do is automatically insert a string (such as "ABC") before all the 5-number bar codes, excluding the 10-number bar codes in the process. For instance, "01234" becomes "ABC 01234" but "0123456789" is left alone. How can I apply these changes to existing records? Thanks.

Does any one know of a way to determine if an image reference stored in a Container field is valid? And.. I'd like to do this without a plugin if possible. More specifically.. I have a table of images where the image container is a calculated field and its formula is something like: myContainer = myPathName and myPathName contains a text value like: "image:Images/ITM_000038.jpg" So the pictures are not imported. I just put them into a folder called 'Images' in the same folder as the database and FM loads them automatically when needed. The problem is, I would like to write a utiltiy script to step through the database records and flag those who ha…

I think this is an easy one but I cant seem to get out of y own way today. I have a table of shortage causes. there is a summary field counting the occurrences of each shortage type and another field defined as a calculation to get the total # of records defined in this table. I have a corresponding report layout made up of sub summaries. I want to be able to display the types of shortages and show the total number of each and the percent of the total. The summary field takes care of the total # of occurrences but im having trouble defining the percentage calc. Any thoughts?

Okay, here is a simple version of my problem. I have two fields on TableA, which are related to TableB. The Fields show: TableB::number TableB::date What I want on TableA is basically: If TableB::date < Today's Date - 60, don't display the value of TableB::number. Instead, display 0. Can this be done without adding a new calculation field? I'd much rather have it be the original field, just with the view formatted to show something different.

I have a separate table with only 60 records, each with 2 container fields and (2 text fields to establish a relationship with another table). The container fields have small jpeg images, only about 50KB each. Another related table has about a thousand records. With only about half of the container field records filled the file size jumps to almost 300 MB, from only 15MB with no container records. When I deleted all the jpegs from the container fields the file size dropped back to 15MB, I added one small jpeg in one of the records and the file size jumped up 5 meg! I thought perhaps the relationship to the 1,000 record table was multiplying the jpeg data, however, ta…

20/03/2008 4:12:08 PM jdoe Moved to Users Hard Drive 20/03/2008 5:08:02 PM jdoe Moved to Finished Items I have a field that has 3 paragraphs. First paragraph is empty. The field is not a repeating field. How do I extract the times from each of these paragraphs and place them into 2 time fields in order to perform a total calculation. I know I should have put this data into a separate table.

Hello all, I am working on a calculation that will present a percentage in a field based on a radio buttons 1 thru 5 and NA selection. (1 being excellent, 5 being poor) NA would just be a "0". There are 4 sections, each section containing anywhere from 3 to 6 questions each. What I have to determine is the percentage of satisfaction in a field. So I am able to break down the questions worth (in percentage) as such: 1 = 100 2 = 75 3 = 50 4 = 25 5 = 0 NA = 0 So for example, I have 3 questions and the person rates a "1", a "2" and a "2". So 250 / 3 would be 83.3 percent. I am trying to get the percentage field for that section (each…

Hey all, This is no doubt a really easy thing to do but it's making me pull my hair out. I need to set up a text calculation that says, basically: First Name & "and" & Partner First (if there is one) & Last Name The idea is to create a Full Name field. Most of the time there's no Partner First, so I need and If function in there, and the "and" to only appear if there's an actual Partner First. That's where I get thrown - the 'if' part. I'm having a heck of a time so any assistance would be appreciated. Sara

I have a problem in defining a field hope someone leads me in right direction. Items are imported to our database. We have 2 tables to accomodate this information.VPN table and MPN table One VPN can have multiple MPNs. VPN and MPN tables are joined using VPN. In MPN table there is a column current cost. Now I want to have a column in VPN table called Target Cost. The conditions for Target Cost: 1.It should take the minimum cost of the MPNs. 2. In any case the minimum cost is changed(current cost of the MPN updated by User) and became higher than the existing one it should retain the old value, otherwise if it is minimum first condition…

I am very new to Filemaker. I am trying to use Filemaker instead of Excel for a few projects and I am having a hard time getting some of the calculations worked out. I am trying to get Filemaker to calculate the age of an item using the current date and a field called "manufacturedate". I got it to display a result in whole years (for example 2.0 years) but I need it to break it down into partial years (for example 2.6 years) I am sure this is easy, but I have tried a bunch of combinations of code and tried using formulas from some of the sample files with no luck.

Is there any way in native FM9 to get the size of an image in a container field? or more to the point... I would like to be able to find all photos that are duplicates. Is there any way to do that without using a plugin to first determine the size of the photo? BTW... my photos were pasted into the database. So creating a calc field of the photo with text as the result doesn't return the image dimensions (as I've seen suggested in other posts). Thanks. John

I have a Timestamp field w/dropdown calendar. When i first make an entry into the field, the date and timestamp are correct. if i update the field, the date will change, but the timestamp will not. the field is also a button that performs a check then uses the Go to field (samefield) step. Does anyone know why the timestamp is not updating?

Hello. I have 3 fields. "Start Date", "End Date" and "Plan". The "Plan" has 3 options "1 month", "3 months" and "1 year". I want that if the "Plan" is "1 month" that the "End Date" be the "Start Date" plus 1 month, and the same with the other options. How can i do it? Thanks

Ok, so I got my first paying gig to design a Contact Management System for a large condominium Association. I need to parse the fullname field into FirstName and LastName. That's simple enough if the data is the same, but, of course it's all over the place. Here's a quick sample: Allen, Craig Kirmayer, Kurt & Gertrude Lane, Jessica & Hunt, Bill & Nancy Platz, Dorcus & Sunjay Bedi Joseph, Drs. Jacob & Alice Nessim, Dr. Wagih Banister, Bruce W. & Karen E. Harter, Beryl A. Okash, Michael/Katherine Athanasiou, M/M Nicholas Joyce, Joseph, Nancy, & Steven Bassali, Dr. Botros & Saod Dickson,David & Kathy/Ipach,Ro…

Hi All, I have a database that tracks sales per sales person per year. All records per company are in a specific "year" field. I have built the first sales person to say If(Year = "2008" and Salesperson = "Brian"; Cost;" "). Now I have create that same field for each year (also adding summary fields per item able to be sold (approx 10) which comes to about 10-15 fields per salesperson (5) per year. Is there an easier way to get this information without having to build all these fields? I am creating a summary Report that will show per sales person, per year, a breakdown of all their sales for that year. Thank you in advance for any help!

Hello All, Thanks for any suggestions you may have for my current problem. I have two global fields that I'd like the user able to enter words in with or without capitalizing and then be able to specify a variable calculation using those global fields, but (and here's the catch) make the variable output Title Case. Here's my attempt to set the variable's calculation using TextStyleAdd - TextStyleAdd ( GlobalBlank1 & " " & GlobalBlank2 ; Titlecase ) With this calculation, I still get lower case words as a result if that's what's entered in the global field. Any suggestions? Thanks again for any help, Five Fingers

How would I round up to the nickle? In excel it is: =CEILING(cell#,0.05) How could I do this in fmp? thanks

I am creating a database for calibration of instruments. The instruments need to be calibrated every 1, 2 or 3 years depending on the instrument type. I want to have a field that will display "DUE within 60 days" when an instrument is due for calibration in the following 60 days. Another field would display OVERDUE if the instrument is not calibrated by its due date. I have tried creating a field that contains the current date using Get (CurrentDate). Then using this field in calculations to prompt the given messages but this does not seem to work. Could anyone show what calculation I should be using or is their is a simpler way of achieving this operation? …

In Database A I have a calculated field (text) called FLName_Birthdate. This literally puts the first name + space + last name + birthdate. In Database A, it looks like this: sam jones09/01/1980. In Database B, I have the same calculated field, but I can't get the day and month to have leading zeros in the calculated text field. Instead, it looks like this: sam jones9/1/1980. Everything looks exactly the same in both databases. Any ideas?:)

Hi all, I have read and reread This Thread to try to figure out a possible solution for converting a cdate to an actual date. This thread made sense because I understood the numbers. But I am stumped because my cdate doesn't make any sense to me and I am hoping it might make sense to some of you more experienced developers. Cdate 1185444436 =Thursday, 26 July 2007 Cdate 1185590426= Friday, 27 July 2007 How to I make that a readable date? Thanks !

Once again FileMaker fails to fix a serious bug that consistently causes a crash of the application. In the Windows version of FileMaker versions 6 to 9 importing a sound file (.aif, .wav) into the container field invariably causes a crash (a conflict with QuickTime for Windows.) It boggles the mind that FM Inc. still has not fixed this annoying, dangerous, and well-documented bug. Shame on you, FileMaker Inc. :)

For some reason, my calculation is not working, All I want to do is have a field that will have a 1 if my InventorySold::Serial Number ≠ Serial Number and a 0 if it is not. Below is what I came up with but I must have missed a ) or or ; along the way, any help? Thanks - Jeff Case ( InventorySold::Serial Number ≠ Serial Number ; 0 ; InventorySold::Serial Number = Serial Number ; 1)

Is there a way to timestamp a record based upon when a particular field has been modified? I know that I can automatically update a time field based upon creation or modification of a record, but I want this timestamp to constrain itself to when a specific field has been changed. We create memos all day long within a record but only periodically move the record to a different production status. I only want to timestamp the record when the particular field "Status" has been modified. Any ideas? Jarvis

Hi, I know this may be an incredibly stupid question but here goes: I have a list of products with various models and unique serial numbers (manufacturers not filemaker). In this table a model may appear several times, I want to be able to count how m any of each product is listed (ProductA, ProductB, ProductB, ProductB, ProductC, ProductC, would return 1, 3, 2). I can't for the life of me figure this out. This would allow me to keep tabs on the number of each product in stock. Thank you for your patience. Tom

I have a custom login screen as part of my IWP solution. I want the "password" field to fill with "*" as the web user types into it. The closest I've come is with a second calculation field called "password_display" that converts the text to asterisks, but that only happens when the user commits the record. Is it possible to get a realtime conversion of the characters as they type? Or is there a font that I can use that is made up of only one symbol that would repeat for every character. If so, would having this font on my server suffice, or would every web user also need the font? Thanks for any help you can offer.

I'm updating an old FMP 4 database that used to be used by a single user. The database keeps track of food orders. I used to have a layout with all prices (set annually)for items contained in global fields. This database will now be hosted. My understanding is globals will hold as set before the database is hosted, but the users will not be able to change prices without working off the server. Is this correct, and is there a better way to make this information available to all tables.

Maybe the title gets you consfused. There are 3 fields, 1st text, name: Name 2nd Number, name: money 3rd number: name: Total money of current user This table record a few people's income, for example, Peter 30 , tom 40, peter 50, lina 30, tom 20 I want the 3rd field show the current user total sum money, here after I finish peter's 1st input, that record should be 30, after inputing 2nd time 30, the both peter's fields should be 80. Just show in real time that how much money the same people's money field sum. could any one let me know how to implement the "total money sum of current user" field please? Thank you very much …

I want to generate a unique login from a full name (first, middle and last initial). If the Login exists (or the middle initial is missing), the middle initial should be changed to X; if that exists, change to Y; and then Z. I am able to assign a variable to the first concatenation and assign to the Login field; however, I don't know how to test it against the existing entries for the Login field. If I set the field to be unique, the validation is passive and doesn't allow a calculation to change the field. How do I accomplish this? Thank you.

I have a booking system where events are booked on a particular date at a particular time. The events are often late in the evening and end early am the next day. I need to ensure that an event is not booked for the same venue at a time that may conflict with an existing booking. The problem I am finding is the fact that 3:00 am is later than 10pm the night before. I need a way using a relationship to catch potential overlapping times. Could anyone help please, it's doing my head in and I'm sure there is a straightforward solution?

As I develop a big solution, I often find myself doing searches in raw tables for various debugging purposes, which often triggers the auto-indexing for that field. Once the solution is tested & debugged, however, many of these field indexes are no longer needed and are probably causing performance drains in use. How do you deal with this situation? Manually turning off field indexes? Is there an easy way to turn off ALL field indexing, leaving the ones that need re-indexing set to auto-create so they will regenerate when needed?

I'm sure there is a simple solution but here is my situation. I have a couple calculation fields that I need formatted to 2 decimal places. I have them formatted in the layout but I didn't realize that because they are calculations fields, they are not being outputted in the actual calculation to 2 decimal places and it is screwing up math later on when I'm trying to use them in currency formatted fields. So basically how to I format a field that is a calculation to output a number to 2 decimal places. Any help would be appreciated. Thanks

I hate to say it but I think I asked this one before but can't find my post or where I used it I have an inventory solution where I need to round up from selling units to an increment of buying units. For example: Paint Cans sellingUnit is by the can (1 each) buyingUnit is by the case (1 case) = 12 each buyingQuantity is by the pallet (105 cases) = 1260 each So: when I need 1 each, I need to purchase 105 cases when I need 1261 each, I need to purchase 210 cases, etc. Thanks in advance for any help.

Hi Everyone, I am new using the filemaker script/calculation, I don’t know to validade a field if someone click at the “CustomerNumber” field a message box should show up and asking “Are you sure you want to make changes at this record?” If they click “OK” it is allow to make change at that field and if they click “No” changes are not allowed. Any help will be appreciated and thank you in advance. Sandy

Greetings, I have three (Number) fields: Field1, Field2, Field3 that track Cost. I am looking for a calculation that will accomplish this logic. If there is no value in Field3, Field2. If there is no value in Field3 or Field2, Field1. Any suggestions would be greatly appreciated. Thanks in advance.

I need al solution for a count of all possible values of a field. In the example file there are only two fields (Id, name) and 18.000 records. I reality I have 200.000 records and 20 fields. I need to make graphical representations of the number of occurrences of (almost) each field. For instance: time > how many at 01:00, 02:00 etc place> how many in NY, Dallas, LA, Chicago day> how many on monday, tuesday etc Can someone give me a solution using the sample file below. The sample file has a count function for the preview. But I need to process the results, so preview is not working for me. Yupp test.zip

Hello, I've got a non-editable field, FIELD, with a dropdown arrow that is a button. The script checks another field, OTHER_FIELD, and if OTHER_FIELD = "X", it won't allow you to edit FIELD. If OTHER_FIELD is not X, the script tells it to go to FIELD where the dropdown can be initiated and a value selected. Problem: Have to click twice The first click will initiate the script. But, I have to then click on the arrow to see the dropdown options. Desire: IF OTHER_FIELD not = "X"; Go to FIELD and make the dropdown drop down all in one click?? Thanks!!!

I feel kind of dumb even asking but I need too. I have a startfield and and endfield. I need to know what percent the endfield is of the startfield. startfield = 50 endfield = 25 I know its 50% but i'm not sure how to write this in FileMaker TIA

My brain is foggy today and am reaching out for help. How do I calculate the date (dd/mm/yy) for the first day of the month previous to the current date? Examples: Current Date = 6/12/08 Result 5/1/08 Current Date = 1/20/08 Result 12/1/07 I will be writing a script to find all the records in my database for the previous month. I have found custom functions for calculating the last day of the previous month but am stumbling on the best way for getting the first day of the last month. Thanks to all who reply Rich :)

I have been trying to figure out how to find the difference between 2 numbers. Example: +108 and -124 = 32 This is the difference between the two numbers. It seems to work if the numbers are the same like two positive numbers or two negatives.

Hi All, I have a portal that holds records driven by year. I have a separate database that holds my sales items and pricing. I am trying to create a calculation that will find the word AD in the sales item field. The field that contains the word ad has different text following per record. ie. AD Full Page, AD Back Cover, etc. How do I find the records that contain the word AD? When I try to use = AD it doesn't work.

Despite the enormous and varied online resources for learning FIlemaker, I've yet to hit upon what I most need: a workbook that teaches Calculation functions by providing a series of increasingly sophisticated real-world calc tasks that the reader can work through, thereby gaining familiarity with the various functions. The earliest tasks would be very short and simple and many would use the same functions that are of most common use, while as you progress, you'd be introduced to more and more obscure functions and longer calculations. The vital thing is that each example pose a real-world scenario that challenges the user to select the needful functions. If…

I have a time field (Time OnCPBP) in which I can set a script to enter the current time; however when I want to enter the military time manually, I use the Auto-Enter Calculated value specified as: Let ( [ TheText=GetAsText(Time OnCPBP); Reformat=(PatternCount(TheText;":")=0); RawNumber=GetAsNumber(TheText) ]; Case(Reformat; Time(Div(RawNumber;100); Mod(RawNumber;100) ;0); Time OnCPBP)) This lets me enter the four digits and it inserts the semi-colon for me (much easier) for my users.... Problem: I have another time field (On CPBP #2) in which the calculated value is specified as: If (Event =…

There is a Calculation involving 6 Fields I do not seem to get the right combination to work. I have a First Name and Last Name Field A Spouse Field and a Spouse Deceased Field and a Deceased Field. Case ( Spouse > 0; First Name & " & " & Spouse & " " & Last Name; Spouse > 0 and Deceased Sp = "" ;First Name & " & " & Spouse & " " & Last Name; First Name > 0 ;Full Name ) I cannot get it to display correctly I need the first name last name if no spouse First name & Spouse Name if there is a spouse "I get this one to work right" I then need a calc to give the first name last name if…

Hello, I'm trying to add to the functionality of this database so I'm working with pre-existing tables and trying to figure out the best way to do this. Simplified: 2 Tables: TASKS and USERS TASK_ID is unique and is realted to USERS. (one to many) Multiple users can be assigned to a single TASKS. However, right now there is a calculation field, CURRENT_USER (Last(USER::USER_ID)) within TASKS that pulls up the most current USER from the USERS table that has been assigned with the particular TASK_ID. And only this person can access that TASK_ID. Is it possible to make this CURRENT_USER calculation to result in something like: USER_…

I have a table that lists the basic details of all the invoices for a certain customer. Basically, I have a column for the date, charges, credits, and the running total. What I need is for that running total to update on each line based on the previous total + charges - credits, but in list view, I'm not sure how to set up the field to reference a previous version of itself. Any help would be greatly appreciated. Thanks.

Is there any way to set a flag on a record when a certain field is modified, without a script? I want to flag a date change. When I use flag=Evaluate( "1", dateField), it'll set the one if I enter the field, but do not change the value. I need to capture only a true modification.

I have a couple of summary fields calculating in a subsummary part, but when I do another calculation on the two summaries they only find the answer for the grand summary. eg i want to calculate the ratio of room capacity to room size sorted by the subsummary of "Faculty". Each room has its own ratio but an average of those ratio doesnt give the right answer. thanks

Length ( containerfield ) calculation returns the file size in a container. Files stored as a reference aside, the file size of a Quicktime movie stored in a container seems to be wrong (way too small). So what file size does it safely return, image, stored file… ? Anyone know definitively or should i try every combination, think i will probably get it wrong and would like to be sure. best Stuart

I'm using FMP 4.1 (don't ask). I have a date field in my database and I'm interested in a calculation field formula that would check the date field and tell me how many days have passed from the earliest dated entry (record) to the latest dated entry. My calculation field is called "Days in Service". I want to know how many days a car has been in service. I've tried a formula like: =Max(Date)-Min(Date) I've tried several versions of this. A few of them yielded a number, but it was not the number of days in service. I need a formula that will work and has the proper syntax. Thank you

Hello, I'm trying to make a calculation for sales tax for an appliance business. We live on the border of michigan and indiana so there are different sales tax scenarios. I need a sales tax field to know how to calculate based on the following scenarios: If an item is being delivered is being delivered to indiana (IN), then subtotal sould be multiplied by .07 (Indiana sales tax). if not, it should be multiplied by .06 (MI sales tax). I have something like this but it doesn't work. Any help? if isempty(Delivery?) and Customers::City = IN then (Subtotal )*.07 Delivery is either yes or empty in case you are wondering. Any help would be appreciated, thanks…

HELP!! I thought I'd had this little problem sorted awhile ago, but have been running into problems. Maths was never my strong point!!! I'm trying to get the Time in Leu (TIL) earned at work, attached is a dummy database I've setup. I've managed to get it setup so it provides a running total of the current TIL, but we want to cap the amount of TIL a staff member can earn to 32 hours. Once they've earned if they work overtime it doesn't get added, but if they take time off, it comes off!! Hope this makes sense. TIL.zip

I am trying to extract data so I can summarize it by months and also by years. I have created two fields one for months the other for years the first works and the second does not. This is the definition of both fields can anyone see what I am doing incorrectly please. MonthsWellsTran =Case(Get(calculationRepetitionNumber)=Month(Extend(TDate));Extend(Revenue). This has 12 repitions for the months. Figured this out it was the case test that was incorrect. YearsWellsTran =Case(Get(calculationRepetitionNumber)=Year(Extend(TDate));Extend(Revenue).This has7 repetitions as so far only have data for seven years. The month one works fine but for some rea…

Hi There! i've just started with FM and im working on a stamp database atm. i've got 3 fields, with 3 different prices on one stamp, then i have a field that uses the average calculation so i have an average. what i want now, is a field that calculates the sum of all averages in all records, so there is a globalfield that is allways updates with that value. Im kinda new, so i hope im makeing some sence : Thanks i advance!!

I am now working on another database, and this time, I need help on a simple calculation. lets say I have 3 Fields. One fields called "Rent", another "Commission Percentage" and the Last, "Commission amount". What I want to do is to be able the enter in the rent, under the "Rent" field, maybe $1000. Then, I would enter the percentage that I would receive as commission, under the "Commission Percentage" field, 5%. Then, the calculation would kick in, and figure out my commission based on the percentage entered in under "Commission Percentage" Field, which would be $50. Any help is appreciated

I have a looping script to extract a series of words from some raw text. Sample Text: tagWORDtag2 tagWORD2tag2 tagWORD3tag2 I should be able to extract, WORD, WORD2 and then WORD3 in three iterations of the loop. I set the variable "$num=$num+1" before each iteration. I am trying to use a calculation to extract the text as follows: Middle ( Fieldname ; Position ( Fieldname ; "tag" ; 1 ; $num) +3; Position ( Fieldname ; "tag2" ; 1 ; $num) - Position (Fieldname ; "tag" ; 1 ; $num)-3 ) The first iteration does get the first occurrence, "WORD", each additional iteration is returning a blank. Any suggestions?

Hello there. I'm trying to make the accessibility of one field to data entry dependent on the contents of another field. In one field of the same table I have test type. If the test type is "Array" then I want to be able to enter the array type into the "ArrayType" field; otherwise, I want the "ArrayType" field to be locked or empty by default. Please share any ideas you may have. Thanks!

I have a database that is hosted on a remote web based server. This database has a container field that holds PDF files. I am able to insert (embed) the PDF file and I can see it from any client station but I cannot open it. How do I make this happen?

I am stuck on this... The data i have consists of 3 fields. Code is an identifier Views is number of times a "code" has been seen Type can be either A or B Code Views Type 001 5 A 001 5 A 001 3 A 001 7 B 002 3 A 002 1 A 002 2 B 002 5 B I can work out the total "views" for each "code" using the SUM function across a self join (code to code) e.g in the example above, "001" has a total of 20 views (5+5+3+7), "002" has a total of 11 views (3+1+2+5). What i want to do is find the totals for "code" and "type", s…

I have a field that I would like to add dashes in-between certen text. The layout will always be the same, example 52-526T-A-652. This way I would not need to store the dashes.

I originally developed this file in 8.5 Adv but recently started using 9 Adv for maintenance and new development. I have an older database, (dev'd in 8.5), with a date field where you can enter a date such as: 5/19/2008 and it will display as May 19, 2008. This display is okay but the strangest thing happening is that this field is somehow locked into displaying a wrong date! It is showing "May 8, 2008". I can enter and change the date in the field but it always displays "May 8, 2008". Any ideas what is happening? :

I'm creating reports of Line Items. Line Items contains 3 fields: Amount Billed, Amount Paid, and Percent Profit (=1 - AmountPaid/Amount Billed). Line Items also contains 5 fields of Worker, Client, Worker Type, Client Type, and Date. I want to create reports that will summarize the data for the first 3 fields depending on the sort order of the other 5. I'm halfway there, but stuck. I've got a single layout, with 5 sub summary parts. I have a summary field for Total Billed, and for Total Paid placed in each of the summary parts. That all works great. I'm getting the proper summaries depending on the sort order. It looks like I can't get a true cal…

Currently we are using the default serial number configuration in a field (log number) so it looks like: 08-001 ('08' being the year prefix and '001' being the incrimental serial number) Is there a way to turn this into a calculation so I can use a global field for the year prefix (that can be changed via our configuration layout) so the users won't have to modify this serial number field each year?

Hi I have a field, that returns a calculated value for a person’s age, using the calculation: (Date of procedure - Date of birth) / 365 I would like the number in the age field to turn (for example) red if the persons age is over 70 yrs old, or under 1 year old. I have tried adding: Case (persons age>70; TextColor (persons age ; RGB (255 ; 0 ; 0 ) ); Evaluate ( persons age ) ) But my attempts fail, mostly FileMaker says there is a “operator missing” Could someone please help with the next step to follow my existing calculation? Thanks

I am having trouble figuring out how to count records in a portal that have a "checked" box. I have a check box field called "reserve" in a related table record shown in a portal. I need to be able to count how many of the records in the portal (related table) are checked and show that total in the main record. I have come up short so far. Thank you for any ideas.

Good afternoon, Looking to find out how to create a calculation field that: 1) Will show the total records that meet a certain criteria based on a value field (i.e. if 15 of 100 records say 'Active', the calculation will show '15'; if 50 of 100 records show 'Closed', it will show '50', etc. 2) Also, is there a way to do this in a related table. For instance I want to create a 'splash' or start page in the database that will show these counts based on the main table. Too much? Thanks!!!

This is probably an easy one, but is giving me fits. I can get it done using a long series of subs etc., but there has to be an easier cleaner way of doing it. Floor(Random*587)+1 which of course will produce anumber like 45,4. and need it to show 045,004. Need it to always pad it with 0's.

hello i have two fields and i want to make one of it empty based on the other field. lets say if A is selected / filled [date type] then i want to make B blank and if B is filled, i want to make A blank. for now i have this piece of code which only makes one field blank Field A = Let(Trigger = Field B ; "") if i put the same code in field B and change trigger = B to Trigger = A, it makes both fields blank and then you can't fill any of the fields as they both cancel each other out. is there any way to make one field blank at a time. if A is filled, B is blank, if B is filled, A is blank. thanx in advance. NOTE i have come up with …

How would I set up a calculation too give out a frequency? Every four months from the start date a certain event will occur? In other words: If the start date is 1.1.08 and current date is 2.1.08 the field would display 0. If the start date is 1.1.08 and current date is 4.1.08 the field would display 1. If the start date is 1.1.08 and current date is 4.25.08 the field would display 0. If it is too difficult to setup the calculation due to months having different number of days, including the leap year the frequency of every 30 would also be fine. Thank you.,

I just discovered that PatternCount() doesn't check for overlapping strings. For instance the calculation: Let( list = "1¶2¶3¶4¶4¶3¶3"; PatternCount(¶ & list & ¶; "¶4¶") ) returns 1. Threw me for a loop when I was trying to get a count of a specific value in a list. So, Let( list = "2¶2¶4¶4¶2¶14¶2¶41"; PatternCount(list; 4) ) returns 4, but I want the result (for both examples) to be 2. As I type this I realize FilterValues() and ValueCount() will do what I want. But the PatternCount() thing is still weird isn't it?

Need help in converting numerical value to text (IP address). FM database has an ODBC connection to an external data source. IP address field in the external data source is a numercial equivalent to the decimal value calculation of an IP address.

Hello everybody. I am trying an age calculation. As result I only need years. However, the dates should compare only months. Means, the person should become already a year older on the first day of the month of the birthday, and the birthday should be compared with the creation date of the record, giving me the same result whenever the record is created (first day of the mont, or last day of the month). Any good ideas out there?

I would like to summarize invoices by client on a monthly basis. Essential I need a column for client then column for 12 months and finally a total. It follows I need to add up each clients invoices for each month. I would then have a row for each client. I could export all the records to Excel and use a pivot table to do this but that is very clumsy and I wondered if anyone had any suggestions. Thanks Mitch

Hi, I've been unable to work this out. I want to work out a price change after a certain date. eg. After May 19 2008 it has risen from $16.00 to $16.50 I tried IF(Date1=>"5,19,2008";16.5;16) and other more embarrassing attempts which I wouldn't dare to put up here! With many thanks, Charles

Thanks for all of your help so far, now if I may ask just one more thing. I have a report with the following that does exactly what I want: Sub-summary by boarding (Leading) Boarding Location - <> <> That shows the boarding location (3 of them chosen from a dropdown list) and the number at each location. I would like to have the same info (the qty at each location), but on the reservation form. Just a summary of each location and number, no details. I would like it to appear as follows: LA Union Station # Fullerton # Oceanside # My question is - How do I get the numbers for each location? My layout is below to see what…

I have the following calculation to express a percentage in a cross-tab report: GetSummary ( Crosstab value Summary ; Breakfield ) / GetSummary ( Total values Summary ; Breakfield ) I have had to use a calculation to express the summary because using a fraction summary field does not work when the numerator of the fraction is a crosstab result and the denominator is a subtotal (unless someone here knows better). The problem is that often the Total Values Summary will equal 0 when sorted by the Breakfield and this gives me a calc error (I get a ? result). I did this to solve the problem: Case ( GetSummary ( Total Values Summary ; Customer ) ≠ 0 or "" ;…

I have a Price field that I would like to auto-calcualte based on which answer is chosen from a dropdown list. I have a Member field, which is either a Yes or No, chosen from a dropdown list. The Member cost is $35, the Non-Member cost is $40. I would like the Price field to auto calculate the price based on either the Yes (Member) or No (Non-Member). I know I start with price= in the formula, but which would be the correct stament to make the choice? Thanks in advance for your help.

Hi all, How do I auto create new records based on a number in a field. i.e. If the field has a four in it then I want to create four new records?: Looping?

Dear all. I hope I can get some help here... I have two databases, they are related. In the first one: 3 fields per record (start date, end date, value). These records define a certain value for a certain period. In the second database: 2 fields (month date, value) What I like to achieve is to input a month (date) in the second database, and then the value should be looked up in the first database: > if date in the second database is in the range (period) of a record of the first database, take the related value of that record. Of course I managed to look up for the value, but only of the first record of the first database. Any help would be …

I am a little confused on how to do the following..... I have the following fields: reservations price total_reservations available_seats total_seats income_total income_needed payment_received 1 - In the 'total_reservations' field, I would like the sum of all of the 'reservations' fields. 2 - In the 'available_seats' field, I would like the sum of 'total_seats' minus 'total_reservations' field. 3 - In the 'income_total' field, the sum of all 'price' fields. 4 - In the 'income_needed' field, the sum of a set number ($1100.00 for example) minus the 'income_total' field. 5 - A PAID logo to display if the 'payment_received' field…

I need to make a excel report that counts the number of case per state. I need this for all 50 states. I can do this for one (1) state at a time on 50 different reports; but How do I do this with all 50 states on one excel report? Thank you, Joseph

Hello all. Here at my workplace we use something we call an accession number for patient samples. There's a manually applied labeling system that uses a six digit number that is sometimes followed by a lettered suffix. The lettered suffix usually indicates that the number has been used before on a relative of the current patient; i.e., it's used to relate the mother's sample to the child's via a common number but with a suffix denoting the relationship. Anyway . . . I want to write a calculation that will remove all letters from the aforementioned manually applied ID number. The output field values can be used to relate samples to one-another, probably through a…

Let me first apologize if this post is in the wrong category. I am trying to figure out how to include a notes field that uses a button to insert the current date and places the cursor next to it ready to go. This part I can figure out... What I can't figure out, is how to create this notes field as a running entry with multiple, dated notes with new entries above the last entry. Every time I insert a current date it overwrites the previous note / field contents. Should this be a repeating field, a portal, or something else? Your assistance is greatly appreciated, thanks. -Dgee

I have a problem I hope someone can help me solve. I have a table that I log the odometer miles of my motorcycle into every time I have a service or issue with the bike. I would like to create a report that shows the average miles in between issues, but I'm not sure how to go about it. Any ideas?

I need a field that is a sum of sales for that day on a layout. Right now I have a summary field giving me a running total but I need to have the same value across all records for that day. Any suggestions?