Calculation Engine (Define Fields)

I have noticed TRIMALL(field,0,0) is not removing any spaces if I use text sourced from Apple Mail. I am thinking that the spaces might have a different type of coding (Maybe I could do a replace before the trimall). Any thoughts? thanks

Hi everyone I have the need to parse some data but am not sure how to do it. I would appreciate some advice. I have a text field with data e.g. /Volumes/Documents/jsl/Referrals/EP15849245201392441jsl.pdf I would like to grab this data and place it in a variable but strip out the "/Volumes" part Can anyone suggest how I can do this?

I'm sure there's a dead simple way to do this but I can't think of it. I want a calculation, that given a number "x" will return the sum of all integers between 0 and x. I'm only concerned about positive integers. So, Function ( 5 ) will return 5 + 4 + 3 + 2 + 1 = 15. Function ( 6 ) = 21. Any ideas without resorting to a recursive custom function, because elegance. Ah, Google is my friend. I searched for sum of all previous integers and the first hit had this gem: "1 plus N quantity times N divided by 2." So my function is: ( ( 1+x ) * 2 ) / 2

Dear all, I am running a b&b and run into the following issue. I have a calendar with a check in date and a calendar with a check out date. The period of time selected into these calendars should result into the following: 1) number of weekend days (whereas weekend days are definable i.e. friday/saturday or saturdays/sundays) 2) number of work days (also definable: ie weekdays can be defined starting on either sunday or mondays) 3) a total of nights stayed. (check in date - minus check out date) I can't seem to find the way to calculate 1 and 2 and how to set this up. Anybody knows the trick for this? Thanks in advance.

I apologize if this has been addressed previously - I was unable to locate. Is there a way to prevent users from entering data when in Find Mode? I have a couple of users who keep getting mad at me (!) when they spent several minutes thinking they have created an extensive record only to discover they have been in Find mode. I know this is User 101 and I know I am not the first person to face this. I think having a check within FM that displays an alert if data is entered into more than three fields in Find mode, but I am not sure how to integrate that. Is there a script step that checks the mode the user is in? If anyone has any input, I can be found over…

