Calculation Engine (Define Fields)

I am stuck on a calculation and need some help. I have created a calculation on a date being show 4 weeks before a certain date field. eg: ="Date Due" - "Call Date" But what I want is a calculation showing that if, the call date is past the current date, then the formula equals = "Current Date"+4 I am not sure how to do it via the "if" function. Thanks In Advance

I have a repeating field that have created a calculation for. The calculation basically says Case(Incident type = "Theft"; 1;"") this would return a value of 1 should the incident type be theft. I then have a summary field that totals the entries for the incident type that = "theft" However this is a repeating field. Reason being that in addition to theft the incident may also include burglary for example. Is it possible somehow to have this field report both events to the summary field or is it as I assume... that only the 1st entry in the repeating field is going to show in the summary. if thats the case I'd be happy to hear any suggestions to remedy my dilemma. Tha…

I have two fields: Networks and Servers. There can be many Servers which all belong to the same Network. But no Servers belong to more than one Network. Each record I have looks something like this: Network Server A a A b B a B b B c C a So what I'm trying to find is -- using a Calc field if possible so the data can remain constant, i.e., not dependent on summary layouts -- how many Servers does each Network have? (In this case, A=2, B=3, C=1).

Greetings, I need to get the values on the left and right side of a decimal. I have the left side figured out via the truncate function, however, how do I get the value on the right side of the decimal? Left Side Calc=Truncate(TOTAL_COST, 0) Ride Side Calc= Any help would be greatly appreicated. Thanks in advance!

I have instances where I would like to use an alternate name for a field. My plan is to set up a calculated field that simply lists the primary field as the "calculation". Is there is a downside to doing this? Is FileMaker smart enough to recognize that it's a simple equivalence? Is there a better way? Thanks in advance for any advice.

Hi, I'm stuck with a ormula and although I can get it to work using loads of if statements I'm hoping there is a easier way. Basically as a practice project I want to make a program that can work out the best poker hand from data entered. I've got all my fields set up (flop1, flop2, river, etc) and I have used the case statement and lots of AND and OR's to get it to pick up pairs, trips, etc but I'm thinking there must be a easier way than listing all possible combinations? Also I can't think of how I would use this method to pick up the straights, etc. So far I have not included suits. just numbers. Any ideas?

Hi all, this is my first time posting here, and Id like to say hi to everyone as this seems like a really friendly group, and im thankful this site exists. First the background...Ive been using filemaker for a bit and have used it to run simple sports stats without problem (wins, losses, points. etc..) Now I have been asked to create a database for work that tracks staff stats (I work in a callcenter) I have built a database that tracks all the stats individually for each week, which all pools into a layout for the quarter, everything works fine if you want to see what a certain tech support agent has done for the week, month or quarter.. but where Im lost is ho…

Hi, I am a complete novice with Filemaker, so please forgive me for asking a question that is probably very simple to answer. I am trying to set up a database for a research project. Basically, we will be entering a record for each time a behavior of an animal is observed. There will be several hundred animals, for each of which there will be many observation sessions. In one observation session, there could be 200 total occurences, each of which could be one of a 10 different behaviors. My inclination was to have one record for each individual tied to records for each observation session, in which I would define several repeating fields (time, behavior, et…

A customer decides what items they want. From a customer database a new job ticket is created and all of the items this customer wants are entered via a portal. As the items are entered, fields in the portal are populated with information from another database about the item such as price, color, size and category. The category may be wood, metal, plastic, glass, or paper. The order that the items are entered is critical for assembly at a later date. None of the categories are really important except for the paper one. What I need is a (auto entry) counter so that everytime paper is the category a (field named paperpostion) would populate with it's position (starting with…

Hi, I have a calculation set up to show the state of a checkbox (True or False) if a container is populated. Now I need to alter this slightly, I need the checkbox to show False (off) if; has been pasted into the container and True if; C:windowsdatafilesassetsxyzfoto.png has been used instead. How do I reference these external files correctly? My current script is; If (IsEmpty(Foto) ;"F";"T") ...which works as normal. Thanks!

Hi, I would like to create a script that upon opening the application the following occurs... 1) If the date from a "Est. Completion Date Field" is more than 60 days old, then the field named "Project Status" is changed to read "outstanding". I'm new to FMP so any help would be great... trying my best to learn on the job. Cheers, Dan :

Sirs, how would I do a calculation for the time difference between two entered fields, such as Start Time, End Time? I have tried to make such a calculation, by making the start and end time values TIME, but the result makes no sense. It thinks the time value, for the 24 hour clock, is minutes. It will not display the time normally, but puts the : mark after the minutes and then adds seconds, thus 1630 is displayed as 1630:00, which must have something to do with the calculation. I cannot enter the times as numbers since the calc will be totally wrong. I do not want to use the 12 hour clock, but will if this is the only way to do it. And if the two times are on diff…

