The Left Brain

I searched and was not able to find an answer. I have a number field that I want to return as text, and I want to force 2 dec places. ie 10.00, I do not want to change to 10, or 10.10 to change to 10.1... I know I am missing something simple here... Any help would be great.. Shawn

Hi Guys, I'm in a bit of a conundrum and am afraid i'll be thinking about it for 10 hours before i come up with something workable. I need to do hierarchical style finds (and privileges)... Essentially any single person (or supervisor) within the scenario can 'own' a record... but when isomeone does a find, i need them to find the records owned by everyone below themselves (including themselves) in the hierarchy. The people who create the records essentially own them so an account id from the accounts table can be placed in a creation field... Examples: 1) When supervisor 2b does a search, all their records are returned, aswell as all the reco…

I've been modifying Business Productivity Solution from Filemaker to create Sales order Solution. I can't seem to figure out how to decrease Amount in Stock in Inventory Database from Sales Order. Any thoughts?

How can I automaticaly create a record ID that has the last 2 digits of the current year followed by sequential numbering that updates itself to begin back at number 1 when the new year begins. Example: 0701 0702 0703... Next year the records would begin to number themselves: 0801 0802 0803... Probably simple for most of you, but I am having a hard time. Thank you

I have built a database for our church, and I’m trying to create a system where we can print either envelopes or letters to go to our members. I have created separate records for each person and a field called “family id” (a number) for each family. Now all I want to do is find husbands and wives and put them on one envelope/label (instead of sending a letter to the wife AND the husband). Is this hard? Seems like it should be basic. But I can’t figure out how. And I’ve searched boards, watched tutorials. But to no avail. Can you help? Thx either way. Jeff Krieger Phoenix, AZ

Win XP, FMP6, FMP8 Adv 1) I have an older(.fp5) 5-file solution and am analyzing it on two WinXP machines each with FMP6. On one machine under the "Window" menu in the FMP toolbar, the five files are listed WITHOUT extensions (i.e., File1, File2, File3, File4, File5). On the other machine under the "Window" menu in the FMP toolbar, the five files are listed WITH extensions (i.e., File1.fp5, File2.fp5, File3.fp5, File4.fp5, File5.fp5). 2) Also, on one of the machines I have occasionally experienced the same (seemingly random) extensionsWITH and extensionsWITHOUT behavior while viewing the files at different times. What causes this, and does it matter? D…

I have a text field that may or may not contain dollar amounts embedded in the text. For example, a text field might read: "Expenses related to our trip included $12.50 for lunch and 6.22 for supplies. At 6PM, we had a dinner that cost 21.85. Our only other expense was $6 for cab fare." What I want to do is parse the field and extract all amounts into a companion array within that same record. The parse rules would be: If it starts with a dollar sign, then it's definitely an amount (with or without a decimal point) If it starts with a number only, then it must have a decimal point I've searched these forums and read about parsing, but nothing seem…

Hi everyone. Where I work, my employer logs employees' absences by recording the start and end dates of each absence period. On a screen showing a single employee, these dates are visible in a portal. Is it possible to DYNAMICALLY (i.e. not via a script) display these absences in a calendar? For example, if the portal shows Start date---End date 2nd Mar, 2007---3rd Mar, 2007 5th Mar, 2007 then the employee's calendar should show 1st Mar 2nd Mar x 3rd Mar x 4th Mar 5th Mar x Thanks

Hi! The current project I'm working on is quite a doozy. Since I've found this is a tough nut to crack, I thought I'd share the problem so that maybe some of you can give suggestions (and help me out a bit) or just have fun mulling it over if you have the time! Problem background: The secretaries in my office maintain our contact database, which is constructed and edited in Filemaker 8. The database does not really contain any complex calculations, only fields for data entry (name, address, phone#, etc.). We've had this database for several years now, and it's grown hideously large (8000+ records) and difficult to maintain. Recently I was asked to implement…

