Calculation Engine (Define Fields)

I am creating a database for cars. I need a script or function that allows me to shown the following when I input the data: I input: 3VWFE21C04M000001 Then it outputs 3VW FE21C 0 4 M 000001 The VIN number is always 17 characters. Also, the I, O and Q are not to be used anywhere in the VIN so I need somekind of error to shown if these are inputted. The last five digits of the VIN should only be numeric. Position 10 cannot be U or Z If someone can help me with a script for this that would be great as I am OK with the basics of filemaker. Thanks in advance.

Howdy, all. Please refer to the attached file. Goal: I have two CFs, one to calculate elapsed months and the other, years and months. On the surface they work fine except I'm having trouble getting a couple of conditions met. Record 1: Works okay Record 2: Works okay Record 3: When there isn't a date in both Start AND End fields I want the result of both Span_mo and Span_yr_mo to be empty; the former I think I solved but Span_yr_mo...I can't seem to nail the correct syntax for the field's calc to null it. Record 4: Span_yr_mo is correct in that only five months have elapsed (the date span is one day shy of being six months), but Span_mo...again, I…

Hi Everyone! I have to calculate the number of times a person has attended since a specific date. The date since is a field "Last Test Date" and attendance field is "A_DATE" how would i make it do this calculation? Thanks in advanced!

What is the easiest way to compute the highest serial number in a column, in FM10? The serial numbers are of the form P-000002 - that is, fixed-width integer with alpha prefix (thus unusable by the max() function). If sort / goto record is the only way, so be it - just thought there might be a better way. Thanks, Chap

It's been a long time since I have done anything in Filemaker and I am required to again. I have a list of three items that I want the result to be a "X" in a text box depending on which radio box is clicked on from the list. What is the way to write the calculation for each of the boxes that I will need. There are 3 items in the list I am working from. I can get a 2 option radio box to work, but not the 3 option box. Thanks in advance.

In a demo file I have there's a relationship between two tables between two text fields that should equate to equals but it's not explicitly put as such and doing so breaks the functionality of it. What they have is "f1 <= f2 AND f1 >= f2", what does this make filemaker do?

In my babyparking i've 2 cards, one of 30 hours, one of 50 hours. The 30 hours card expire after 5 month. The 50 hours card expire after 10 month. I need to calculate the expiration date jumping the summer's month, july and august. So if you buy a 50 hours card in may, i must calculate the expiration date adding may, june, september, october, november, december, january, february, march, april (10 month). I've a creation date field obviously, but i can't figure out how to make a calculation like that. Over this i must add the year of expiration if i pass december like the example above. Thankyou! G.

As stated in the topic title, I need some guidance on how to retrieve the value a record based on the max value of all the relevant records in the same table. Information about the problem: I have 3 main tables - Status, Agents and Companies. Each agent is assigned to one or more companies; each agent can trigger one or more status change in the company. I am storing all status changes and I have created a StatusID that automatically generates a unique serial number for all status change (regardless of company). Now, I am trying to create a layout that displays the following details: Agent Details like Name, Contact Number etc Information about the comp…

Not new to FM but fairly new to design, etc etc. A little background first: My company has thousands of documents that we use for many different machines, accounts, etc. Currently they are in a disheveled state, mismatched etc. Documents are mostly pdf's but also some .xls and .doc files. What I am attempting to do is to setup a database to better organize these files and make it easier to find what we need. They reside on the root level of c: and it is a folder with many subfolders that contain documents. We use Copernic and/or Google Desktop to find what we need. Neither solution works very well as we get a lot of extraneous hits. My end goal would be to have…

Hi. Im building a database for organizing audio cd's meta data. I have created 1 table with all the album meta data and i have created 1 table that has the data for each track in an album. im trying to do in layout view that i can see all the data in the tracks table of a certain album for example if i have an album id: #1234 in layout view i selected this album then all the tracks that are under album id#1234 will be shown in the same page with all their information... Thanks a lot!

I have a calculation using the position function to scan a field for certain verbiage. The calculation is exactly as follows: If ( Position ( ReportType; "URAR" or "multi" or "SFR" or "condo"; 1; 1) ≥ 1; "Full Report"; If( Position ( ReportType; "Desk"; 1; 1) ≥ 1; "Desk Review"; If (Position (ReportType; "Field"; 1;1) ≥ 1; "Field Review"; "Other"))) In my data, one of the possible field values is "Final Inspection - 1004D" that returns "Full Report" for the calculated field. Can anyone give me some idea how this is possible? Shouldn't it be an "Other"?

Hi Hope someone can help. I have a repeating field which shows jobs done for each customer, with the date done. Average Date Job Cost 7 17/1/11 500 lts £10 2 10/1/11 200 lts £10 7 8/1/11 500 lts £10 75876 1/1/11 600 lts £10 I also have a field which works out the amount of days since the last job done (Jobs::Date- Jobs 2::Date) which works fine EXCEPT for the first Job/date (oldest) which doesn't have a previous date to work with. So results in a figure of 733487 (or near). WHY ? I would like to find the days bet…