I have 2 fields: STATUS and DATE. Status field has 5 pre-defined values to choose. I change them manually from time to time. However I need one status value to be set automatically when the date value is in future (date is higher than today's).

I have a global field on a Settings table that stores the email address for an outside contact to the system. Sometimes this email changes, hence setting it up in this manner. However, whenever I load the file, this field is ALWAYS blank. Even after editing it multiple times. I've tried everything I can think of - committing records, manually saving records, etc. The file is a hosted solution, but all the other fields on the Settings table work perfectly.. why would this occur? It's doing my head in!! Thanks in advance!

is there a way this can be done using a script what i want is new record request and go to layout function but using only 1 button

I have a look up field which drops down when clicked. The drop down shows me data from another field (i'll call it the 2nd field) in the related table. The look up field varies in length with a maximum 10 characters. I use "courier" font thanks to some suggestions on here as to how to get the data aligned. The issue is that because the look up filed varies in the number of characters I cannot get the data from the 2nd field to align in the drop down list. The beginning of the solution is for me to use the "length" function to find out how many chracters are actually in the look up field. I then deduct the number of characters from the 10 maximum and this gives me …

This seems like it should be simple, but I can't seem to figure it out. Thanks to this solution http://fmforums.com/forum/topic/68663-sorting-titles-ignoring-the-a-an/ I created a field that stripped all of the leading articles from titles I'd like to sort. The calculation works properly, and the resulting "Sort Field" with no leading articles looks right. Then I ran a sort on that field... and found that the titles still aren't sorting properly. This sounds so basic, I'm not even sure how to explain it any further, but the sort I'm getting is only slightly different than what I get when I leave the records unsorted, and I see them simply in the order they wer…

I have a table that lists camera lenses we own [lenses]. The user can click a button and a new window opens displaying a list of quality control entries made for that lens within a portal [lens QC]. A button is clicked on that portal line to open a further window which the user can create a QC check list (or tasks) [advanced lens QC], again within another portal. The fields defined within each task are "task", "status", and "comment". The user can either auto populate a checklist based on a value list, or they can manually add portal (task) lines themselves. Most users auto populate. Upon the user exiting this advanced lens QC window layout (via a button that says Exit) I…

I have a simple number field structure db of lotto numbers and I want to analyse them with summary counts. Â But i'm just getting a bit confused as to what I need to do to get it to work for me. Â Lotto Balls 1, 2, 3, 4, 5, 6 and Bonus, and Power numbers Lotto numbers 1 through 40 are available for 1 through 6. Meaning once a number is drawn it can't be redrawn in that draw. Bonus Ball Numbers 1-40 Power Ball Numbers 1-10 Â So say draw numbers are :- Â 1st, 2nd, 3rd, 4th, 5th, 6th - Â Bonus, Power "26","33","39","12","31","36" - "17","8" "7","14","30","23","2","33" - "5","8" "20","38","3","17","10","16" - "32","8" "14","25","19","38","34","13" - "8","1" "3…

I have a field in my database that displays the html of a google map with multiple locations. I only use it when I want to copy the html - to display pages on the web for board members to view. (sporadically) The problem is that every time I switch pages the field recalculates based on the records displayed, could be one or a few or thousands, and this makes the database very slow. Even if I'm not displaying the map (its tab is usually not active), this field is calculating the html. For now I've removed the field from the layout to alleviate the slow down, but i was wondering if there was a way to have the field but only have it do the calculation when request…

I would like to use a CF in a calc field that needs the table name passed to it. Rather than hard-coding a table name as the parameter for the CF, is there anyway to grab the "context?" I'm not on any layout...just in the Define Fields dialog.

Hello Everyone, I have a field that gives an image height, and an image width. What I am looking for is a way to type in a number and have the field automatically add a " to the end of the number. I believe this is done with a calculation field, but not positive. Thanks in advance for any help.

I'm working on a database where users can search by a person's name. Many of the names in the database have accents and other diacritical marks which it's important for us to keep and display properly. Unfortunately, if a user searches by typing in "Francois," the record for "François" will not be returned. Since it's a pain (at least on a Windows machine) to find and use those special characters, or the user might not even know that André actually has an "é" at the end of his name, I'm looking for a way to ensure that a search for a name that includes a character without a diacritic will find the name that has a character with the diacritic. I've seen a number of inq…

I have this rough assembly to add a phone area code based on the state postcode (zip code) of an entry. Its correct 90% of the time and cuts down on manual entry. However, I don't want it to calculate/generate a result if the field 'Landline Trimmed' is empty, as at the moment it creates "+61 2" in the field as the area code field always contains a value. Is there something I can add to this to stop it generating "+61 n"? Thanks Let( @NumbersOnly = Filter(Landline Trimmed; "0123456789"); "+61 " & Area code from postcode first numeral & " " & Left(@NumbersOnly; 4) & " " & Right(@NumbersOnly; 4) )

HI, I'm trying to create a field which looks up the value of a related Summary field, however I'm getting a value which indicates the summary is being evaluated on all the records in that table, not the related ones. If I set it to lookup a basic numeric field from the same related table, using the same relationship, it works fine. If I make a calc field to get the summary value it gets the correct value (I can then set my lookup field to lookup from that calc field within the same table but that seems somewhat long winded) Can you lookup a summary field value not based on all the records or am I having relationship problems again ? thanks

Hi ya all.. peace to the world! Problem Each line a,b,c,d,e, etc of my text variable is separate by return (13) character. However some lines might come okay as completely blank then end up printing blank lines.. on a large scale input AAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBB DDDDDDDDDDDDDDDDD EEEEEEEEEEEEEEEEEE output look as: AAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBB DDDDDDDDDDDDDDDDD EEEEEEEEEEEEEEEEEE TIA rod

I have a number of text fields where I use the following auto-enter calc to replace existing data to eliminate unwanted leading spaces. But one of the text fields needs to have another auto-enter calc to render addressing coherent and consistent. I am at a bit of a loss as to how to combine the two. Could someone give me a pointer?

I want to combine multiple address fields into one field to copy and paste. It has fields like; Address line 1: Unit 5 Address Line 2: Chess Building Address line 3: 23 Cherry Tree Lane Address line 4: Home Town I want the result to look like Unit 5, Chess Building, 23 Cherry Tree Lane, Home Town. So I've tried using Address line 1 & "," & Address Line 2 & "," & etc But some fields don't have data, so if the field is empty I get two commas ,, Is there are workaround to stop this or is there some way of nesting a function that strips the ,, or ,,, etc occurrences if they exist?

greetings, FM12, Mac 10.8. Customers make payments using various methods. There is a field that categorizes the method, credit card payment category begins with "5-".. So there are two fields "payment_amount" and "pay_type" which exist in the daughter table for payments and charges. I would like to have a summary field for credit card payments only. How to do that? Thank you.

I am trying to run a test on a field which if blank or less than 0 proceeds to copy the previous field into the one I am trying to 'Set'. However, it is not working. I have the following: Set Field[Jan2013;Case (IsEmpty(Jan2013) or Jan2013 < 1;Dec2012;Jan2013)] so this should work to substitute the field if Jan2013 is empty. But what if Dec2012 is also empty? So I have the following underneath: Set Field[Jan2013;Case (IsEmpty(Dec2012) or Dec2012 < 1;Nov2012)] etc for as many months as it takes. Can someone suggest why this won't work? Many thanks.

I have several fields that are auto-enter data via a calculation ("pull info from fields B, C, D, etc."). These are set up as auto-enter calculations because it populates the target field not just with the raw data but often additional verbiage as well as many other factors. The purpose is to take the details of a Quote Request to create a Quote Letter that could be sent to a client. They are auto-enter calculations so that the salesperson can alter the verbiage as needed for the Quote Letter - the initial data is populated as a starting point (and often remains as entered) but allows for necessary customization. (I have inherited this database and the original design…

Database with separate tables for Customer Information and Quote Request Generation. Per desire of coworkers, If they want multiple contacts from the same company, these are separate records in the Customer Information table (I would have preferred to set up differently, but that's what they wanted). I have set it up so that when they enter a Company Name in the appropriate field in the Quote table, it pulls address, phone, etc. from the first matching record in Customer Info and then tab takes them to the Contact Name field which is set up with a "Related Only" lookup of names from that company. However, I have noticed that it takes a couple of minutes for any new names …

Hello Whats the script to identify if the container field is empty

Is there a way to sort the contents of a field? I have a value list of numbers (001, 002, 003, etc.), displayed as a checkbox in a field. For each record, I check off the values that apply. I then export this field (along with others) via XML and an XSLT to transform it into a tab delimited ALE file. The problem is the carriage returns in this field mess up the formatting of my tabbed file. As a solution, I've created a calculation field which pulls the values from my checkbox field and Substitutes the carriage returns for underscores, so I end up with a text string. This field exports beaufitully as tab-delimited and works great. The only problem now is…

Hello is there a way rounding off the amount if there is 1,2,3,4 or 6,7,8,9 at the end for example if the amount is 191 it should be 195 or if there is 197 it should be 200 other example 1856 to 1860 2003 to 2005

Hello, I have one layout (1 record) when i'll print i'll choose the amount of papers i want to print. is it possible to add page numbers on my labels. I've already tried get (pageNumber) but it shows on all labels 1.

Considering I've had FM since FM5, I should know how to do this. A field for sequential serial numbers needs to be amended to include a one-character prefix letter (7,000 existing records from 0001 to 7000). I need to create different copies of the entire database with the serial numbers starting with a letter prefix (e.g., A0001 to A7000). Sorry for such a novice question; everything I've tried has failed.

I have a situation where I am trying to write a calculation with many variables: Buyer Country Buyer State Ship to Country Ship to State Before Date After Date such that part of the script looks like this; ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and ( CONTACTS::CountryCompany ≠ "United States" or CONTACTS::CountryCompany ≠ "Canada" ) ; " Tax 1 "; ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered > Date ( 4 ; 1 ; 2013 ) ) ) and ( CONTACTS::CountryCompany ≠ "United States" or CONTACTS::CountryCompany ≠ "Canada" ) ; " Tax 1 "; ( ( Date_Ordered > g_Tax Comparison_Date 1) …

Hello to all. What I’m trying to accomplish is to make an invoice that reflects the payment from several buyers. My problem is that I don’t know how to calculate the quantity remaining to keep on with the payments. I.e. Selling a house/cellphone whatever. Price: $40,000. There are 3 buyers. 1 wants to pay an exactly amount and the other 2 want to pay the 50% of the quantity remaining. Peter $10,000. 40,000-10,000=$30,000 Paul 50% of the price remaining. 30,000*50%=$15,000 Now here is another problem: Mary 50% of the price remaining. 15,000*50%=$7,500 I attach the file I’m working with so you can see my little progress. As always, thanks f…

I have a container field - 50x50 px. I want to insert an image into this field, that is say 256x256 px. When I do this, there is signifcant spacing around the edge of the image, to the size of the container field. I want the image to take up the full size of the container... I have tried using the getthumbnail() method to resize the image appropriately, but it still has space surrounding the image. Any help would be greatly appreciated! Cheers.

Is there a way to loop through the records in a portal, something similar to getting the next record in found set? GetNthRecord(field:Get(RecordNumber)+1)

I have two tables: Table 1 contains all our company products ("Assets"). Table 2 contains all the Quality Control ("QC") entries pertaining to each individual asset. On one layout I am currently displaying all our assets as a List, and the user has options to search and filter the assets on this layout. When a QC entry is made for a particular Asset, the QC record is time and date stamped. The user is able to search the Assets by date (to show what was QC'd on what date, etc). The search feature works great, however the Asset list also displays the Date Stamp field from the related QC table so the user can actually see the date of the latest QC entry without needing t…

I'm trying to come up with a simple way to add people to projects. Since a Person can be related to many Projects, and a Project can have many people connected to it, I've created a Role table to avoid the many-to-many problem. On the Project layout, I have a portal bringing in the Role data, making a nice list of all of the people related to that Project, and a button triggering a script to add a new role. The table I'm using to catalog the people has fields for First Name, Last Name, a unique identifying number, as well as a calculation field that joins First Name and Last Name. The portal in the Project layout displays the unique identifier and the First Name Last …

I've made a few simple task trackers where start and stop times are entered manually. However, trying to type 2:30 is a pest as typing the colon slows the process down. I'd prefer to enter 2.30. The layout options for time have a : or . separator option, but as soon as I enter the period it says there's an issue. Is there any workaround or calculation that would allow the entry of a period separator, even if this were calculated/converted to a : somehow?

How would I find the position of a quotation mark in a bit of text? This calculation always returns zero, even if $text contains a quotation mark. Position ( $text; """ ; 1; 1)

Hi guys, I'm using FM 5.5v1. I've a field call "Seconds" that simply shows the amount of seconds like this: 88 23 268 409 I would like a calculation that converts them into rounded minutes, following with the above examples the calculation would convert them as follows: 2 1 5 7 What calculation should I use? Best!

Hi guys -- I'm using FM 5.5v1, I want to import an excel file which has a column with many rows that have the date & time together, as follows: If you just look at the excel column it looks like this: 5/11/2013 10:25 5/11/2013 18:19 5/11/2013 20:33 etc. But if you click on each individual cell it looks like this: 5/11/2013 10:25:41 AM 5/11/2013 6:19:12 PM 5/11/2013 8:33:05 PM etc. Ideally, I would like to have it in FM under the 24 hour format as in the former but with the seconds included as in the latter, like this: 5/11/2013 10:25:41 5/11/2013 18:19:12 5/11/2013 20:33:05 etc. The problem is that when I import it, FM com…

Anyone know how I could use conditional formatting to format a column header when sorted by it's particular field? screen shot attached to illustrate what I'm trying to make happen. Â THANKS!!! Â

Hello Please Help what i want is if a checkbox is ticked the Subscribed and Sworn will be cleared in the textfield

Hi guys, I've FM 5.5v1 running on OS X 10.6.8. I've a text field titled "Country 1" with many different records, like this: Italy [ITA] / ITALY - Italian company (active) [iTA33] [Checked] Spain [ESP] / SPAIN - Spain company [ESP29] [Checked] France [FRA] / FRANCE - France company [FRA87] [Checked] etc. I would like to create a Calculation field that removes everything with the exception of the country name at the start (the red text). The only common denominator on all records is that there's a space followed by a "[" after each country name (green). What would the calculation had to be like? And: I've another text field titled "Country 2" with many differ…

I'd like to format phone numbers in the following way: landline +12 1234 5678 cell/mobile (strip leading zero) +12 123 456 789 Any ideas how this could be achieved? thanks

Hi guys! I've FM 5.5v1 running on Mac OS 10.6.8... I hope there's someone out there who can help with this: 1.- I have a number field called "Serial Number". In Options -> Validation -> I want to set-up the validation by a calculation as follows: Only accept if the number is from 8923418450004111000 to 8923418450004112799 OR from 8923418450004112900 to 8923418450004112999. I entered this calculation: (Serial Number >= 8923418450004111000 and Serial Number <= 8923418450004112799) or (Serial Number >= 8923418450004112900 and Serial Number <= 8923418450004112999) But when I test it I get this error message: This field is defined to contain …

I have an auto-calculate field that uses a global field in it's calculation. If I change the global fields (Server Address and Path), I would like to have the autocalc field (photopath) recalculate for all records with the new global field values. What is the best way to accomplish this, a script? The purpose is to have global fields identify the location of the images so that if I have to move the database to a new computer or upload to a server, I can just update those global fields with the new server address and image folder path so all the container fields have the proper reference path. I attached a mock database. Any suggestions? autocalcfmp12.zip

Hello Filemaker developers, I need some help. I have a portal that could have one or many lines. I need to build a calculation field on the main table that can have all the lines of the portal row. The result will be used on and email that will be sent to the client. The result should be like this. Ref. 06125654 IMac 24 1,250€ Ref. 06125654 iPad Mini 750€ Ref. 06125654 iPhone 5 650€ Ref. 06125654 iPhoto 250€ What king of calculation can I use to build this portal row report inside a calculation field? Thanks for you help and support. Best regards, Joao Coutinho

I have a portal which displays a check list for the user. The status for each task are (in radio buttons): Passed, Failed, Comments & N/A. I'm trying to put together a key next to the portal to display the count of each value. Basically, a running count of which tasks were Passed, Failed, etc. I would also like to display a count of which tasks didn't receive a status (i.e. left blank). I've tried multiple functions but don't ever obtain the correct count. Please help!

Hello! I have a table that contains 3 text fields (med, qty, directions) and they can all repeat up to 8 times in a record. So, something like this: MED(1) QTY(1) DIRECTIONS(1) MED(2) QTY(2) DIRECTIONS(2) MED(3) QTY(3) DIRECTIONS(3) etc. I would like to have a 4th column/field that contains a serial number for each of those lines. I don't mind if each record gets 8 serial numbers, so long as I can hide the extra ones. How do I do this? I have a separate layout for the form where I can just hide the extra serial numbers- if I could have help with that though too that would be great. Thanks

Hi, I have a client that wants to see where their Commodity is gong each month; By Country / By Commodity. So based on their List I created a Destination Table to hold this list of destination and to add additional destinations if they need to. Same with there commodity; I created a Commodity Table. However, I had to hard code the calculation so the summaries would work in their reports. How can I correct this so I don't have to manually update this calculation each time they add a Destination, or Commodity ( Category ). And then will the Summaries be able to distinguish the differences ?? If ( Destination = "Belgium" and Category = "Copper" ; Price C…

Hi , I am trying to "find" where a field contains less the the correct number of characters but it appears not to work. I have a barcode field that should always have 12 digits, but some have less than this and i need to find and isolate them. I thought using the Find mode <12 would work but it apears not, can anyone please advise? FMpro V12.04 MacOSX 10.8.3 Many thanks

Hi, I have just started using filemaker and was wondering how I can get this formula, in access, run in filemaker SRQuota: IIf([summary Report Date]>DateSerial(Year(Date()),Month(Date()),1),1,0) So if a date falls within within the current month under Summary Report Date then SRQuota would equal 1. Today's Date 7/4/2013 Summary Report Date 7/1/2013 then SRQuota = 1 Thank you!

I am trying to use ExecuteSQL() for the first time, and have run into the dreaded "?" for a very simple query. I want to count all the records in the Games table where the field Won_Lost contains "WON". My calc is ExecuteSQL ( "SELECT count(*) FROM Games WHERE Won_Lost = "WON" "; "" ; "" ) What have I done wrong?

Hello, does anyone know how to create a summary field (sum in my case) of a field in an external table (budget cost in my case). i tried to create the summary field in the external table, but it won't work in the original table does anyone has any idea?? thank a lot, Filip

I have a file which has labels in repeating field called r_labels. This was put in by a programmer years ago and it makes a menu header. I want a simple list of those values but I do not know how to turn a repeating field into a calculation which lists them with a carriage return between each repeat. I can not change the existing setup but I can add a calculation and that is what I want, a text calculation as a multiline. This is only for a display because I want to modify it a bit further. Is it possible? Ideas would be great, I searched and found similar but I am not smart enough at this to figure them out.

Is there a Get () function to obtain the file path of an object that it is referenced in a container field?

Hi,  I am creating an estimator for a customer and I built the calculation by nesting "IF's" via a script trigger but as I increased the operators; "+, -, x, / " my nested "IF's" within my script grew exponentially.  It works great, but I am hoping for an easier solution ...  ... I was hoping to use a "Let" statement but "Let" statements confuse me for some strange reason.  Here is what I would like to do if a "LET" statement can manipulate the operator characters the user selects ...  ( 1 + 1 ) or ( 1 + 1 ) + 1 or ( 1 + 1 ) + ( 1 + 1 ) or ( 1 + 1 ) + ( 1 + 1 ) + 1 or the user could do this ( 10 / 1 ) + ( 100 x 50 ) - 67  Currently the user has a…

Hi, I have a database for budget control ID Name_budget_line Amount Every budget_line has 3 titles Ex: Budget 1 - Subtotal Expenses - Subtotal Salaries - Subtotal Salarie 1 - Amount Salarie 2 - Amount Salarie 3 - Amount I create a subtotal (=Sum of Amount) and this works fine in every sub-summary part. I made a extern file the same fields ID Name_budget_line Amount TOTAL=Sum of Amount Linked the 2 tables but the TOTAL summery field doesn't work the same anymore. It doesn't give me the totals per group but only for the last line. Does anyone have any idea how to solve this. Thanks a lot, Filip

We are using FileMaker for all aspects of our business but one area has me stumped. When we purchase our product it can be any size from 1m2 to 5m2 in increments of 10cm. I have the calculations set up to calculate longest length and longest width which works fine but in terms of pricing as soon as it goes over a size (eg: 900 to 910) we get charged the 1000 rate. How do I get this to work in FileMaker as rounding rounds down below 0.5 and floor/ceiling just doesn't work in a repeating field or accurately. Any suggestions?

New to Filemaker so please apologise if this is a simple question. I have a text box for hourly rate so before after I enter the wage say 6.19 I need it to return £6.19 The £ needs to be inserted automatically. Thanks for your help

Hi All, I have two DB fields that I trigger Yes or No in the field. I can do a FIND with Couples = Yes and then use Get(FoundCount) and it gives me the total of Couples Checked In I can do a FIND with TotalPeopleCheckedIn= Yes and then use Get(FoundCount) and it gives me the total of People Checked In I want to display both of these totals on the same page and I am not quite sure where to start. I tried this: If ( couple="Yes"; Get(FoundCount)) as a calulation in a field called couples, that did not work. Any help would be greatly appreciated. Thanks..MG

Purpose: To capture the contents of a field before it is changed. Compare the before to the after. Highlight the after and screen capture then export. I can't get past the first sentence and it's getting frustrating because it seems extremely simple. 1 Field: OnDeck_t (text) - The OnDeck_t field data = On 2 Field: OnDeck_before (Where I want to capture the OnDeck_t before it's changed) I have an OnObjectEnter script trigger on the OnDeck_t field that runs this script: Set Field By Name [substitute (Get (ActiveFieldName); "_t"; "_Before"); Get (ActiveFieldContents)] If I do this it works fine. (But I don't want it hard coded. I will be using it f…

I cant send a file, so I will try to explain my problem. I have a field named ''city''. it has so many datas, for example ''newyork, paris,london,istanbul etc,'' and also there are so many istanbul or newyork records. I can summary theese records. and I can count how many records.and also This is a related database, my problem is that I cant count How man city is, when I count city field with summary , it gives everytime found records. I just want how many city, not how many records.

Due to stupidity on my behalf, It was determined that I had used relational data for name/address/city/state/zip fields when creating an invoicing database; the question, is there an easy way to correct without having to resort to importing data into a properly setup invoicing solution. Open to any suggestions... Jack

I am new to Filemaker and have a question on a formula/script that I cannot seem to do myself. In the UK we have Bank Sort Codes as: 20 - 45 - 99 I need to input 204599 then it automatically inserts the dashes for me so the result is 20 - 45 - 99 Hope someone can help.

I have plenty of texts I want to enter in a database. Each text have basically a first line which is the title of the document. I tried to have the entire first line automatically pasted into the title field using a script with find, copy, paste... But I have no idea on how to tell FMP to copy everything in the first line before the return carriage which is the only common character to specify the end of the line... How to to tell FMP to use all 'special' characters that are in a text field ?? (letters, and space being the easiest), but carriage return, tab, etc... There too, I am close to excess of aspirin..! Thanks for any tip or lead

Hi guys. I am working on a database about change orders and I have the following fields in each record: Change Order Name, Description, Contract No., etc. (this is not relevant to my question) Contract Original Sum (coming from a related Contracts database. No problem) Prior Change Orders Prior Contract Amount This Change Order New Contact Amount Since this is a database to store data about change orders, I have info about change orders for many different contracts. I have a problem with the field "Prior Change Orders" because I don't know how to summarize all the prior change orders that belong to a specific contractor, so that when I imput a new change orde…

I've found an undocumented RecordOpenState (see attachement). The state is 3 and as far as I can tell it means "Record that you just deleted but is still showing on the layout because an OnRecordCommit trigger is stopping it from committing". Â I post it for information and to see if anyone else has ever seen it. There seem to be some specific conditions required to make it happen as I can only make it happen on records created via a certain relationship at the moment. I'll report back if I can find out enough info to report it as a bug.

For a client database, I created a simple feature that displays the current database size using Get (FileSize). This seemed to work fine locally, but when I hosted the database on their server I'm noticing several discrepancies. 1. The actual File Size (and Date Modified, etc.) do not update in the Windows Explorer window on the server when the file is being modified by clients. Can someone tell me what prompts the update here? It's not when the database is closed/reopened, because the list of hosted files show recent updates and the files have not been closed recently. 2. For some clients and in some instances, the Get (FileSize) calculation will return: a …

How can I convert data from a number field, .5 for example, to a readable "50%" in another calculation text field? Thanks!

We have the following convention in my company of naming our project starting with the last two digits of a year (i.e. 2013, becomes 13) followed by a three digit serial project number as projects come in, so project no. 10 this year, would be project_id = 13010. Initially I was thinking of having the user input the project no and using a hidden filamaker serial as primary key. but I 've decided against this and I am thinking of keeping the current convention as the primary key in the projects table too. But how do I have filemaker input this YYXXX format automatically?

I have written the following calculated AppleScript: "tell application " & Quote ( "Microsoft Word" ) & ¶ & "activate" & ¶ & "open" & Quote ( "Macintosh HD:Users:Shared:Guild_Word_Template.dotx") & ¶ & "set theDate to current date" & ¶ & "set text of active document to theDate as text" & ¶ & "save as active document file name " & Quote ( UIN_FOCUS::GT_WORD_FILE_NAME ) & ¶ & "end tell" & ¶ & "tell application " & Quote ( "System Events" ) & ¶ & "tell process " & Quote ( "Microsoft Word" ) & ¶ & "keystroke " & Quote ( "v" ) & " using {command down}" & ¶ & …

I'm still learning as far as calculations go, particularly calculations within calculations. The other day I wanted to get a list of people from a contacts file that don't have an email address. I could get a list of everybody but I couldn't filter out the ones without email addresses. Would you wrap a FilterValues calculation around the List function or vice versa or do something completely different? I managed to do it by establishing another relationship that did the filtering for me and then used the List function. Nothing wrong with this solution I feel other than having another table occurrence on the relationship graph just to accomplish this one thing. In …

I need to find the sum of the 4 lowest scores from a range of scores. I have created a field called "o1scores" that uses the "List" function to store list of all of the individual scores from the various individual score fields (p1score, p2score, etc). How can I use this new "o1scores" field to find the for lowest scores and then sum them up? For example: I have a field called "o1scores" where o1scores = list (p1score ; p2score ; p3score ; p4score ; p5score ; p6score). p1score, p2score, etc., are numeric fields that store an individual player's score How can I sort "o1scores" from lowest to highest, pick out the 4 lowest scores, and find the sum? Thanks!

Hello, thank you for your inters in my topic! Â I'm looking for some help on hiding record titles. In list I have a sub-summary with client name in it. I also have the client name in the body. When list is sorted and the sub-summary is in use I would like to hide the client name in the body. Any Ideas on how to do this or if it can be done??? Â I've attached screenshot to help illustrate the issue. Â I'm guessing I could use some conditional formatting, make the text color the same as the background, etc... Â Any help would be appreciated, THANK YOU!

I would like to be able to calculate age and report according to the guideline shown (knowing i input the hour of birth besides the day/month/year: I would like to be allowed the recording of date and time of birth (hour and minutes if known) and subsequently be able to express age in hours for the first 4 days of life, then in days from the 5th day of life through the first 28 days, then in weeks beginning on the 29th day of life through the first three completed months of life, and then in months beginning with the 4th month of life through age 2 years. Subsequent expressions of age may be expressed by year and month (2 years 6 months) or with a decimal place for e…

I have a number field that I would like to auto enter either (2, 3, or 9) at random every time a new record is created. Please help! Thanks Matt I tried to do a sequenced auto enter but filemaker simply counts up from 1 and I can't specify the 3 numbers. Thanks

Hi, I have need to combine a date field and time field into a single timestamp field so I can calculate "time-since" figures. How do I go about this please - I know it's not as simple as table::date & " " & " " & table:time and calculating on that because a timestamp is a number (in the background). Can I simply subtract 01/01/0001 00:00:01 AM from another time/date field? Any ideas please? Greg

Looking for some advice on my due date calculation. My calculation is working, but wondering if it is as sound as it could be. due_date = [ invoice_date + terms ] Should this be written better?

I have to teach pupils about field types - specifically text, numbers, data, time, graphics, calculated, link and boolean. This is the terminology I must work with. Boolean I'll just make a text field and apply a little validation. The one issue then is the link field type. How would one do this? Thanks John

I would like to create a field that will display the name of the file that have been placed in a container field. I'm using FMP 9. Thanks for your help. -Gary Additional info: I used GetAsText and it give me this output: size:612,792 image:Photos and Documents/Company Style Indicia.pdf imagemac:/Macintosh HD/Users/GarySmith/Desktop/Envirolution/Photos and Documents/Company Style Indicia.pdf I'm looking for a calculation that will just give me the file name... not the entire path. I'm inserting both images and files into the same container field. -Gary

Using FMP 10. I manage a database of inventory SKU's using FMP I need to put a Max Valu Limit on a Calculated Field. Basically, I'm trying to set up a rule on the Calculated Field that would go something like: If value >76, value should = 76 Any help is much appreciated. Johnny

I have just changed our system to allow individuals to checkout items for 2-days or a week (used to be all just one day, which made everything easy) - and I have a calculation field to determine when the item is due. So, an Individual can checkout equipment on May 5 and it will be due back on the 11th. However, I have a reservation option as part of this solution - and I cannot figure out a way to: Today, I have an individual reserve an item for June 1st (on an item that is allowed for a week) which, when checked out would be due on June 7th. I have another individual reserve the same item for June 4th - I need to figure out a way to 'block' the dates in b…

I am working in Filemaker Pro Advanced 8 on a Mac OSX 10.6.8. The database is fairly straight forward: it is tracking the total number of comments (all types) and the sub total of online comments at a federal website on a new TSA regulation. 4 Data Fields: Month, Day, Total Comments and Online Comments (a subtotal) Records are sorted by concatenating the numbers in a calculation field with the month and day fields. This data is exported to a tab separated file and imported into Adobe Illustrator to make pretty charts. All is well, so far... Recently, we have found that there is something suspicious about the numbers supplied on the federal site, ma…

Hi, I have a "person" table with firstname and lastname and ct_fullname (unstored, calc). I want to use the full name in a drop down list to set the person id FK field on another form but I get the error. Is there a hack to get around this or do I have to set as a stored field, have script to "recalc" it when people update the person record and run scripts when logging into the system to go through my thousands of users and make sure the stored field value is correct? This seems far too complicated.... Thanks Nick

Hi I have a portal showing upper and lower limits of measurements. The portal is sorted by FK_MeasurementsID Then I have a list showing the upper limits List(Measurements::UpperLimits) also for lower limits My problem is that the List() function does not sort the upper and lower limits the same way as the portal. Then when I use those values they don't match. Does the List() function use the creation order of the measurements records? Is it possible to sort the List the same way as the portal? Regards Reynir.

Hi all, Can anyone point me in the right direction for this one... When a user with a customised privilege set logs in they are prompted to change their password for the first time of logging in [standard filemaker option]. What I'd like the user to be able to do on my layout is have a settings section.... A user could change their username and their password. Is this something FM can support? I've searched about for others achieving a similar thing but have not found anything yet. Any help, as always - is greatly appreciated.

I am at a loss here. I need to create a packing list that states how many packages have a certain quantity in them. For example: there are 10 packages. Nine packages contain 1000 items, one package contains 750 items. My packing list needs to state this information: Total Quantity = 9750 Total packages shipped = 10 9 packages of 1000 1 package of 750 I can figure out how to list this using a self joining relationship and a portal but I need the individual values (9 at 1000 and 1 at 750). help??

I have a calculation to find a middle name that does not return any text. The calculation is for parsing the middle name in a string that may contain a title, such as Mr, Dr, etc is: Case(IsEmpty(Title) = 1 and MiddleWords(FullName; 2; 1) - NameLast; MiddleWords(FullName; 2; 1); IsEmpty(Title) = 0 and MiddleWords(FullName; 3; 1) - NameLast; MiddleWords(FullName; 3; 1); "") This is accepted as valid but returns only a blank. Any help would be appreciated

hi please help i want to change the portal text color base upon what i choose for example if i choose Open the portal text should be Green if i choose Closed the Portal text Should Be Red

Is there a way to Auto Capitalize and auto Caps all letter in a single field? example if i type canada it should be Canada and if i type ak it should be AK

Probably a simple question, but i wanted to use a repeating calc field to display a series of navigational buttons. If there are four repetitions, I wanted to have each field act as a button containing text indicating which layout the buttons would send you. When defining the calc field, I used a Case and GetActiveRepetition function with the corresponding text like so: Case( Get ( ActiveRepetitionNumber ) = 1; "Create Jobs"; Get ( ActiveRepetitionNumber ) = 2; "Search Jobs"; Get ( ActiveRepetitionNumber ) = 3; "Search Companies"; Get ( ActiveRepetitionNumber ) = 4; "Search Invoices") My issue is that the text displays but only when I place the cursor in the…

Hi All, I have not been on the forum for several years - hopefully I'm doing this correctly. I want to identify the Unique Invoice numbers assigned to a sales rep and then count how many Unique Invoice numbers there are. I need the count so a can generate the average value of each sale. Example (first 30 records): Rec - Sales Person Rec Num (Invoice) Summary Adam Cravens 2040122 Adam Cravens Adam Cravens 2040122 2040122 Adam Cravens 2040122 2040129 Adam Cravens 2040122 Total of two unique invoices Adam Cravens 2040122 …

I have a Kit table and a child table called Kit and Product Tie. Kit table contains the name and ID of kits, where the Kit and Product Tie contains the products that are in the kits. They are joined by a Kit ID. I have a modification time and date stamp field in the Kit and Product Tie table which shows the user when anything in that specific kit was modified. The field only sometimes updates. Most of the time it doesn't change the date or time from the previous entry. I can't figure out what I'm doing wrong. I've turned indexing on and off, tried adding repetitions(?), tried changing which table the time stamp is created in, set a script to refresh the page and flush…

On April 1 2013 the tax regime for businesses in British Columbia changed. Previously a single tax (HST) was applied to sales. April 1 the tax split into two, GST and PST. Further complicating this, Canada is a federation of Provinces and Territories and each has its own tax system. For example, the Province of Ontario uses HST whereas Alberta has only PST. So, if you sell to Alberta you charge PST only. HST is a harmonized federal tax whereas PST is Provincial Sales Tax. I am trying to write several scripts to calculate the payable tax. To start with British Columbia. I have a field that shows the federal tax type payable , either "HST" or "GST". The Tax Fi…

I am trying to use a calc field with a container result to display whether a phone number in a portal is selected as the primary number for the record. I have a person table and a telephones table. The person table has a field for "PrimaryPhoneID" that is populated by a script when the indicator icon next to the phone is clicked. This part works. I have a "isPrimary" field in the telephones table that uses ExecuteSQL to lookup the value in "PrimaryPhoneID". This is where my problem is. The result of that calc field does not update when I select a new primary number until after I click into another field on the portal. In the example, you might ask why use …

I have a supposedly simple thing I would like to do but I am afraid it is rather difficult Here Goes I have a button on which I would attach a script to allow the user of my file to open a page on the internet. BUT after they have opened it the following needs to happen 1. They can close it and open it again throughout the same calendar day. OR 1. They can try to open it again and a dialog comes up that says you can only do this once per day. I have a list of urls to go to but after they have gone to them they are erased from the database. The trick is using the same button to know if it has already done the script that day. This is a hard one.

I need to know how to set up a field to calculate the next model number. Currently the method is to find the max and add 1[Max(veh_SELF_type::Model ID) + 1]. This has worked in the past. Letters have recently been introduced for a certain type of item so the current method will need to be tweaked. I currently have 4 records for this new type of item (Model Nums - A001, A002, A003, A004). When I add a new item, the current method gives me model num "5" instead of "A005". Also, once the numbers get to A999, the next one will need to be B001. I also have to skip I, O and Q letters. I appreciate any help.

I have need to remove something around the first 11 lines of text from a field I copied text to. But sometimes there are variations and it is 12 lines or 8 lines etc. It is easy to spot and I have a field called "ParagraphR" where I put the number of lines I need to remove. So how can I remove the first 11 (or the number in the field ParagraphR) of the text in the field "Mastertext" ? I would like to set the new field called "FinalText" with that remainder of text. Thanks very much.

I have forty questions each field in the table represent one multi choice question (a, b or c). once the user enters their response it would changes to RED if incorrect and BLACK if correct. The code below will do that (1)event.rc= true; if (event.value != "a") { event.target.textColor = color.red; } else {event.target.textColor = color.black;} I need assistance for calculating the totals of correct and incorrect responses. Bernard This forum is ONLY for site operations - NOT for asking questions about FileMaker Please clear this text and leave comments & feedback here. For all other support requests please choose a forum that best applies to your p…