I have a need to sort records for a given period (one month) using three or more variables, and cannot figure out how to do it. There are 10 vehicles, each with an ID number, plus another few privately owned vehicles, used for deliveries that are identified with a route number. I need to make up a monthly report showing the number of miles each vehicle has driven on the different routes, plus a total of the miles driven on the routes for all vehicles, with a separate total of miles driven on the different routes for the private vehicles as a group (don't need to break down the miles for each private vehicle, just the total for them all on each route). I thought I coul…

My users can be bright sometimes... but they also have very annoying habbits. One of them for example is putting a zero in front of single digit numbers. That is, i have a postal address "street number" text field set up. It allows for entries such as 1A, 214/3 etc etc. But if a user enters 03 for example (as they do even when i've told them not to), searching for 3 obviously doesn't work. I need either a field validation calc or an auto enter calc. I'm thinking of using something like the following: If( Substitute(TextStyleAdd(StreetNumber ; UpperCase) ; ["A" ; "" ] ; ["B" ; "" ] ; ["C" ; "" ] ; ["D" ; "" ] ; ["E" ; "" ] ; ["F" ; "" ] ;…

Hi clever people ... I have a question, I've developed a timetracking system for my comapny, and they ahve asked me to impliment a way to flag when a job has nearly reached its estimate. For example, job is quoted at $500, flag it as "nearly at quote" when it gets to $400. My thinking would be to have a calculation that works out the percentage of the quoted amount and when it reaches "X%" have a another field display the message?... now, the question is , how the bloody hell do one do that? Thanks in advance. Al

Anyone know of source for a database,exel file, table, text file, etc, that I can use to autoenter gender based on first name (obviously some names are only best guess). Searched a bit and have not found any sources. Rod

i made a button which creates a new entry. the problem im having is that i need that button to create the same entry in two different tables. example: i click "new record" on my job schedule table, i need that same record created on my job specs table.

I'm looking for advice on how to create the best backup and restore function for a runtime. I have 2 types of DBs. One will always maintain the existing data while more is added and the other must work more like a typical program where the user can save and close the current project and then load another one to work on.

I am not even sure if this is the right section of the forum.... using FM7 OK, here is my problem - we have a database with customers that are mapped in DeLorme, our drivers use GPS to get to them, upon arrival, they need to fill out a report which is in filemaker - now, one would think by looking at the customer number on the DeLorme map, and then searching for that number in FM7 they would enter their data into the correct customer record - well, seems like dislexia is well and kicking in our company.... the best way to eliminate the problem would be, if there was a way to use the hyperlink that DeLorme offers in a map-tag to jump to THAT customer in FM..... …

Hi all, A couple of question here; 1. If a database has images linked (not embedded)... and then we decided to change the original directory structure to something more convenient. Is it possible to tell filemaker how to refresh all image links (without us having to go in and re-assign just about 1000 records) ? 2. When we drag and drop an image (say) from our desktop into a container field ... that image becomes embedded. Is there a way to have it always link; by default ? 3. When we embed an image it seems to always be stored as a BMP occupying the largest amount of space in the database (is this who Filemaker works by design) ?

I'm trying to create a visual data entry process for my users. I would like to create a way by which my users hit a button to create a code of DNA. I envision four buttons to sequentially insert data into a field in a particular color. G [black] A [Green] T [Red] C [blue] I've tried to think, using my limited knowledge, of a way of doing this with no success. I tried using a temp field to insert the data and a second display field set to autoenter data from the temp field with various text formatting calcs. I know this should be straight forward in some sense, but I'm just not finding the right way to do it. Help! MJ

Hi! Is there a way to count records in the current relation in filemaker? I need to count the records wich have a "1" in the Field named Status in the current relation. Thanks. Ron

I am trying to figure out a way to force users to enter data into a field that's in a portal. Setting the field to Validation - Not Empty won't work since the field is in a different table than the data-entry layout. I've tried setting up a calc field (in the data-entry layout's table) to populate when data is entered in the portal's field, but there isn't a way to set the Not-Empty validation on the calc field. I tried Auto-Enter, Calculation and Lookup in yet another field, based both on the first calc field, and also the field in the portal. This isn't working for me at all. The Auto-enry field remains empty. Am I missing something that's really obviou…

Concerns: Filemaker 8 for use on pc What I need is a way to return to a specific field. If I may compare my question with Excel, I need to store the reference to my cell. Which would be in this example B9, or G4 or.... So I've used Get(Activefieldname) and Get(Recordnumber) to retrieve the position of my field. Now here's the problem. Going to the record number is no problem as the "go to record" script step allows me to specify "by calculation" (where I can refer to the value I stored using Get(recordnumber). But how do I go to the right field? I've stored the field name retrieved with Get(Activefieldname) but "go to field" doesn't allow m…

Is is possible to insert a "get scriptparameter" function in the following If statement, to replace the two instances of "standard 1": If [standard 1::highlight_a_tempkey = standard 1::zk_standard_p]

The new daylight savings time will be in effect this year. The new DST makes March 11th. and Nov. 4th. the days that clocks are set forward and back etc. How is this going to impact FM? What will happen? I did a search through the forums and found no discussion on this so I thought I would toss it out there for left brain food.

Hi, I have a customer who wants me to give them the ability to add a button that when clicked will navigate to the U.P.S. tracking web site and automatically populate the tracking number dialog box, check the "agree to UPS Terms" and then <> so the tracking action will be activated. I can easily script getting to the site with a tracking number but that is as far as I can get. Do I need to use Windows Scripting to do the rest? HAs anyone ever done anything like this that I can borrow shamelessly from? Thanks, Steve

I'm still searching the forums for answers but it hit me that this might not be possible. I need to send a solution to someone and have it expire after a limited amount of time or recoreds. The catch is they need to have unrestricted access. They need to be able to add/modify tables and relationships. I've seen some limitations done with scripts but to have full access to tables you must have full access to everything. So my guess is that it cant be done with scripting. So the question is.. Any suggestion on what I can do? Thanks for any suggestions in advance.

I need to know if there is a way to do the following: 1- Set privileges that would only allow "User X" to be able to Create a new record without having access to existing records? 2- Is there a way to save Schedules? I would hate to have to redo all my schedules if I had trouble with my machine that required a new system install. thanks!

I have a document and image database and some of these documents are templates (for example an Omniform lease agreement template) I want to have the user of the solution to be able to click duplicate and it duplicates the record and the file referenced in the container. So far i have no problem developing a script that will do it ( iam using the Troi File plugin) BUT I keep getting an error saying can't find the file to copy. Now i am using a GetAsText function on the computer and getting back 2 different file paths and i believe 1 is PC and 1 is Mac however my knowledge is limited on retrieving a filepath a determining whether its a PC or a Mac. I have attached…

I recently provided this calculation which removes blank lines in a list of phone numbers: Substitute ( Trim[color:green]All ( Ph1 & " " & Ph2 & " " & Ph3 [color:green]; 0 ; 0 ; " " ; ¶ ) This works for phone numbers because there are no spaces in the example provided. But if the numbers are (515) 342-1135, it will break. I wanted an alternative in case there were spaces (which would accept multiple-word lines) so it could be used with addresses etc. It kinda looks confusing but this would work: Substitute ( TrimAll ( Substitute ( Ph1 & "~" & Ph2 & "~" & Ph3 ; [ " " ; "/" ] ; [ "~" ; " " ] ) ; 1 ; 1 ) ; [ " " ; ¶ ] ;…

Hey all, I'm reading the White Paper for FMP Novices by David K. (Foundations Database Systems) And I am learning a LOT. I am rebuilding our database (currently, it really is a perfect example of "angry fruit salad"!) I am only permitted to add necessary fields to existing tables, and create a proper interface to the database. (it is a mess like you would not believe!) I was thinking about user preferences, for relationships and such (my plan is to have a portal with a list of all items, for example, all quotes, listed in a portal on the screen, with a transparent layer on top of each row, to execute a script to pull up the entire quote when selecting it …

Hi, I was wondering how other people achieve what seems to be a very common task: copy/move a record (and all the necessary fields) from one table to another related table? Think of 3 tables: PEOPLE, TEACHERS and STUDENTS. The people table is where every new record is created. It has all the fields it needs for what is known as PEOPLE. People may become TEACHERS or STUDENTS in the future (you do not know it in advance when you create a new record, that being the reason to create a general people table). The TEACHERS and STUDENTS tables each have additional field that are only pertinent to a teacher or a student, but I would also like them to have some field…

I have a field with years (it is a text field) in it, ie: 1970, 1971, 1972 1973 and so on. I have it set up as a check box field so a user can select muliple years in the field. I then have a calc field that replaces the returns between the years selected with a slash / so it is displayed better on other layouts. The problem is, if the years are selected out of order, say 1972 then 1973 and then 1970, the calculation displays them in the order they were selected. Can I change the display order of the calculation to be in an assending order no matter what order they were selected in? Thanks for any help. Shawn

I have attached a file that has a simple calc that extracts the hour from a time field. My final solution is for a FMP5/6 client. My Formula is: Abs(Mod(Hour(TimeStart)+12 , 12))& If(Hour(TimeStart) < 12, "A", "P") In FMP5 i can not get the proper result. timeIn5.zip

Hopefully someone can help me or point me to an article that can help. FMP has a built in function (PMT) to calculalate a Mortgage payment based on LoanAmount, InterestRate, and NumberOfPayments. However, I would like to figure out what the LoanAmount would be if the known figures were Payment, IntertRate and NumberOfPayments. I have manually entered the formula for the PMT function as shown below. It has been so long since I had a math class I dont know where to begin to try to solve for LoanAmount. The file containing the formula is attached (FM7). Any help appreciated. Loan = 100,000 InterestRate = 7% LoanYears = 30 Let ( [ IntDec = InterestRate * .0…

Perhaps this is not the best are of Fm Forums for this post, but I was wondering if anybody could provide me with information, templates and calcualtions needed for billing on the HCFA-1500 form. It seemed simple enough when launching into this task. I keep coming to dead ends and infromation that is inaccurate. So any info would be appreciated.

FMP8Adv, WinXP I've seen John Mark Osborne's articles on this subject in Advisor magazine and his method is pretty neat, but he uses globals to create custom letters. I'd like to be able to create custom letters in a multi-user environment and have individual users be able to create/save/modify/recall/control/etc their own custom letters. Is there a way to do this within FileMaker 8 and without plugins? TIA Lpm I hope I'm asking this in the right way...

I've been looking over these calendar solutions, and am having a hell of a time wrapping my head around the sample files. Basically, I am working on a welcome screen to the new database i'm building, and i would like to make some simple "today's events" and the next few days before and after. We don't need a whole calendar (there are lots of events, and very little space on the screen for a complete month, and we don't need that really) Basically, i'm trying to get some help or find a sample database on how this would be done. I'm at a complete loss for how to create events and make them show up on a screen, possibly with arrays to store the data. I know i'…

:) HI There, I been working on one fo the project were master file is creating portal as follows. fields = 1)Day 2)Date 3)Driving Hour (can't be <0 & >12hour) 4)Working Hour (Can't be <0 & >24hour) 5)Start Kilometer 6)End Kilometer 7)Total Drive Hour 8)Total Work Hour 9)Start Kilometer (First day open kilometer) 10) End Kilometer (Last day close kilometer) 11) Kilometer travelled : Solution is like: User enters the detail of how many kilometer traveld by driver in day one. and then day two so the End kilometer on the day one will be autoenter as a start kilometer for day two. : Question: 1)…