I have a container with a script to inset a picture. It is stored as a reference file so updates to the file are automatically updated in FM. Trouble is I need multiple users to insert the picture on Mac and PCs but opposing computers aren't see each others insert. Even using a VMware fusion version of filemaker cant be seen on a standard PC or Mac. Is there any way around the PC/mac differences in the path? thanks

Hi there I´m pretty new to filemaker and I´m searching for a date function that will display the nearest date compared to the current system date ? I have a few date fields (date 1 ... date 50). Now the calculation date field should show me which of the date fields is coming up first and closest to the current date ? Is there a function for that? Thanks a lot, fraank

I'm sure it's fairly simple, but I'm new to this FM stuff, and I have two dates, Move In and Move Out, and I need to calculate the days between two dates. I just need a whole number. I appreciate any help!

Hi. Im trying to create a new database that will allow me to create sales report each month. I have 1 table with my product id and i would like to create another table with the product id's and the amount they sold each month(updated each month). Now what i want to do is that i would be able to create a report from these 2 tables that the 1'st table will be filtered by the product id's in the second tables so finally i can see all the product id's that made selling and the amount of each one... Am i designing the structure right or is there a better way doing this? Thanks

My intention is to be able to search for peoples birthdays between two sets of dates. For example between the dates "Greater than 01/01/NA" to "less than 15/01/NA". How can I search through dates for the day and month, It doesn't matter what the year is. I am looking for a way to have a script which performs a find in the BirthDate field to find the results. Hopefully someone can help me here. Many thanks.

I'm generating a report in which the script goes through several records one at a time and sets a variable to contain values from multiple fields in each record (separated by tab characters usually). Most of the fields are text. But one is an 'amount' field which should look like 3.50 or .90 or 3.00, etc. So the zeros are important. At the end of the script, the variable is dumped into a field. Problem: the contents of the "amount" fields show as 3.5, .9 or 3 instead of 3.50, .90 or 3.00. I tried using SetPrecision() but no change. Apparently, the field's number format is not going into the variable along w/the field value.... either that or the fact that th…

Brief question: Got an object. One field (field A) is a checkbox set. Another field (field B ) is a calculation. What I need, and can't seem to figure out, is this: In the calculation in field B, for filemaker to count the amount of checkboxes. I.e. if three boxes are checked, return value 3, if four are checked, return value 4, etc. any idea how this is possible? Thanks! OP

Am doing a ticket printing layout for one of my developments. One of the fields am using is "Title" that gets its information from a value list as defined in a relationship. Now, I want that just as the user choses the correct "title" it could also get the "price" info from the same record. How can I do this?

I am trying to calculate the yield for my items. The calculation is (Width x Length of belt)/.65)/144 My problem is that when i receive an order the width is in millimeters for example (37mm) but for my calculation i need it to be in Inches (for example 3.7 centimeters) how do i set up a calculation which will always convert the width from millimeters into inches and then carry out the remaining calculation?

Hello I'm looking for a way to change values in records, for instance country should be ISO code (Austria is AT not A), but I want to keep the modified date (aso) as they were before the update. Is there a way to accomplish this? Cheers

I am very new to filemaker and am struggling to set up a simple database where after entering one date in one field the second field automatically shows a date 45 days after the originally entered date. Please let me know how this can be accomplished whether by setting up a script or if it could be done without one. Please let me know. !!!

I have a number field with values ranging from 1-50. When I sort on this field, it sorts like text, like this: 1 11 12 13 14 15 16 17 18 19 2 20 There must be a way to sort like a number, no? Thanks!

Here is my problem. I have a solution that i plan to distribute as a runtime application for the Mac platform. The solution consists of two files, an interface file and a data file. The installer will copy the runtime engine and interface file to the 'Applications' folder and the data file will be copied to the users 'Documents' folder. My problem is that the two files need to be linked together by putting the file paths in FileMaker's 'Edit data Source' dialog for each file. The path to the 'Applications' folder is fixed and simple to achieve as it can be hard coded, however each user will have their own path to their 'Documents' folder and as far as I am aware…

I have the following two tables (simplified) which are a one to many relationship by 'order_no' 1) Order with fields order_no, date, purchaser 2) Items with fields order_no, fruit, variety, boxes, box_wt, lb_cost A single order could have multiple items such as 2 varieties of oranges and 2 varieties of apples. I would like to sum the number of boxes, total weight and total cost by 'fruit'. I have tried to add the fields to the 'Order' table and using the IF() function but it calculated the overall totals for both oranges and apples, not by 'fruit'. I am relatively new to FM 10 (started learning several months ago but got pulled away). What is the simplest way …

I've heard time and time again - avoid a repeating field like the plauge. But - I'm thinking of using one. Tell me - is this a mistake? The basics: Each time someone in our office prints a letter, I would like to store the date of that event. I have a seperate file to track the creation of the letter, and the letter type, and the letter will have the current date referenced when printed. The letters will be printed from a script, not straight from the print button. Suggestions on how and where to store a date, each time the letter is printed?

Hi all I have a calculation field that includes a date field within it: & INV::EventDate & However, how can I format this in the calculation field so that it display as DD-MM-YY rather than the default american format of MM/DD/YY Many thanks for your help in advance!