I see a lot of great posts here, but none that address my issue. The main "problem" is that I am new to FileMaker (and databases in general) and I have to tackle the reservation, invoicing, etc project created by a former employee. They did not complete the project but it is usable with some limitations/errors. The first issue: We have a 'booking number' field that is calculated using the 'arrival date' and first 3 of 'customer's last name'. The 'arrival date' is entered via calendar. Problem is that if the month and/or day is a single digit, the 'booking number' field will have slashes inserted in place of the number. If I setup the calendar to insert lea…

I require a field that always shows the current date - I have tried a field with an auto enter calculation of Get(CurrentDate) and set to always validate but this is not working. Can anybody show me where I am going wrong. many thanks

Hi All, is it possible to calculate the average price from the last three purchase orders for a stock item? I'm just not sure how to obtain and evaluate just the last three prices. Additionally some low use items may only have one price recorded for that item which makes it a bit harder. Thanks Peter :-)

Our company makes vitamins. We have lots of powders and keep track of the batches used in production. The idea is that the mixing in charge scans the barcodes of the batches into a field. Each barcode is always a length of 8 characters. If he scans 3 barcodes, the field will be 24 characters long. I am trying to create a function that will reformat the text like so from: xxxxxxxxxxxxxxxxxxxxxxxx to xxxxxxxx, xxxxxxxx, xxxxxxxx I cannot get the function to work. it would be a text field with a calculation to reformat the text. Any help is appreciated.

I have been struggling for over a week to solve this problem and I am hoping somebody can see my problem. I am trying to get a beginning balance calculation to work. I have a doantions table, disbursement table and a cause table. The cause table keeps tracks of what a donation was given towards. I need to get a calculations that will show me the beginning balance for each Cause. This would be Donations minus Distributions for each Cause Code (ProjectID). I have created a calculation that shows me the beginning balance for donations in one field BegBal_R. This is based on what reporting month the user selects. So if they want the beginning balance for January 200…

Here is an interesting "thing" I ran into. I have two related tables. Some of the fields in table two, upon record creation, are to be given the existing value from fields in the related table one. The fields to use are in the calculations for the fields in the database info. This works great if adding the new record for table two through a portal on a layout assigned to table one. However, if adding a new record to table two on a list layout assigned to table two none of the fields are given a value. I have to use a script to enter the values before commiting the record. Can't find anything about this in the manuals or outside books, seems there are a lot…

I have a timestamp in Table1, call it SomeTime and I have another field in Table1, call it Temperature In Table2, I have a timestamp called WeatherTime and a field called WeatherTemperature. It is highly unlikely that I can use an exact = relationship between SomeTime in Table1 and WeatherTime in Table2. So I 'know' that my weathertime(s) come ever hour. I created another timestamp field in Table1 called SomeTimePlusHour. I'm banging my head against a wall trying to use a spiffy relationship to Table2, like ">" SomeTime, but "<" SomeTimePlusHour. So It isn't working for me. Does anyone have advice for looking up tabulated values using a 'close…

This should be simple - but it's mystifying me. field:t_DeptChair? If record is for a Department Chair, the user clicks off "Yes" in this field (Yes is the only choice -- it is left blank if not valid). field:t_calc_Active - this calculation looks at the end date of the record and returns "yes" if that end date is AFTER today's date, meaning that Title is currently active. If the end date has already passed today, the calculation returns a "no" value. field: t_calc_CurrentDeptChair = If ( t_DeptChair? = "Yes" and t_calcActive = "Yes"; "Yes"; "No" ) On the Titles layout, the calculation works fine. However, when I place that field in another, related …

I need to put into a claculation field the contents of two other fields; Field1: NUTS Field2: BOLTS CalcField:= NUTS BOLTS At present, my calc field, which is obviously invalid, returns 0. Many thanks! Frank.

I've got a working solution that allows users to record information for a project (header file) and then record specific tasks with the associated start and end times in the related detail file using a portal. What I would like is for the start time for the next entry to default to the end time of the previous entry. For example, user creates a header record, then puts the first line of detail in the portal such as: Travel, 9:00 AM, 10:30 AM, and any comments. They then tab to the next line and enter Support, at this point I want the system to automatically enter in 10:30 AM as the start time (based on 10:30 AM being the end time of the last task. I've tried us…