Hi all, There is this problem: 1. There is a script that brings up a popup and pauses for the user to act within that popup window. 2. There is a cancel button for the user to close the popup (and the underlying script resumes with the rest of the steps) 3. Now if the user presses the 'X' button of the popup window (the standard Windows button) ... then it forces a close causing the underlying script to keep waiting (the "continue" button appears on the left; the status area) Is there any type of error trapping that can detect this problem ? I would like to know when the 'X' button is pressed so that I can treat it as a 'Cancel' command. Thomas P…

Ok, I am a novice at this but I am trying.... This is what I am trying to do. I have a field (field 1) with a dropdown list of items, lets say Cars. I want field 2 to list the prices of those cars when I pick it from field 1. Basically, if you pick Ford Focus from field 1, field 2 would show $21,032. Also, field 2 should be editable. Thanks guys ! JT

Ok here we go again In one of my tables I have a date field that has a drop down calendar attached to it to enter a date. The field is named consent_expiry I want to add some hidden text or similar next to the field on the layout that will show the following messages when the dates are nearly or have expired. When the consent_expiry date has 30 days left to go the hidden text will show “The consent for this record will expire in “however many days are left”. Or if the consent_expiry date has passed then the hidden text will show “The consent for this record has expired and needs to be renewed” Can this be easy achieved? Advice much appreciate…