I am trying to create a function that will tell me whether a date criteria has been met given some data inputs. I just want a basical True/False to be spit out if the criteria is not met, but I'm getting wonky results, especially with date ranges that extend from one month or year to another. Let ( [ StartDate = "1/3/2010" ; Calc = "<" ; CheckDate = "1/10/2010" ; Duration = 3 ; Units = "Days" ] ; Case ( Units = "Days" ; If ( Evaluate ( Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Days True" ; "Days False" ) ; …

I have a calculation field whose result is text. Integrated into this calculation I have a number field. Here's the problem: When the two digits to the right of the decimal in that number field are both zeros, only one of them appears. I've tried the Round function to no effect. Am I just a knucklehead or has anyone else had this problem?

I'm having some issues with a calculated field.... I have a bunch of invoices with line items each line items has a number of boxes the items come in. I have a summary field that gives me total number of boxes in an invoice. Next I have a calcualted field that determines the number of pallets a given invoice will require based on the summary field. When I put these fields into a sub-summary based on Invoice ID the summary of boxes field works correctly, but my pallet calculation gives me the total pallets for every invocie in the review. (IE its calculating based on the total boxes in the found set) Not sure what going on here... An example of m…

Hi, I'm pretty new at a lot of the concepts in FileMaker Pro. I have a database where I track calls and clients. The main table is calls and the clients are in another table. They're related by an id. I'm trying to put a summary field of the total amount of calls each client has on the calls page so I can easily just see how much each person has made. It's got to be a really simple answer, but I don't see anything in summary or calculation that will do just one set of records without doing a find. Thanks

I'm not sure if this is a calculation question or a script question, but I'm hoping that some of you can help. I'm creating a simple database so my employees can track how long it takes them to complete certain projects. Their work on these projects starts and stops several times, so I was hoping to create one button that basically starts & stops the clock, and each time they start and stop the clock the time for that segment gets added to the previous block of time. This way I can have one field for the total time spent on that project. I hope I'm not being confusing, but any help with this will be greatly appreciated, as I've been working on this for a couple of…

I am banging my head on this one. I need a simplified way to express a Log() function and as I am getting older, my head isn't working as well as it used to (if it ever worked at all). I'm trying to figure out the calculation for a customer who pays extra on a loan, how many month will it take to pay it off. I am using this: (Log ( extraPayment ) - Log ( extraPayment - ((CurrentBalance * InterestRateCalc ) / 12)) ) / Log ( 1 + (InterestRateCalc / 12)) This works great but the software using the DDR that I generate doesn't like LOG() or any other financial functions. I also know of the function NPER() that works with excel but that won't work with filemaker. Any …

Want to make a little "quicksearch" field in a solution. (Still in Fmp10adv). The script should check a global field and then proceed based on whether the field contains a number or text. So I need some "if" logic. Simple enough. But my brain's cramping on how to identify whether the field contains text or a number. Tried some IsValid combinations like IsValid(Abs(thefield)) but apparently the absolute value of some text is valid.... as is the Int of some text. So I need a boolean result based on whether the field contains text or a number. ... or maybe a reliable way to generate a particular error whenever it's text or whenever it's a number. Would be eas…

I need to create a database that will be designed taking an Excel file as a template. In the Excel file there's some 250 rows and about 10 columns. I need to more or less duplicate this layout into FM. I was thinking of using tables instead of a layout to handle this but I need two of this columns (with their corresponding 250 repetitions) to pull data from two other tables inside the same file. I understand this is not possible just by simply defining the field and the creating repetitions, as these will all share the same values. Could someone please shed some light on how best to approach this? Any help will be deeply appreciated! PS Am attaching a scree…

Hi, let's say that I've two fields. Field 1 contains "Hi, my name is john and I'm learning filemaker" I want to be able to tell Field 2 to display Field 1, but Erase after a certain value. Example, I tell it to erase from the word "my". Which will contribute that the Field 2 only displays "Hi," from field 1.

Hi, I am new to FM.... I need help with a simple calculation I have two fields one is called Account Number (will have accounts like 710004, 711000, 711002, etc.) and another field called Total (this field will hold how much an item is say a computer for $700). I have created this in a portal and I am able to sum up the Total field. I would like to know what is the calculation to get: If Account Number is 710004 then add all Total Fields that have Account Number as 710004. Does this make sense? I am just trying to find a fast way where I don't have to add up all the fields that are say 710004 manually. Thank you soo much for any help :)

Ok this should be easy... I have invoices table there are 2 ID's there, Invoice ID and then the shipping ID. These must be separate ID. Every invoice contains Invoice ID, so I just set that to auto serial number. However only some invoices have shipping ID. How can I get the shipping ID to be either empty or next value... the ID should autofill if I type something in the shipping address box. It should also autofill when clicked... How can I get this kind of serial to work, where it doesn't auto fill it and looks up the last record with some other than empty value in it and plusses one?

What suggestions do you have for building trully universal ID fields? (To be used by stand-alone copies of the same application) I was thinking of a global field in the system table of my application which will be appended to every table's primary field (in Aut-Enter). But then I found out that it doesn't update. Any recomendations?