For a linguistic study, we will making voice recordings (must be uncompressed .wav files) and would like to use FM Pro 8.5 (for OS X), to store the files and related data. My question: In FM Pro, can we store a .wav file in one field and related text documentation in other fields of the same record? (In other words, the .wav file and it's assorted text documentation would live in different fields of the same data record.)

Hello everyone, Is it possible to define the results of a calculation field to be unique without transferring the results of the calculation to a different field? For Example FullName= FirstName & " " & LastName Result not allowed if not unique. I don't see how to add a valdiation without taking the result and trasnferring it to another field that allows for validation. Am I missing something basic that I should know about, perhaps in the functions? This might seem like an odd request as I think about it, but it is import to the DB I am working on. Steve PS It is not about names...but that was easy to …

Need Help : I lose the value of the global variables when I quit FileMaker!!! All global variables are prefixed with $$… I try in FileMaker 8.0 and FileMaker 8.5 and the result is the same. Why? Tks ================================== In FileMaker Help says: "A global variable can be used in a calculation or script anywhere in a file, for example, other scripts or file paths. [color:red]The value of a global variable is not cleared until the file is closed. [color:blue]Prefix global variables with $$. "

I hope this is the proper place for this post. I have an old solution that includes some repeating fields (i know i know its on my list for redesign but for now i need a work around), I need to allow my users the ability to at least "insert" a blank repetition. The user would define where this blank would be inserted. and in a perfect world the users would be able to rearrange the data within a repeating field. I know im asking for a lot but this is a BIG file (and heavily used) and it would take a long time to really fix it right. If i can provide this functionality it would be a huge benefit to the users. If anyone has dabbled with this sort of problem pl…

I have a basic filemaker template for timecards for employees. wonder how to modify a box that say it is not modifiable. Have tried every trick I know using the layout feature. Anyone know how to do this :

I have a calculation field "deleteButton" that always returns the value "delete". When it's okay to delete a record (according to a set of rules) the value appears in the color assigned to the field. But when it's not okay to delete a record, the value appears in a "grayed out" color. How can I have my Delete script check the color of the value in that field?

I need to make a simple set up of a statement ie X + Y = Z. I would like to just have three blank fields for X,Y and Z. If the user enters any of the two, it solves for the third. The only way I can think to do this is to run an action to detect which fields are not blank and then, calculate the result of the third. Is there an easier way to do this. (I would like to use 6 variables and it gets a bit messy) Thanks

Hi, Here is my problem. I have a checkbox with 5 options. Call them check1,2,3,4,5. and the field itself checkbox_Checklist. Now depending on which checkbox is selected I want to display a second field. So say check1 is selected I want Check1field to display and also want the check1field to display the number 1. So would this be IF (checkbox_Checklist = "check1";"1";"")? Because I have tried that and it didnt work. Any help is appreciated. Thank You, Eric G.

Hi - How do I link a container field (containing a QT Movie Link) to a checkbox field (Has QTMov: T;F)? Many thanks! Frank.

Greetings Fellow FileMakers - I have a field, call it Field1, it's a Container Field (albeit, the same thing happens with a text or any other type field...). When I turn that field into a button, and attach a script to it to look at itself - it doesn't see the data (I run a Show Dialogue after Get(ActiveFileContents) it shows nothing) (or I Set some other field to it and nothing shows up...). I subsequently noticed that there are two tab numbers attached to each buttion field in the layout.... hmmmmm. Side point: If I make one of the two (the first) buttons active from a tab perspective, it seems to get the field contents. When I make both, neither or the second tab …

I'm trying to get FileMaker to print checks. Example: Amount of check: $1,359.37 For the purpose of printing the check, how do I get the amount of the check to also appear as: One Thousand Three Hundred Fifty-nine and 37/100 Is this possible?

I have a database which produces students end-of-term report cards. I have an Overall Comment field which is made by concatenating a Course Outline from a related table and a teacher's comment, which is individual to each student. I also have a Year field, which records which year each report was created. The Course Outline is common to all students following a course in a particular academic year, but will change from year to year. I want to be able to find last year's report cards and see them as they were - Course Outline and all - even if I've changed the Course Outline to suit the updated course this year. However, because the concatenated Overall Comment field…

I have the feeling I am missing something obvious. I am trying to get a field that will have an assortment of letters in it, based on other fields. Sample outputs - None F I P S T D F T ....and so forth My attempt - If( Frog="Yes", "F" , If( Iguana="Yes", "I" , If( Puppy="Yes", "P" , If( Skunk="Yes", "S" , If( Tiger="Yes", "T" , If( Dog="Yes", "D" , "None")))))) I have the feeling I am missing something obvious...anyone point me in the right direction?