Hi, I would like to be able to calculate the sizes of the parts of an assembly based on the width,height and depth of the finished product. There are 43 different assemblies with differing part lists for each. For example, each assembly has a back but the size of the back is calculated differently based on which assembly is being built. One assembly may call for a back that is (width - 3) by (height - 1) and another may specify 2 pieces (width - 2) by (height - 1)/2. I would like to save each formula with a record describing the assembly type and part name so that the part size will be calculated when the finished width, height and depth are entered for the assembly. I…

I've got this rather complex calculation that I'm supposed to do to store a value in Filemaker. I actually have the statistition who developed the formula working with me on this and he tells me that I need to either be able to use the log base 1.21 or be able to interact with an R script to get this calculation done. I only see the option in Filemaker to use log base 10 and the natural log. Can anyone help me with this? Is there a function that does the custom log or perhaps does anyone know how to interface Filemaker with R? Is there a plugin out there? I've been looking for hours and I haven't found it. Maybe I don't know what to search on.

I am trying to create a custom function or calculation to no avail, Please help me! a conditional sum of four fields but only if a related field has a specific value. Kind of like a sum on a found set, without the "find". I would have several of these fields for various values of the related field The logic works like this sum (field1; field2; field3) if (tableA::fieldX = "1") I found a "SumConditionalFields" custom function, but could not adapt it to my needs ....

Hi guys, my first posting over here, used to post on the old FM forum. I was wondering if anyone has already done the calculation for working our ebay auction fees and would be willing to share. I'm primarily wanting it just for the final fee part and not the listing fee, ie I input the final bid amount and it returns the final fee. eBays fee structure is as follows: £0.01 - £29.99 5.25% for the amount of the highest bid up to £29.99 £30.00 - £599.99 5.25% of the initial £29.99 (£1.57), plus 3.25% of the remaining closing value balance Over £600.00 5.25% of the initial £29.99 (£1.57), plus 3.25% of the initial £30.00 - £599.99 (£18.53), plus 1.75% of t…

Hi all, (in FM7) Is it possible for a script to know which record the user is looking at right this minute ? Thomas

Hey... I need a quick calc to determine if a number is whole = True, but if it is a fraction it is false...any thoughts? Example 32 / 4 = 8 (True), but 33/4 = 8.25 (False) Thanks in advance for your thoughts. Mike

