Calculation Engine (Define Fields)

I have a table with contact information for customers And i have a table for open service tickets. The fields are related by company. I have a button to send an email internally to announce the new ticket. I want the ticket to include the email addresses of all related contacts (related by company) Now I could use Count ( Contacts::EmailAddress) for an If statement, to determine if there are more than one contact records for that customer And i could use GetNthRecord ( Contacts::EmailAddress; 2) But what i want to know, is there any way to do a For Loop? Like For( TempVar; Start at 1; end at Count ( Contacts::EmailAddres…

Hi, I have a filemaker 8 database - recently upgraded from filemaker 6. The database contains a large number of calculations - 1 of which is no longer working. Comparing the old files to the new files there is no difference and i have made no changes - i just upgraded. however the FM6 files displays the result of the calculation and the FM8 files display a ?. The calculation is very large and complex and involves a lot of fields - not all of these fields will contain data, it depends on the job that the client needs to quote - is there any reason why upgrading to FM8 will prevent previously working calculations to no longer display results?

How do I pad a number to ensure that it is always atleast 4 digits? The original DOCID is in the format of 0000. There is a field NumPages. So if on record 1, the DOCID = 0005, and the Numpages is 6, i have a script which basically runs and creates records with 0005 - 1 0006 - 2 0007 - 3 0008 - 4 0009 - 5 0010 - 6 but in reality it creates it as 0005 - 1 6 - 2 7 - 3 8 - 4 9 - 5 10 - 6 See the attached for a better explanation. I was thinking of using the count characters but I think I'm going about that the wrong way. login and password are both "admin" TIA example.zip

I am sure this goes to show how new I am, but I can't find the answer in any of the FM books I have or even on the web. I just need to know that when I have a phone number field I want the data entered to be seperated from the area code and a dash entered. Could someone help me with this. Thank you in advance.

I am trying to develop a job costing program that summarizes hours spent working on different projects. A worker may clock in for one client several times or he may work for several clients just once. The input might look like this: Smith Project: 1 hour Jones Project: 2 hours Jones Project: 2 hours Camp Project: 3 hours I would like the summary report to query all records and generate something that looks like this: Smith Project: 1 hour Jones Project: 4 hours Camp Project: 3 hours I would like the summary field to keep track of whenever a new client has been added, find all i…

Hello, here screams a novice in FM for help. My problem is simple( not for me) In database I have two fields, and when I enter one value (number) to the first, the second should show one particulal value(number)The second value is not by some calculation (*, + etc.)but with both fields, I have earlier decided what the second would show when enterin some value to the first.Confused? So, for example if I enter "30" in the firs field, I want the other to show "50", IF i enter in the first field "40" I want the second show "60", etc. I figure it is Logigal Function "case" I should used? Is it?

Hallo, I've been trying this for four days now with no luck! Is there a way to add a new set of records in a record? What I want to achieve is to have a set of different projects. Every project is a record. What I want to do is to add the emails(with descriptions of users etc) related to each project. Each e mail must have a different record since there are many emails related to each project. I hope I'm making my self clear here. Please help me with this, any tip could be useful! Thank you in advance, Lukas

This seems like it should be easy and I feel like I'm missing the obvious answer but I'm coming up empty. I've done a quick search here but I don't see any obvious posts on this topic. I know that FM 8.5 Advanced can import table schema from one FM database to another but can it import the schema from something like an Excel file? The world seems to revolve around XLS files and I have a very large spreadsheet I need to do some work on. I'd like to bring it into FM but I don't want to manually create the dozens and dozens of fields (XLS columns) it will take. Can FM import an XLS file and assume the first row is the field names. I'm assuming the fields will a…

I am not sure if there is a function that would be able to do this but I figured I might have just missed it. I want to do the following: Adress Field: Company Name Address City, State, Zip Change it to individual fields per line: AddrLine1: CompanyName AddrLine2: Address AddrLine3: City, State, Zip Thanks for any help!

I've recently built a database in FileMaker 6 and I wanted to know if there is way to lock a field after data has been entered into it. I would like to prevent someone from changeing the data in that field.

[color:green]Hello all I have a field called Coordinate with this value: [color:red]N40° 1.694', W121° 30.999' [color:green]I want to do a calculation, two field and put N40° 1.694' in Latitude Field and W121° 30.999' in Longitude field. The below kinda works, but there must be a better idea out there. For the Longitude I used this Calculation: Middle( Coordinate; Position( Coordinate; ","; 0; 1 ) + 1; Position(Coordinate & "¶"; "¶"; Position( Coordinate; ","; 0; 1 ); 1 ) - Position( Coordinate; ","; 0; 1 ) - 1 ) {I used the Comma as the dividing place. Probably not the best idea because what if somehow there was another comma} [color:g…

I have a ex VAT Field that I need to add VAT onto. How do I make my inc VAT field calculate 17.5% onto the price? Also can I make bot fields dynamic so if I enter info into one field it recalculates the other field? i.e if i enter the ex_vat price, the inc_vat field auto calculates or vice versa if enter the inc_vat price, the ex_vat will auto calculate?

I created a file called Drawing Requests where users login and fill out their requests. I set up a table that contains user profiles (name, email, title, etc..). When users login they are greeted with their name at the top of the page. I want to setup a field that shows the names of the users who are currently logged in. I am having trouble getting this to work. Surely this is the place to find an answer.... ....Would be greatly appreciated. Sifter.

Hi, I've a calculation field that creates a code from 5 other fields. What I want to do a build a check to ensure that I've all the parts to the code. The code looks like this "H01-02-01-X ######" H01 - Part 1 02 - Part 2 01 - Part 3 X - Part 4 ###### - Serial No (length varies) I thought that this would do it; If(Identity Number Calculation ="h0#-##-##-@ *"; Graphic 2 ; Graphic 1 ) Do I need to get more complex and check each individual part that this code is created from? Thanks in advance, Skip

I realize the nature of a global field is temporary or session-based, but I've seen some uses that I can't replicate. Is there a [non-scripted] way to preserve the values of a multi-repetition global field such that the field is not cleared when the file is opened? Asking because Brian Dunning's Chartmaker app does something along these lines, but I don't see how any script is refilling or bypassing the field in question.

I am having a Problem in FM 6 Developer with a date calculation. The problem is as follows: I have two date fields which are both user entered fields and native to the same record. I have a third field (also native to the same record) that calculates the difference in years between the two previously mentioned date fields. The problem I have encountered is that the calculation only updates when the information is updated in the second date field. I can update the information in the 1st date field all day long and the calculation field will not display updated results. Has anyone encountered this problem? Thanks.

I need to format a postal code. I have about 400 of them in my database and this code includes both letters & numbers (example: K0J1L0 is what was imported. I need it to appear K0J 1L0). It seems easy enough to add this space between the first 3 and last 3 letters/numbers. I used the following: Let ( imput = Filter (Postal Code; "0123456789") ; Left (imput; 3) & " " & Right (imput; 3) ) I formated the calculation to be text. Yet it returned values that were only numbers! All of the letters became numbers! Can you help?

Hello folks I'm new to the whole filemaker thing and finding my way fairly slowly. Hopefully this question is in the right place. I'm using Filemaker 7 on a mac and a pc. I have a list of address's database with fields like this: street number, street name, town, county etc for each street name there are several street numbers as such: 1;2;4;6;7;8 harley street london etc what i need to be able to do is automatically create multiple records so that i have (in this case) 6 records with one street number thus: 1 harley street 2 harley street 4 harley street etc. the reason i need it automated is I have 15000 odd records that…

I'm working on a school database with several tables: students, classes, class_records. Students has general demographic information, classes is a list of classrooms and grade levels, class_records is the table where kids are assigned to classes. Class has: grade teacher year room Class_records has the following fields: recordID studentID classID year What I need to do is create a calculation field in the student table that shows the grade level for the most recent year. I'm sure this is simple, but I'm drawing a blank!

I would like to compare the fields in a record to the fields in the previous record and change the color of the text if the field is different. The comparison will be done in a found set of records so the comparison will need to be dynamic. For instance, the weight in the first found record is 100, the weight in the second record is 200 and the weight in the third is 200. I want the 200 in the second record to display in Red and the other two records will display in black. Any suggestions?

I'm looking for something similar to the drop-down calendar option in Field Control Setup, but that would give me something like a day book schedule with times in 15 minute increments from 9am-5pm for the record date entered in the previous field on my form layout. Is there anything existing I can use in FM8.5? Or does anyone know how I could go about creating it? Thank you.

For populating container fields with files where the file path can be calculated as the contents of a text field, I would like to set the "Specify source file" and "Specify output file" options for the associate script steps listed below. The dialog box seems to only want file paths manually entered. Is there a way to use the contents of a field to do this? Insert File[], Specify source file Save Records as PDF [], Specify output file Thanks, Paul

Hi everyone, I've been working in web development for a while and away from FileMaker, and now that I need to use it I find that I'm VERY rusty! I appreciate any help that you can provide. I want to set up a field that automatically calculates a priority list based on an entered due date. For instance, if the due date is today or tomorrow, the priority would be Very High. If the due date is the day after tomorrow, the priority would be High. The day after that would be medium, and anything after that would be low. I also want it to only count weekdays, if that's possible. Can anyone help? Thank you SO much! Very best, TigerGirl

I am trying to do an autofill field with a calculation that I just can't seem to fight my way through. I have a field "Frequency" and want to calculate the field "TV Channel" I have all the numbers, but can't come up with the syntax. The calculation is mostly "if is >10 but <19.9, then "1", and "if is >20 but <20.9, then "2". But i have to repeat the if/then statement 69 times, as there are 69 possible TV Channels. Any help on where to start this would be greatly appreciated! Thank you.

We've got 3 Macs sharing a FMPro7 database that processes orders. Instead of making a hard copy invoice, I want to write a script to create PDFs which are embedded in a container field. I’m concerned about access to the files by all users (trying to avoid filepath complexities) so that anyone can click a button and have the invoice display. I'm looking for the best way to structure this so that I don’t run into too many unforseen problems. THANKS!

I need to learn how to format phone numbers & zip codes correctly. Once again as an MS Access user I know how but can't discover it in FileMaker. Help please!

I need to do a couple of things related to time but my math skills are not getting the job done. Any help would be appreciated. I have a perfect time (pt) and an actual time (at) I need to find the difference between those two in non negative numbers, and then have text that will tell me if the difference is late or early. example 1 pt = 13:14:25 at = 13:14:26 desired result Field 1 = at - pt = 1 Field 2 = at - pt = late example 2 pt = 13:14:25 at = 13:13:24 desired result field 1 = at - pt = 61 field 2 = at - pt = early example 3 input pt = 13:14:25 at = 13:14:25 desired result field 1 = at - pt = 0 field 2 = at …

Good Afternoon: I have a perplexing calculation that I would like help with, if possible. I am working on a large Human Resources Database using FM 7 Pro. I am trying to calculate years of service for an employee. I have the field calculating current date - hire date divided by 365 which returns the value fine. What if the employee leaves? I have a field for termination as a yes/no value list and then a field for termination date. I need my database to stop calculating years of service if the terminated field is set to yes. I have tried "IF" statements calculating current-hire date/365 if the value of the terminated field is "no" and another statement current da…

Assume the following situation: a Quantity field and a Price field in a child table. I just need the sum of the all the Qty*Price on the parent layout; I don't need to see the individual results (product per row). I can create a 3rd field in child table LineTotal = Qty*Price and a new calc field in the parent to sum LineTotal = Sum (LineTotal). I tried the formula = Sum(qty*Price) but did not work. I am just wondering if this is the only way to get the sum of (Qty*Price) from a child table. Thanks

Where I work we have a database for memberships purchased for our organization. Our memberships expire yearly, and we have a field where we enter in the expiration date. However, unless someone goes into an expired record and changes the person's status from "member" to "expired member," an expired member can potentially still show up as a member. This can potentially be a problem when we do a count of all of our "member" records (since we turn the number over to our board of directors, it needs to be fully accurate). Is there a way to trigger a field to deactivate at a certain date and time? Not that manually doing it is that much work, it just leaves a lot of ro…

Now this is interesting one. I am trying to com up with a way to enter data into a text field that CONCATENATE's values from 2 data bases. I want to be able to type what I want into a text field and have that text field be used by a calculation. Here's what I got Script Syntax: (Text Field user types into)= "CHANGEALIAS " & """ & FC_Users::Name_FirstLAst & """ & " " & """ & FC_Users::User ID & """ FC_Users is a related database. So I type that in to my Syntax field, I now want a field that will CONCATENATE, via a calculation and spit out: Script Calculation:CHANGEALIAS "Buildings & Grounds" "Buildings & …

Hi I'm after some food for though. We are getting data from a external supplier that is regionalised i.e.. Euro Germany UK Eire France Just creating a database from the supplied CSV/XML files supplied populates at the moment around 30-60k'ish records each. Ranging in smallest 21mb to largest 89mb Database. This information needs to integrate into an existing solution but will get updated weekly/monthly. My gut feeling is to contain all this data in the appropriate tables in a Supplier Info Database and create filepath relationships back/forth from the existing solution. But the MD says it would be better to cram it all into our exist…

Is there a way to check all repetitions of a repeating field for some pattern at once and if it does not exist, go to an empty repetition to enter a new entry for that pattern you were checking for ?? i.e. check for "Smith" Johson Jackson Sanjay __ __ so enter "Smith" or search for "Smith" Johnson Smith Frank then add some date of checking to the "Smith" entry in the repeating field notes for that repetition ??

this is probably an easy one. I need to place IP addresses into a field. Can I format the field so that the numbers are entered like ###.###.###.### of course not all IP addresses have three numbers in each section so hopefully it will not ask for three numbers if there is only one. But would be nice just to get the . automatically entered. Whats the best way to do this?

In order to create calc for a second date 10 days earlier than a entered date in "date1", 9/20/2006, for example: "date2" = ( DayOfYear ( date1 ) -10 ) as a "number" yields the value "253" In a new calc "date3", the value "253" returned as a date yields 9/10/0001. I see that the day and month are correct but the year is off. What am I missing?