I have tried this several ways, but the format is not turning out right. I am trying to calculate a percentage charged by my merchant services. Field 1 "Charged" ($) Field 2 "Funded" ($) Field 3 "CC Charge" (%) So in Excel it would look like CC Charge=SUM(Funded/Charged) So it would look like this: Charged:$100.00 Funded:$97.86 CC Charge:2.14% But that isn't working in Filemaker. Please help, this is kicking my butt. I don't know if it is the way I am formatting the fields or defining them, or my calculations or all of the above. But I have tried do to fix this for a week!

I have a date field (YYYY-MM-DD) and quantity field (Number). I would like to get an average field which will be the average of the quantity field of the month. When a new month starts the average should then be for this month. also when it is a change of year the average should be for example jan 2011. Could anyone help me with this calcutation ?? munad.fmp7.zip

I work in printing and I'm trying to set up a calculation that will provide the maximum number of pages that can be fit on a given press sheet size. The math behind this is easy and it's also very easy to do it in one's head, but I'm a novice at formatting filemaker functions, so I'm getting lost in nested case functions. The are a limited number of press sheet sizes that we use in printing, but the method of calculating this fit is a simple test which I'll word out (all values are in inches): For example: If the press sheet size = 25 x 19 and the page size is 8.5 x 11, then: (Int (25 / 11 = 2.272) * Int (19 / 8.5 = 2.235)) = 4 (Int (25 / 8.5 …

I think this may be a quick one. I want a function that returns a Boolean result if a number is divisible by 4 ie it returns a whole number when divided by 4. Is this possible. Many thanks

Hi, I have a question. I have a database that tracks takes from a film shoot. I have two tables, one of takes from A camera and another from B camera. Both cameras were covering the same scene, but each started recording at slightly different times, so while they are in sync, their starting and ending timecodes are different. I need to form a relationship between the takes in these two tables. The most reliable value to form s relationship on is timecode. All the other values are text based and vary greatly in each table. So, I need to find the "nearest neighbor" record (based on timecode) for A camera in the related B camera table. The records are never predict…

I was hoping someone might be able to help me create the following calculation in Filemaker Pro Advanced 8.5: I have 2 fields: "First Name" and "Surname". I was hoping to create a separate field that automatically entered the initials of the name, eg. John Smith = JS What formula do I need? Please help.

Hi All! Just wondering if anyone knows of a way to garner the list of fields by name or a simple count of fields in a given table. I am aware of the FieldNames() and FieldIDs() functions. However, these functions will only return those FieldNames or FieldIDs for fields that exist on a specified layout. I'm looking to simply get a list or count of fields from a specified table. Happy Holidays!

Dear All, I just tookover a system design in FM6 which I will need to convert to FM11. However, there is one file that trouble me (please refer to the file attached). There are many Calculation Fields (e.g. calP1, calP2 etc.) refer to the field, "Recovered" which is NOT defined. How can this happen (usually, FM will replace the field deleted as "field missing")? And, I think it is the main reason that I can't convert it to FM11. Thanks ahead for whoever solve the puzzle and share with me. Regards, Omega Goh OPADRAW Clone.zip

Does anyone know of a way to set a field in a related table via a calcuation?

I am trying to set up a field so that no matter how users enter information it will be corrected through autoenter in order to ensure standardization. All the information needs to be in one text field. Users may enter information in a veriety of ways such as 4x100 or 10 x100 or 14x 1200 etc.They might even additional spaces betwween or leave them out etc. I need field to have just a single space between each amount (i.e 4 x 100 etc.). I think this ought to be able to be done with an autoenter calc but cant see how to do it. can anyone help please? Tnanks

HI everyone - HELP!! Im Stuck or brain dead I have 2 databases which are linked by a relationship. One is a student database and the other an accommodation database In our school we put students with homestay families - when one student leaves we put another one with that family. I am trying to do a field "current student" in my accommodation database so the current students name will show up only. I have done a claculation according to the student status - each student has a code number depending on if they are still at school or not 141, 142 or 143 means they could still be in the homestay and 144 means they have left school - my calculati…

How do you produce the results of a repeating calculation fields repetitions by its own internal calculation? Is this possible? For example if you wanted Field[1] to be 1 and Field[2] to be 2 and so and so forth via the fields own internal calculation. Why do I want to do this you ask? Because I want to set a repeating calculation field with 7 repetitions with the current weeks dates - repetition 1 being Monday etc. I can isolate the date range of the current week just wanted to know if I could produce a result in a repeating calculation field via its own internal calculation thus eliminating the need to set the repetitions with a script. I have been playing around but ju…

I'm trying to turn a "flat" (single table) database into a parent-child database. Let's say the original database hold records of Book titles, with a field for Publishers. I want to create a parent table to hold the Publishers, each of which will publish many (child record) books. I figure as a first step I can just duplicate the Book Table as new Publisher Table, delete all the irrelevant fields and then throw out all the duplicates (assuming all publishers are unique). Then after creating the relationship between the tables via new key fields, I can populate the foreign key field in the Book table by matching up the Publisher names and pulling in the Publisher pri…

Hi all! While having a look at a customized software, I spotted a nice module, that I would be excited if I could integrate into mine. Inside a text field the user writes in free text what could be termed as "a time-span calculator from free text". Let me explain myself a bit. The text-field, is used to specify the "age" of an archaeological object. The allowed expressions are in free text, but yes, they have some rules (see below). What is interesting though, is that some sort of calculation takes over after writing the text in an "everyday-language" form and calculates/translates the time span it covers into dates. It's not only that it facilitates date…

I have searched for days and cannot find an answer to my problem. I have created an FMP9 (Mac) database of 600 images of various sizes and their respective data (i.e. filename, filepath, etc.). I understand that when an image is inserted using the Insert > Picture menu option (reference only), the GetAsText function returns not only the path, but also the image file name and size (height, width). How do I generate this information - specifically, the size - when using a path to populate the image container field? Also, what is the difference between "image" and "imagemac" at the beginning of a path string? Note: The calculation for the image container field c…

Hi. A vague title I know. Sorry. I have two tables. One for a list of clients and one (related by a client ID) for visits to a facility (the visits I cam calling episodes). Every client will have one or more episodes. As clients present with new episodes I want a numeric field in the client table to automatically update with the episode count. Currently I do a Find and count then manually update it, but is proving awkward to do manually as users will jump about screens. How can I set up a calculation field in the client table to automatically track the number of episodes that client has made. Thanks in advance. Greg

I need to calculate the amount of interest payable on an outstanding bill. I am only allowed to charge interest at a prescribed rate. The rate I am allowed to charge varies from time to time. For a long outstanding invoice there may 2 or 3 or more interest rates applicable for specific periods during the time when the bill has been unpaid. Any suggestions as to how I work out the interest?

I know this is going to be obvious...and I apologise in advance: can some please tell me what the "/*" signifies?

Hey everyone! I'm new here so don't bite my head off. I appreciate any and all advice. Anyway I am looking to create a way to create a field that acts like a timer. Basically what I have thought about doing is creating two timestamp fields: "Start" and "Stop". From there I created two buttons with matching names on my layout each with a function of Insert Current Time into their respective fields. Then I created a third field called difference, to see if I could create a calculation field that would subtract the Stop timestamp from the Start timestamp. Here is where I ran into trouble. I tried to have the results come up in a number format, which ends up being…

I have a database that is used as a template to clone new databases from as needed. Each new database must start out containing no records, so that any records that are added to it belong only to that database. The problem is with a repeating field. The field is simple enough - 14 repetitions containing consecutive numbers incremented by 10 starting with 20 and ending with 150, and stored globally. I can enter the values in a cloned database with no records and each added record retains all 14 values. I can make a copy of the original file and the values are retained. I can delete all records in the database and the values are retained. But, if I Save a Copy As …

Hello, We use a barcode scanner in our filemaker solution, but it inputs a carriage return at the end of the number on certain barcodes. Unless we manually delete that carriage return, it says there. I'm looking for anything that would automatically take that carriage return out. I've tried the trim function, but that only seems to work with spaces and not carriage returns. A Calculation? Script trigger? Custom function? Any ideas?

This problem seems to be easy to solve but somehow it has driven me mad in the last few days. This database has hundred thousands of records, each has a photo section with 2 fields: field A contains the link to photo (imported from Excel) and field B is a calculation field with result as a Container (to display image from field A). The calculation is: "imagewin::" & Substitute (PHOTOLINK ; "" ; "" ) where PHOTOLINK is the field name of field A. The result is a message "the file cannot be found: C:\Photos\IMG_2222.JPG" I've checked the photos and their preferences and found no mistakes. I've tried every possible solutions but stil can not have the imag…

I didn't add a unique "serial number" for each record in a solution I've been working on for some time. But now I find I need one after all. I'm 2400+ records into the db. Is there a way in FMP6 to populate the existing fields without having to enter the information in manually? Then I'd lock up the field so user can't enter/input/change and let the automated serial number work when each new record is created. But was needing to know if there was a way to automate the population to save an hour or two of manual work. Thanks! :

After conversion of my multi-file Filemaker Pro 5.5v3 Marina Management Solution using Filemaker Pro 10v3 Advanced I have found what appear to me to be several conversion anomalies. For instance, the calculation operator for "Not Equal To" in FMP5 (< >) is changed to "≠" in FMP10. However, after the conversion to FP7 files, the "Not Equal To" calculation operator is still "< >" in the converted files. This happenstance is true for the calculation operators "Greater Than or Equal To" (>=) to "≥", and "Less Than or Equal To" (<=) to "≤". All these operators are left in the previous FMP5 format. Does anyone know if this will result in potential…

I have a donations table and a contact table. The donation table tracks donations by date, amount, event and links them to their appropriate donor by contact id. I am creating a letter writing program that I can insert a merge bracket and it will automatically include the appropriate contacts last donatio amount. The problem is i dont know how to create field that calculates what the amount and date of the last donation was automatically. Does anyone know how to do this? Thanks!! Erik

I'm just curious if this is possible. Say if I have a calculation that is asking get (CurrentDate) = get (CurrentDate) and the results is 1. If I have a field that contains the literal text "=", can I substitue that value as an calculation operator like this? if ( get (CurrentDate) & RandomField & get (CurrentDate) ; 1 ; 0 ) and get 1 as the result? Is this the correct method to accomplish something along these lines? I'm basically wanting to allow the user to create their own custom calculations without needing a developer to do this for them.

I have the following formula: Case( Beginners _ Kids Magic = "Yes" ; "Beginner Magic"; Card Deck = "Yes" ; "Card Magic"; Card Magic = "Yes" ; "Card Magic"; Close_Up Magic = "Yes" ; "Close Up"; Collectibles _ Historical = "Yes" ; "General Magic"; Comedy Magic = "Yes" ; "Comedy Magic"; Easy To Demo _Shop_ = "Yes" ; "Beginner Magic"; Fire Magic = "Yes" ; "Fire Magic"; Gambling Magic = "Yes" ; "Gambling Magic"; Gospel Magic = "Yes" ; "Gospel Magic"; Magic For Kids = "Yes" ; "General Magic"; Magic Set = "Yes" ; "Magic Sets"; Magic Staples _ Consumables = "Yes" ; "Supplies"; Mentalism = "Yes" ; "Mentalism"; Money Magic = "Yes" ; "Money Magic"; Season…

I'm thinking this has a pretty straight forward answer, but I haven't been able to figure it out. I'm needing a calculation that will determine whether the current user is accessing the database from the host computer or over the network. In the past I have used something like If( Get(HostIpAddress) = Get(SystemIpAddress); 1; 0), but depending on the setup of the network that sometimes doesn't work. What's the most reliable way to determine this? This is using regular FM as the host with network sharing, not FM Server.

I have an unstored calc field that returns text ... If ( Code( Left ( field b ; 1 ) ) = 191 ; field a ; field b ) This seems to be evaluating correctly when the file is opened in v8.5 of FMP yet function Code(text) was not introduced till v9.... The file is deployed with server 10. Any thoughts? TIA, Kris

I have a repeating Number field[24]. I now want to set another field to display all the values in the repeating field, but only those that have a value in them. I want each of the values separated by a ", " for ease of viewing. I will need to set the field long enough in size to allow for the possibility of 24 separate numerical values. And allow for the comma between each value also. Would the field need to be set to a Number field or a Text field, as it includes comma's ? How can i achieve this?

Hi I'm sorry if this is in the wrong section, but I've come across a rather strange problem (which I'm positive is user error). first of all, what is the difference between a field that is automatically populated via a calculation, and a calculation field? second, I have a number field that is populated by a calculation that concatenates the values from two other number fields in the table that includes a vertical pipe bar as a text separator. for some reason, the field gives a wierd number result. If I change the field to a text field theirs no problem, but then I can't sort it correctly. My question is, what does a single vertical bar (AKA pipe) character mean…

hey guys i have a database that i use to keep track of my paychecks, etc. two fields define the pay period - "payperiod begin" and "payperiod end". typically the pay period starts on a friday and ends two weeks later on a thursday (eg, begin: friday, nov. 26th; end: thursday, dec. 9th). is there a way to have filemaker auto-enter the pay period end date once i set the start date? this would be one less thing to enter and it would be quite handy. additionally, it would be handy to have an option that would allow the user to select whether the pay period is 1 or 2 weeks, however this isn't necessary although it would be neat to know how to do that. thank you …

Just like the start of many other posts, Seems simple but I can not figure this out. All I want to do is during running script, remove the last character from a field. Example. The Field shows 150B, and I want to set the field to only 150, or 1500B to 1500 and so on. Many Thanks! Mark

Hi, I've run into a brick wall while trying to develop my solution. I'm making a simple contracting managment system with couple of tables "contracts", "contract_articles" and "contracts_lines" similar to regular product/invoicing system. However there is one special feature I want when creating new contracts: I want to automatically insert client and price data (from external table) into contract articles where appropriate. For example I would select "Price and payment" article from contract_articles table and insert price into article text. First I thought I would be able to use some sort of variable inside article text which would be then replaced with proper…

Hi! For a field I need the following sort of thing to work: Case ( TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 = TableB::Field2;TableA::Field1; TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 ≠ TableB::Field2; TableA::Field1 & "" & TableB::Field2) It's all text, trying to get input from two layouts (single field each) to be added to a third layout in a single field if a Yes radio button is checked. If the text exists and is identical from both then I only want one shown, if they differ then I'd like them listed wi…

I'm interested in writing the simplest expression that looks for a range of numbers within a field. Example: Is the number of characters in (text) between 6 and 10? I can certainly do this with multiple Length calls: Case ( Length ( text ) < 10 and Length ( text ) > 6 ; "yep"; "nope" ) However, I'm thinking FM should have a more elegant way to determine a range of values here. I.E., field definitions support the "in range" concept, find requests allow for "7...9", etc. How can I obtain a range in a calc without concatenating 2 or more statements? Thanks in advance!

Hi, I am new to filemaker, but I am trying to find a way of creating a sum of all the fields that have a particular value for another field. So for example, I want to total Field 1 in every record that has a 'yes' in Field 2. How would I go about achieving this? Thanks, Rob

I have a field called "WarrantyExp". I made a calculated field to display the number of days remaining on the warranty using WarrantyExp - Get ( CurrentDate ), but for those fields where the warranty date has expired and is in the past, my calculation results in negative numbers. What can I add to the calculation so that past warranty dates are ignored? Thanks, Kevin

I'm brand new to FileMaker, and to be honest I'm a bit lost. I'm currently working on timesheets for my organization, but am having a few issues. I started using the starter solution timesheet feature that is provided with FileMaker. I have all my employees entered, time codes, etc. My boss wants to make sure we have Paid Time Off to auto calculate an employees PTO. Can someone help me with this? I'm assuming it's a calculation, but again, I'm not sure on how to pursue this. Any help is greatly appreciated.

I have a text field, called "Indx", in a main file which is auto calculated as follows: Client State&GetAsText(GetAsNumber(Date), with the field, "Client State" being a two or three letter abbreviation for the State where a client lives and "Date" being the date applicable to that record within the file. Note also, for later reference, that "Client State" has been looked up from another file, as opposed to data entered in the same layout, (I'm not sure whether that's significant or not but I thought I should mention it just in case). So far, so good - all working. My problem arises when I try to use "Indx" to create a relationship with another table either …

In a nutshell, i have a number field named [iD] with 10 repetitions. Quite often a number will be repeated in more than one of those repetitions. Each number is related to a record in a separate table. So what i am trying to achieve is to print ONLY one copy of the record in the table that unique value records[iD] appear. So for example, in the above values i would end up with 7 printed pages out of the 10 values etc.... How can i achieve this?

Can PatternCount, in Case, have more than one variable.

Hello: I am trying to get something accomplished in a field calculation that I am not sure is possible. Basically I have some logic that tests other fields and if they add to more than 352, do one thing, less than 352, do another, and if the current field doing the calculating goes below '0' pop up a dialog box that says you cant do that. Not sure if this makes sense, but here is a butchered snipit of code that hopefully someone can follow my logic on and possibly make suggestions as to making it work: Also you will notice that I simply said "[This field]" in the code toward the end because I don't know how to reference the current field that is doing the calcul…

I have 5 fields in 1 table where I need to create all possible combinations of their values and present them in a drop down list so a User can choose 1 of the combinations. The way I am writing the calculation seems way too hard (I am writing each combination out). I am thinking that someone might have a better way. The kicker is that only one of the fields must be populated. So, in some cases - all five fields will have a value, in other cases, maybe only one, two or three fields will be populated. So, I also have to account for extra blank spaces to be removed due to concatenating. Here is some make-believe data: Field 1: Apple Field 2: Tree Field 3: St…

Hello: I need some direction. I thought this would be simple but after giving it the old college try for more than 90 minutes... Anyway, I have two date fields I want to use to calculate the third date fields. I manage contracts and I need to create a field that will display the date to start contract renewal process. My two fields, BEGIN and END, are set as dates, and field cannot be empty. RENEWAL REMINDER is supposed to be a calculated field. Result should be 3 months before end date. On Validation tab of RenewalReminder, I set it to not empty, check calculation, and click specified, using this formula: renewalreminder = month(begins) + 7 hoping th…

Thought this would be simple, but I'm stuck. Thanks for your help! I need to give the first number of items, say 2, at no cost. I have that number(2) in a field "no_free". The result should be that the price will be 0.00 for items one and two and the next item, no matter how many more, will be at regular price. What I am trying to do is: free_or_not calculation field is defined as If(the progressive record count w/the same invoice_id ≤ no_free; 0; 1) In the price calculation this field results "free_or_not" is used as a multiplier that keeps or zeros the price. I need to know how to come up with the progressive record count of items sharing the …

I am trying to find a way to calculate a date in the past given a user defined criteria. I've searched all over the place but I can't seem to find something that does this. I've found plenty of references for calculating elapsed time between two dates but nothing out there that will find a date in the past given a timeframe. if ( Date of Service ) > 14 days ; "True" ; if ( Date of Service ) > 6 months ; "True" ; if ( Date of Service ) > 1 Years ; "True" ; "False" It seems that this would be easy to do given the number of days contained within whatever criteria the user defines but I can't figure it out.

I have a single computer acting as a time clock for all the employees. They simply enter their ID and clock in or out. They are on a two week pay cycle. Currently, I have two calculation fields: > PayStartDate = (this calcuation is simply a date that I manually enter every two weeks) > PayEndDate = (PayStartDate +14) These two fields define the range of days show in the portal that calculates the hours that each employee sees on their virtual time card. How can I automate the "PayStartDate" so I do not have to manually enter it every two weeks?

Hi, hoping this is a really simple problem, but it is driving me insane Let me explain: I am trying to modify a filemaker solution I paid to have developed. I now want to try and fix this myself. I have a little knowledge of filemaker so it would be good to know how to do this I have 2 tables, costing and quotes. Both of these tables have a field that is set to Timestamp, and have the tick in the box 'modification - date and time'. I then have a dashboard layout with 2 portals that display this information. My problem is this - if I log in using any user, the modification timestamp on one of the portals (the quotes portal) is changed to the cu…

Hi everyone, I'm sorry if someone has already answered this question, I tried searching through the forum but was unable to find an answer. I've included the backstory below this question in case it helps. My question is this...I need to create a calculation/function that returns "true" if all the fields in a specific record contain values, and false if ANY of the fields in that record are empty. I know that I can do this by creating a calculation that uses If(IsEmpty(field 1) or IsEmpty(field 2) or etc.) but then I'll need to enter the variable names for hundreds of variables (total) into the calculations that I'll have to create for each tests table. Is there…

I'm creating a report layout in a staff roster file which is often updated, necessitating the deletion and import of records from another FMP database. The layout contains fields: staff member name; day of week; date, (the layout is grouped by staff member name and date); concatenated start and finish time field; client name, (these last two fields are in the body of the layout). I need a page break inserted with each change to the staff member name only. When I first create this report, the page break works fine, separate page in each preview. However, as soon as I make changes to the records, including deleting and importing, the page break ceases to function, des…

I'm creating an FM file that contains multiple occurrences within one record where the input of different staff member names from a custom value list performs a look up to a "ref no" field for each "staff member" field. Fields indicating a "time from" and a "time to" for each "staff member" field are then manually inputted. In this application, there could be times where the user might decide to delete the entry in one or more of the "staff member" fields in a record and I would really like, just to save the user having to manually do it, have FM automatically remove the data from the three other fields. Although new to FM, I've worked with database application…

Hi I am sure this is a stupid question but can't figure out the answer. I have the following calculation:- Case ( Instructor = "Yes"; c_Name Search; //Else "" ) I would like to add an additional AND criteria to the above so that if:- Instructor="Yes" AND Active="Active" please can anyone help with amending this formula as I am new to FMP. Many thanks Dave

Hi all If the users enters a year and week, I want to calculate the startdate and enddate of this year/week. In another helpfile I found the following code: Date(1;2 - DayofWeek(Date( 1; 0; Year(Get(CurrentDate))));Year(Get(CurrentDate))+ 7 * (Week - 1) But that doesn't work. (I don't even know what it is trying to do). Can anyone help me with this? Thx Sabrina

I am having problems with a calculation and don't know where to go from here. Volunteers get points for bringing in empty boxes. You get 1 point for each empty box up to a max of 10 points. (The max is the problem) I've written this as both IF or Case (not sure if there is a difference) When a new record is added it doesn't know to sum up all the records for a total of empty boxes. The calculation is just doing it (correctly) for each record. Here's what I'm using. Case ( Empty Boxes SUM ≤ 10;Empty Double Boxes;Empty Boxes SUM>10;10) Person brings in 6 boxes on Mon - receives 6 pts Person brings in 4 boxes on Tus - receives 4 pts Person brings in …

There is a Script Command which is Set Field by Name, where the Field Name can be a calculated value. What I need to do is retrieve a field's content by Field Name (i.e. GetFieldContents(Field Name) ), with the Field Name being a calculated value. I have been unable to locate a native calculation function that does this. Is anyone aware of a plug-in/custom function that would accomplish it. Note: I have 300 structured data elements (e.g., field names x1.0.0, x1.1.0, x1.1.1, x1.1.2, x1.1.3, x1.2.0, x1.3.0, x2.0.0, x2.1.0, x2.2.1, etc.) broken into 60 sets. To further complicate things, each field has multiple repetitions. If I cannot locate a solution, …

Hi all! I'm facing a recursive function puzzle I can't solve and would appreciate it if anyone can help. Her it is: I have a global text field (SortBy) where I store field names in, separated by CarriageReturns. What I need to do is when I refer to the contents of this field from another table (that's why global), to be able to retrieve the contents of the named fields (stored as text in SortBy) and if more than one separate them with a slash (/). The field is used to produce a CALC field to help sort records dynamically inside a portal. So far I can only refer to a single field only, since I can't write a recursive CF that will start from the last va…

Hi, I may be looking at this wrong, or maybe there's an easy answer. I have a calculation that gives me a string Lastname, Firstname,phonenumber1,phonenumber2 I want to export that as a de-limited file that I can import into a spreadsheet. The problem is that I want the first field to be LastName, Firstname with the comma. So I thought that a comma separated file wouldn't work as this separates those two items. This lead me to want to export as tab de-limited. So my initial question is there a way to specify a "tab" as a text character in a calculation? So the string my calculation creates is Lastname, Firstname -> phonenumbe…

Hi everyone. Like so many on here I am a bit of a newbie to filemaker and need some help with a calculation. Basically I have a value list with 2 entries Yes and No I'm trying to create a summary calculation that will show how many yes's have been selected. This then leads on to a calculation from a field of which is called "Amount Total" as long as "Yes" has been selected from the "Yes" option of the value list. What I am aiming for is a summary sheet which will provide the totals for how many "Yes's" there is along with the total of the "Amount Total" I've already had a look through the forums and so far learnt much, just not what I need to …

In my patients database, I need the age of the patient. I want to enter in the field one of three values " depending on what we can get from the patient's relatives" : The specific DOB, the year of birth (four digits) or the age of the patient. For example: 2/15/60, or 1960, or 50; and yet get the same output "50" for any of these entries. Can you help?