I have a problem that is messing with my head. I am building a solution in 7 languages. Thats fine but it will also be used by a company with 3 locations. UK, Germany & France The problem comes in when the contacts addresses are entered. I have a database with 200 countries and its great but when i enter the address from the UK and select from the country list (which i would like to be comprehensive my users may enter the name of the country in there native language or the localized language (if they got a business card from there client). To add to this issue the native name in each 3 locations is different. This makes for a very long li…

First of all I would like to say a big hello to all members of this great forum. I am really, really new to filemaker and could do with a bit of scripting help here. I am wondering is it possible to write a script that can delete and would pop up a dialog to give the user the option to delete the file stored somewhere on the HD in relation to the file stored in a container. Just don't want to have to look around the HD for all those idle files that I will not use again. Thanks in advance. h

I've been using filemaker for basic scientific datalogging purposes for a little while now and need some help with the logic of generating a report. I'll simplify the problem to just one table with two fields: Genus & Location Here is an example chunk of the data contained in this table: Genus;Location Porites;reef1 Porites;reef1 Porites;reef2 Porites;reef2 Acropora;reef1 Acropora;reef1 Acropora;reef1 Acropora;reef2 Acropora;reef3 I need to generate a report that summarizes by location, the number of samples for each genus found at that location. So for example, the report that I would like to see would look something like thi…

I have often used the FileMaker URL command to open hosted databases (e.g. fmp7://[email protected]/DBName). What I would really like to do is open a specific record on a specific layout in a database. For example: [color:blue]fmp7://[email protected]/DBName&-layout=LayoutName&-rec=5814 The reason is: I have FileMaker records published as events to an iCal calendar. I would like to place this record specific URL into each event so I can click on the event's URL and auto-show the record back in FileMaker. Is something like this possible in FileMaker 8.5?

Hi all, Quick question: Suppose we use the Max() function to get the highest date stamp within a table. Now ... once we have found the highest date how can I get the ID of the record that contains this date ? ... and then use this ID to access a sister field in that same record ? (P.S. I do not wish to add a mirror table in the graph and do this via relationships)

Hi all, Silly question... I am trying to consolidate/merge some of my scripts ... but ... is there a way to copy/paste portions of one script into another ? (in FM 7) I don't think there is, but did I miss something ? Does FM 8 has this ability ?

I have to be missing something really obvious here. I have fields timestamp1 & timestamp2 and I need something like this.. timestamp1 - timestamp2 = hours worked So I can do the hours worked * rate calcualtion. But for the life of me I can not figure out how to do timestamp math with filemaker. Help? please? Thanks! -jim lee

Hello, I'm a new FM user. Glad I found this site, I'm hoping someone can offer some answers that I haven't been able to find. I'm trying to modify a pre-made invoice solution in FM 8.5 advanced, one of the scripts is named "-" and it has steps like this one" //set field [sales Orders::bill to name;"name"] I know that this inserts the word "name" into the field but I can't find out how or where this script is executed. Is this something only a mac can write? It was really hard even finding this script.Until now I thought the "-" was just to separate script steps for easy reading.I can't find anywhere in the solution where something calls up the scriptnamed …

Hello. I'm trying to do a calculation and can't seem to figure it out. My objective is to determine the state that the user is from when they logged in. I've created a global field using the get(accountname) function to get the current accountname.Next in the same table, I have created an accountname field and state field which holds the respective data. So once I have the current accountname that is logged in, how do i compare that with my database of accountname and get the state they are from?

I have a client who has requested something that I admit I have no idea how to accomplish without setting up a "side" database for him. He wants to make it so "certain records" created by a particular employee are not visible to everyone else using the same database. The values in those "invisible records" still need to be part of summary reports and the like, so it is not like I can exclude them from the main body of the data base. Does anyone have a suggestion for how I can approach this? I am wondering if I am making more of this request than it really is. If it were possible to password protect individual records that would certainly solve the problem. …

This may not be so simple as I thought. I'm trying to eliminate the transfer of data from the database to a spreadsheet. The "grid" is some 30 x 35 matrix on the spreadsheet. Example: if the program = "Beach Walk" and the grade level was = "Pre-k/k", put the total of the programs taught in a matrix grid square (this would be a numeric container field???) say, A1. So far I've been able to come up with the total count of all "Pre-k/k" programs by using Field Definitions: (first finding the individual records that fit the criteria) Field Name: Sum of PreK K Type: Calculation Options: If (Age Grade Level = "Pre-k/k";1;0) ['Age Grade Level' is a d…

Is it possible to create a calculation that would return the k or MB size of a record or the size of the content of a container field? Get(ActiveSelectionSize) is for text only. Thanks!

I want to be able to email the results of a find,including multiple fields over multiple records. I would like to be able to include the data in the text of the email. Is this possible?

How do you set up a table of images? I have a list of students and I would like to set up a photosheet of the class...just a table of pictures. I found the portal allows me to do a single and then another below...I would like them to be 3-4 across (columns ) and then 8 rows. For the life of me I cannot figure out how to do that in filemaker. :B

I have a field that is has data in it like this: AMBMasaiHan_NHF0032.L.aif I would like to be able to trim out everything in that field before the "_" and after the first "." from the left, leaving "NHF0032" as the only data remaining in the field. The variables are that the number of characters before the "_" and after "." are never constant. Is there a way to do this using the Specify Calculations in scripting? Some sort of advanced trim? Thank you, Matthew Wood

I recently upgraded from 7 to 8.5. A script that I have been using for a long time in 7 no longer works now that I am running under 8.5. The step that fails is: Export Records["data.tab";Windows ANSI] File Type is Tab-Separated Text Files (*.tab) Field Export Order is string1. String1 is Type text with a string of data separated by tabs in the field. It has only one record. When it writes to data.tab the tabs are lost. If I stop the script just before the export step and cut and paste string1 to data.tab, and then skip the export step then the data works in subsequent parts of the script that depend on the tabs being there. Dave

Hello everyone. I'm tinkering around with an idea and hoping that someone might be able to help point me in the right direction. I'm trying to come up with a solution whereby if a user searches thru a database and comes up empty, the user is then able to submit something like a 'ticket' which is valid for a certain time period(lets say for example 3months). What this 'ticket' function would do is that it would check any new records that is being added by any other users of the db for that particular period and if there is a match, the system(server) would then be able to send an email notification to that user who submited the 'ticket'.

Hi! This is how I’m doing it until now: If(IsEmpty(kEMail);0; If(Position (kEMail;"@" ;1 ; 1 ) > 0 and Position (kEMail;"." ;1 ; 1 ) > 0;1;0)) This Calculation is perfect until it comes to deleting the Email field. Then the Validation won't let me delete the field. Any solution? Regards. Ron

I have a simple calculation of gains that student's have made on test scores. My problem is if the first score (field) blank it calculates as a - negative number. If the second score (field) is blank it gives me the number that is in the second field. So it looks like a gain even though there is no score to compare to. Test 1 score=100 Test 2 score=blank Gain/loss field shows [color:red]-100 Test 1 score=blank Test 2 score=100 Gain/loss field shows [color:green]100 I want it to show and empty field or a dash or something indicating that there is no comparison. Hopefully I have made some sense with what I need. Thanks in advance!

I'm not sure how to do this, or if it can be done, so please forgive my ignorance. I want to be able to calculate a number based on the value of a few other feilds. The values of the other feilds are either "A","V", or "F". If the feilds all have a V, it would calculate a 10, if they all had F's, it would calculate a zero, if it was evenly mixed, there would be a 5. Does anyone understand what I am trying to say?

Hi, I am all new to Filemaker Pro. I have my own business and developing a database with my customers informations. I have a questions I will probably laugth about in a few weeks after some experience but: I want my customer reference id to be automatically created using the following informations: -a set of letters (in this case, "NC") -the first 3 letters of the last name of client -the first 2 letters of the first name of client For instance, if customer is named Mr David Jones. The id of customer should be NCJONDA . Do u get it ? I hope u'll be able to help. Cheers David

Hi, I am having trouble understanding aggregate functions. In Filemaker The Missing Manual it states: "If you rever an aggregate function to even one related fiel, Filemaker aggregates that field's values from EVERY related record." But say I use Count(LearnerID) in a calculation, it only counts the current active record. Not ALL records. How do I do that?

I have a list of words that I need to manipulate in two different ways. In order to do what I need, it seems that I need wildcards, but i can't use them inside formulae, because FM just reads the wildcard characters as what they are, not as wildcards, in this instance. What I am trying to do is the following (where "?" is wildcard) IF (word ends with "e?") THEN (add "i" to end of word) IF (word ends with "o?") THEN (add "u" to end of word) IF (word ends with "a?") THEN (add "ı" end of word) etc For example: I want to change input "bet" to "beti" , but "bot" to "botu" , etc. Without wildcards, this is impossibly tedious. Any suggestions? …

I think i have run into a problem here. I have a line item table that is viewed via a portal so formulas of finished goods can be calculated and I have just created a Batch order table that needs to read the Weight Per Gallon from a summary field a few tables away but i keep getting a message about Not allowed to do such and such with summary fields. And if i go to the table that has the portal in it and create a calculation off the line item summarys and i then relate the Field in the Batch Order Table to it nothing shows up. I think i have messed up the relational integrity of this solution. Can someone take a look at this for me. Basically when i choose a Pro…

If I have a list of values, lets say: 100 125 180 300 340 378 and I use the max (field) I get 378 max (field) -1 =377 What I want is the second highest value 340 Is there a way to say find the max, then back it off one occurance? Thanks Dave

I'm always comeing here for advice. THis is by far the best FileMaker Forum anywhere. Any advice would be appreciated. I am tying to figure this problem out. First of all, It seems FM doesn't like my calculation. Case ( ROCPG3 ≥ 001 and ≤ 029 ;category ID = 40; ROCPG3 ≥ 030 and ≤ 032 ;category ID = 41; ROCPG3 ≥ 033 and ≤ 040 ;category ID = 42; ROCPG3 ≥ 041 and ≤ 044 ;category ID = 44; ROCPG3 ≥ 045 and ≤ 050 ;category ID = 45; "n/a" // default value ) Next, i was wondering if it is possible to contain in the same calculation, set a 3rd field (category ID Name) to display a value from a defined list based on the value calculated abo…

How do I get rid of or hide question marks. I'm summarizing store audits. Each store gets scored on their audits. If they have no audits during one particular month, the getsummary function returns a question mark that I can't seem to hide. Can anyone help? thanks! Bob

I have data in multiple fields that needs manipulation. Here are the fields: Field 1: 50751327 Field 2: /MRW/Animation Episode 101/Audio Files/101act2_ADR.L.aif Field 3: 560.083435058594 Field 1 is the file size in bytes, which I want to display in Megabytes instead, e.g. 48.4 MB. Field 2 is the file path, but all I need from it is the file name, so I would like to trim everything out of the field except the last directory tree which is the filename, 101act2_ADR.L.aif Field 3 is the time in seconds, which I want to display in minutes and seconds instead, e.g. 9:20 Can all this be done with script formulas? I could try manipulating the …

Ok I've never made any calculations that had to parse before but i have alot to import and i definatly don't want to enter this stuff manually. Does anyone know any good in-deapth resources for parsing data?

The solutions to (Topic#181055) are close to what I need. Is there a way to make that color change happen to all fields or selected fields globally? I have a field called (Type) with a value list of: Invoice Memo The default text color is black. I want to display a different color if the value chosen is "Memo", and change back if the value is changed. Thank you, Lee

THE PROLOGUE Hi kids - it's me again in the middle of a 42 hour FM coding bender... ...still using FM 5.5v1 by FORCE (yuk) Well, here's my painfully obvious parsing question. I probably know the answer but my brain has long-since fried and I just can't come up with it. THE QUESTION I have records with with city, state abbreviation and zip+4 code all within the _ADDR2 field (I didn't set it up this way - honest). Here's what the data in this field looks like: LAS VEGAS NV 89117-2450 GLENDALE CA 91209-3493 HENDERSON NV 89015-1024 STOKE-ON-TRENT KS 45109-5612 NO LAS VEGAS NV 89130-4108 MERRICK NY 10012-4054 ...etc, etc, etc. I…

Hi gang. Currently I'm forced to use 5.5v1 for this project. THE SITUATION: What I have is a field named _OWNER1 that contains rather long strings of text (owners names). In my calculation field named _CALC_corpown I need a YES or NO result if the _OWNER1 field contains an instance of " CORP " or " CORPORATION". THE PROBLEM: The text strings within _OWNER1 for each record (and thus, the location of " CORP " or " CORPORATION) vary greatly and look like these (as examples): ACE SECURITIES CORP LLAMA LOAN TR INTERNATIONAL LLAMA CORPORATION OF AMERICA LLAMALAND CORP THE CORP OF LLAMA MANAGEMENT INC ...etc etc etc. THE QUESTION: Does anyone h…

I have a table of members Each member belongs to a chapter They have a membership # If the member is in chapter 172 Then the first member # is 1720001 and so on I would like to auto enter membership numbers when I enter a new member What calc would work here? It would have to find the highest number used first, within the chapter, then add 1 If I used something like max (membership#) + 1, it would give me the highest number used by all chapters. Thanks, Dave

How to calculate this in FileMaker: % calculate moon's age in days IP }:|= normalize( ( JD - 2451550.1 ) / 29.530588853 ) AG := IP*29.53 What is substitute calculation for [color:red]NORMALIZE in FileMaker ? :qwery: Ino

May not be the right forum.. but have a notes database, the emp pushes button it goes to the last portal and into the Notes field they type.. meanwhile before this step they've click a button to start a timer.. "Which is only a stamped start time and end time and a calc to get duration." however the catch 22 is that my boss naturally wants the notes to be secure so no one can modify them.. however when the user clicks the button to end the time.. this commits the note record which is setup on an Event Script to autopopulate other fields.. and time taken for writing note must be caputured. See my problem? I heard somewhere that they're may be a way to run a clock from with…

[color:blue]Quick explanation: I'm looking for an auto calculation on record creation for the next available check run day. Now... a bit longer explanation of my conundrum: I get an invoice and I process it through my FMP7 relational database before putting it into the financial system (yay calculations and copy/paste). Checks (aka payments) are run on Fridays (unless there is a holiday) and the deadline for entry into the system is the Wednesday 9 days prior (also unless there is a holiday). The Wednesday deadline is specifically noon, but I can work around that if I can't figure how to add that into the calc. Example: For a check run on Friday, Novemb…

Hello! I am going insane trying to get the following function: I need FM to automatically fill in a Bank transfer sheet. I’ve made a layout with all the fields set out to fill the sheet. These fields are all single Global fields because I can’t get the numbers in the right place on the layout otherwise. What I want to be able to do is, enter a figure in the Amount field in any format (see below) and get FM to sort out the Cents (.00) via a calculation in Script Maker and add the Amount to the global fields. E.G: 12534 to 12534.00 15684. to 15684.00 48652.4 to 48652.40 The reason for this is that the Bank sheet always needs the Cents as well. …

Hello everyone - my first post! At school I have budgetary control over 5 areas - a sizeable amount of money. I have customised the included Purchase Order template in 8.5 Pro to produce these orders for me. My question, how do I get FM to do the following: As a purchase order is created, add the total of this order to the total of previous purchase orders - a running total of my spend in each budget area. I'm sure this must be fairly straight forward - I just can't get anywhere with it at all! Once I have this running total calculating correctly, I will be able to subtract it from my annual allocated budgets to see how much money I have left in…

I have an "invoice" form with a Purchase Order field. There is a portal that lists "line item" details, including a status field in which backorders are designated. How do I create a new invoice for a specific purchase order number that also shows only the open backordered items in the portal. This must be a common problem for invoicing programs, but I can't think of a way to do it. Thanks for any help I can get.

I created a text field in my database and set it to be Global, but the field always clears itself. If I relaunch FMP (for example) the field will no longer have the value. Are global fields not meant to keep their data?

I have two "what if"s I need to calculate.. The customer wants to purchase an item with price (P) and can only afford a monthly or weekly payment of (X). He can start savings at interest (S) or take out a loan at interest (L). How long would it take and how much would it cost for either choice? I'm looking for the two functions.. How many payments of (X) to a saving goal to (P) -and- How many payments of (X) to pay off a loan of (P) -given the other paramiters. Anyone know where I can come up with these two functions? Many thanks in advance!! -jim lee

OK I'm stump.Hopefully this is the correct forum for this post. I have to modify or create a new invoice db that can provide custom line item prices based on customer. So I have a typical db structure Customer, Invoice, LineItem and Inventory db's all with the proper links and portals, but here is my issue. This is a table representing items broken down by Item# and different size (A,B,C and D). Item numbers are a range for example 1000 thrugh 1999 is a range of item numbers each with different description. Customer A Item# ----- A ------B------C--------D 1000-1999 $2.00 $3.00 $1.50 $2.50 2000-2999 $5.00 $8.00 $2.00 $3.50 3000-3…

I have a table used to enter meter readings. one field is Last reading and another field is current reading. I would like the last reading field to be updated by the previous current reading field. Any Ideas? i know that i am not explaining this very well, but it is late and i am tired. Thanking you all in advance Fred

Here's a curious behavior. Try creating new records in the portal by selecting a value for ProductID, OR by selecting a value for Somefield. Watch the unstored calculation cCustomerID populate - or not. Anyone care to offer an explanation? CustomerPrices2_copy.fp7.zip

Situation: My father owns a winery. There are storage tanks on the property used for storing wine. Sometimes he stores his own wine there, sometimes he rents the storage space out to other wineries. I am attempting to create a database in order to maintain compliance with the government and to track who's wine is in what tank. The structure of the database is very similar to an invoicing solution. I have 5 tables: CUSTOMERS, STORAGE, WINES, TANKS and LINES. I have included an attachment of the file for anyone who needs to look it over. What I need is calculation that gets the most recent tenant so I can display that on the record for the tank. I'm thin…

I am almost done with this database, and I am trying to do something that doesn't seem possible. Of course, everytime I think that and put the question out here, someone seems to find an answer. So I guess it's worth a shot. I have attached the file, and here is the best rundown I can give. When on the FLM (Front Line Manager) layout, you will notice that I have a portal under the tab "Evals Due" that shows all the evals due for that FLM's employees. I created an eval TO for each kind of eval to get these results. That part works great. There are 4 other items I need to keep track of. They are Errors, POW's, ODP's, and SE's. You will notice that I have…

O.K. all, Users log into the database using their ID and password. Once the user logs in I want to run a script. The script is designed to take certain users to certain places, when they log in. Can the login ID be retrieved and set into a global variable so I may direct different users to different places. Thanks Dave

Hi everyone, (file attached with my first attempt at solving this problem) -- I have built a job applicant management system and would like to add a way for users to determine whether a record has been updated by someone other than themselves. I want the modification functionality to be scripted because I only want certain important modifications (like a file upload) to trigger the record as "modified". A few issues I have are... 1. I'd like the user to be able to indicate that they've seen a record on a record-by-record basis AND on an all-records-at-once basis. and 2. I'm not exactly sure how to approach the problem of what happens if a record g…

I have 4 fields ... Date Start Time Start Date Finish Time Finish I want to calculate the difference in Hours and minutes between them (this is either very simple and I missed it, or very complex ... for me anyway). The dates: I have a Mini Calandar for user to update. The Time field: Pop-up list in 1/2 hour blocks (24 hours). Or is this the wrong way to even start?

If person "A" is 10 years and 11 months old (10.11) and person "B" is 9 years and 8 months old (9.8), the difference in their ages is 15 months or 1 year and 3 months (1.3). I'm having trouble figuring the formula for this. Dates of birth are not available. Anyone know how to build a calculation where I can determine the age difference expressed as a decimal (1.3) or using a dash (1-3)? Decimal Person A: 10.11 Person B: 9.8 ________________ Difference 1.3 Dash Person A: 10-11 Person B: 9-8 ________________ Difference 1-3 Sorry if I'm not being clear. I just want to be able to calculate the difference between two fields …

I want to get Index from only in found records and I want to type all in one line (or a sort of summary field) with comma

Hello all, i have a textfield containing two strings "the quick brown [fox jumps over] the lazy dog" . how can i best parse out the content between the [ ] characters? until now i have come up with Middle ( string ; Position ( string ; "[" ; 1 ; 1 ) + 1 ; ( Position ( string ; "]" ; 1 ; 1 ) - Position ( string ; "[" ; 1 ; 1 ) -1 ) ) , is that the best solution? how can i fix this, so that in case there are more than one ] in the string, all but the last ] are converted to some other character, say * ?

is there any data analyser plugin or solution that generates data analysing reports such as pivottable in excel?

I'm helping out with a database in Filemaker 8.5 So I have a list of Charities that apply for funding from a preset list. At the moment I have two tables. The first one is the details of the charities, the second is the list of funding they can apply for and the amount of cash that goes with each one. The funds available on each option are fixed. The charities can apply for 10 out of a list of 43 possible grants. So I have dropdown on the charities table (with 10 repeats) which you can select from the full list of the funds. What I want to do is list the funds they applied for then have a selection for the funds they are actually given. I then like to add the amoun…