Calculation Engine (Define Fields)

Hi, I've looked around on the forums and there seems to be lots of info about on this but nothing that works for me so far. I'm trying to create a simple script to make a vat calculation based on whether or not the invoice date falls into a certain range. I have an 'Invoice date' field and then a field called 'VAT' which is referencing 'Sub Total'. The VAT calculation was this... [color:blue]Sub total * .175 ...which was simple enough. And I've since had this working (if the date is later than 31 Nov 2008 then calculate VAT at 15% otherwise, calculate at 17.5%) [color:blue]If ( Invoice date > Date ( 11 ; 31 ; 2008 ) ; Sub total * .175 ; S…

Hi All Looking for some help with what should be a simple calculation to validate a set of fields via a script... If [(Clips::Used = "ELearning") and (IsEmpty ( Clips::Course ) or IsEmpty ( Clips::Module ))] Show custom dialog ["You must enter a course and module number when selecting E-Learning"] Go to Field [Clips::Course] Exit Script [] Basically, there's 3 fields, and what I'm trying to do is send the user back to the layout if: the 1st field has an entry of "elearning' (selected via a value list) - and - either of the second two fields are left blank. So... IF field1 is "elearning' AND field2 OR field3 is blank, then fail. I've t…

Hi I have a field with an Auto-enter serial number that a user claims is reset to 1 for no apparent reason. The only script I have that touches this is a "Remove record" where you are prompted to only remove the last record you created. Apart from removing the record it sets the next serial number to the one you just deleted. The user claims that she has not used this button/script so should not have anything to do with this particular script. Any ideas how this could happen? Client: FMP7 on Windows Best regards Thomas

Hi all, I have 2 tables.. StockCodes CreateFile In CreateFile, I have a field called List. At the moment, this is a calculated list that lists all records (stock codes) from the table StockCodes. So the list shows StockCode1 StockCode2 StockCode3 ... Does anyone know a way whereby this list will only show the unique records from the StockCodes table. In other words the stockcodes table may show a stock item code in more than 1 record. I would like the list to only show the stock item codes that are listed in 1 record only in that table. Can any of you experts out there advise on the best way to do this please? Many thanks …

I have a global filed called year. Every year I create a new file and am able to change the display of the year by entering on any screen and it will show that year on all screens displaying that global field. This file I just moved to my FM9 server (was fm6 not on server before) and workstations access this file via the server. When I change the year it changes on all screens. After closing and reopening my change is gone! HELP!

If I want to auto-enter a value into a field based on a relationship, is it better to use a look-up or calculated value? What conditions would drive the use of one over the other? Assuming that I'm just copying a single value.

Hi Guys I have a signage company and have recently commenced using a quoting program and would like to have a new field inserted for each project where by I drag and drop a shortcut from the relating folder which holds all the CorelDraw and other files pertaining to that project. At this point of time in the resource (materials) area we have a field similar but it only allows image files. Any assistance would be much appreciated. Cheers Theo

Hi, I have table with packing list data that looks up the transport cost from the transport table. The relationship from the packing list to the transport costs are: Transporter Transport Centre Packaging Type The first matching record in the Transport costs table shows 1.93 but the transport cost brought in to the packing list shows 1.86 There is a 1.86 shown in the transport costs but that is the third matching record not the first. How am I managing to knack this The screenshots show 1). the packing list with the looked up transport cost(Tr_cost) 2) the transport cost list Thanks in advance Chris

Hey, I'm trying to format a date calculation and I'm a bit stuck. I'm trying to track the number of days, hours, minutes it took for a project to be completed. I used ProjectFinishedTimeStamp - ProjectStartedTimeStamp. The calculation Result is TIME. How do convert the result of 6557:17:39 into 'D' days 'H' Hours : 'M' Minutes? Much appreciated, P

Hi. I am trying to use an If command to change VAT (Tax) from 17.5% to 15%. I have at the moment: If(Order Date > 01/12/2008,Line Totals*15/100,Line Totals*17.5/100) I want to say: If the date is after 01/12/2008 then use 15% as the VAT addition; if not use 17.5%. Could somebody help me please. Many thanks. Roger

Hi all - having a trouble with the following: I want to conditionally format a field based on: [if] length (filter ( mytextfield ; " !£") > 0 then format background yellow etc But I also want to filter out inverted commas (" and ') from mytextfield I am going round in circles looking at escaping with "" and the quote function...none of which seems to have the desired result Any help grateuflly recd thanks Simon for reference mytextfield is an e-mail address and I want to highlight the field if its got disallowed characters, what I have at the moment is Length ( Filter ( Self ; " *,%<>!?[]{}£^&()/|~#") ) &g…

hello there, I'll appreciate any help on this matter. I have one repeating field with 4 values called "english". english[1], english[2], english[3], english[4]. I have anoter calculated repeating field with 3 values called "diffForEnglish" in which I want to have the following calculated results: diffForEnglish[1]=english[2]-english[1]; diffForEnglish[2]=english[3]-english[2]; diffForEnglish[3]=english[4]-english[3] Could anyone tell me how to do this? Many thanks for your time, Yours, Claudiu

I am creating an email database and I want to link the names to a separate website where the everyone's info is listed. So I am recording the URL for each record in a field. I want to set another field that takes that URL, makes it a hyperlink, and then replaces the text with the records "Full Name". How do I do this?

Hi guys, I have almost finished my little database (somehow) but i am stuck on one last problem which i just can't seem to figure out. It's probably way to much explaining so i thought i might just upload the database here (it is only small) and see if anyone would take a look at it. Basically what i would like to do is, in the 'profits' table i would like all the fields to auto-calculate the sum of money based on the 'bookings' table. The reason i am uploading the database is because i would have to explain all the relations and i am sure, although it works i have done it wrong haha any help would be greatly appreciated Thanks Booking.zip

I understand how to set a field that will populate the Account Name that created a New Record. But, what if that field did not exist before? Is there a way to retro-actively acquire the account used to create a record?

I'm stumped. When I test each piece of the puzzle I get the correct result however when set up as a looked up value based on a calculation I only get the default answer. I need to look up the shipping account number based on the preferred shipping method. UPS or FedEx I'm a auto entered calculation from a related table named: Companies Shipping preference is named: Ship_via_pref I'm using the Text function (left,3) to parse the UPS Ground, FedEx P1 etc... What am I missing? Have I explained myself properly? Thanks in advance, Jim Account_number= Case ( Left ( Companies::Ship_via_pref ; 3 = "UPS" ); Companies Customer::Ship_UPS_number;…

This is something I just don't get. I have a Child table with a repeating field, "Profile", with five repetitions. In the parent table I define a new field Total with five repetitions as Sum(Child::Profile). This then adds up all five repetitions for all the related records and puts the same number in all five repetitions of Total. But what I actually want is for each repetition of Total to be different, ie the first repetition of Total adds up the first repetitions of all the related fields. This is so basic! What am I missing?

I am trying to re-format a timestamp in order to put it into a larger calculation (to compile a file path using variables) and FP is prompting me that there are too many parameters. Have I missed something or am I not able to use the Case function within the Let function? Here is my calculation; Let ( [ $year = GetAsText ( Year ( Get ( CurrentTimeStamp ) ) ); $month = Case( Length (Month ( Get ( CurrentTimeStamp )) = 1; "0" & GetAsText (Month ( Get ( CurrentTimeStamp ))); GetAsText (Month ( Get ( CurrentTimeStamp ) )) ); $day = Case( Length (Day ( Get ( CurrentTimeStamp ) )) = 1; "0" & GetAsText (Day ( Get ( CurrentTimeStamp ) )); Get…

Hi I have an accounts database in which I want to add the sum total of figures in the amount (text field) to appear in sub total (number field) which VAT is added to appear in VAT (number field) and finally end up with a grand total in the final (number field). It all works fine in the amount field if there is just one amount. As soon as I type in 2 or 3 amounts the sum is either just the first amount or an obscure figure. If I change amount field to a number field I can only type one number which is no good for invoicing. Any ideas?

Hi, I was wondering if you could help me solve my filemaker problem regarding the SUM function? I created a timesheet which records and computes for the total number of chargeable hours per day (this is working well). The problem comes when I want to calculate the average chargeable hours for each week. I expect that if I could specify (perform a find) which records I wanted to total then I could use the Sum function to give me the total chargeable hours for the week. The sum function produces a random result. To test the Sum function, we narrowed down the search to just one day for which we knew the number of chargeable hour (eg 4) and the S…

I am managing a database of students for an academy and I would like to be able to have the background color of a field based on whether there is any content in a separate field. More specifically...I have field where the financial aid office enters the amount if any of an awarded grant. I want the overall background of my page OR the background of the field box next to the field where the grant amount is entered to change to red if there is text in the grant amount field. I am using filemaker advanced 8.5 and so i cannot find an easy way to conditionally format the field.

I'm trying to get the following case statement to work, but have been unsuccessful. I have a field called total score and want another field to display Preferred, Needs Improvement, or Problem based on the score. I've created a calculation field with the following case statement, but a score of 69 returns "preferred". What am I doing wrong? Is the greater than/equal the wrong string? Case ( 90≤total_score ≤ 100 ; "Preferred" ;70≤total_score ≤89 ; "Needs Improvement" ;"Problem Supplier")

My application is used internationally where standard xx/xx/yyyy notation can mean different things (sometimes mm/dd, sometimes dd/mm depending on where you are. I have formatted my output to an 'international'format, e.g., 24 Nov 08 so I don't care where folks view the data/reports. My problem is that the only date format accepted when entering a date field is mm/dd or mm/dd/yyyy. There is enough to train users on so they don't have to be conscious of entering data in a format that is not native to them. Worse, they may not realize an error is made (e.g., both 11/12/08 and 12/11/08 are valid but very different depending on where you are. I was hoping FM wo…

I am trying to come up with a calculation to incorporate into a script. Basically the calculation has to compare the value of a field in the current record against the value of the same field in the previous record. It seems like there should be a fairly straight forward way to do this, but I'm missing it. It would be a boolean result. The value is either equal or notequal to the previous value. Would appreciate some help. Thanks.

Hi all. New to Filemaker, but getting somwhere day to day has been interesting. If ( Account = "New" ; "XXXXXXXXXXX" ; "") I have a field calculation that if the corresponding field contains certain text it will result in specific text in the calculated field. So far that works just fine, as long as the reference field contains that exact text ( at least at the beginning of the cell ) What are my options to search the within the cell for a match? I can't seem to use * as a variable in the calculation. Maybe this is a limitation, or better yet, I'm uneducated. Thanks in advanced. Regards, Mike C.

Not sure whether this is the right forum. Apologies if not. I have a table which lists business mileage. What I am aiming to do is to summarise the business mileage for a given tax year. (The tax year in the UK starts at the beginning of April) I think that the best way to do this is to create a field that sets the current tax start date and then to create a related table that just shows the records starting after that date. If anyone has an easier way of doing this please shout! So to achieve this I want a field that calculates the current tax year start date. Something like if(CURRENTMONTH<4,(1/4/CURRENTYEAR-1),(1/4/CURRENTYEAR). What…

Hi all, I want users to be able to send HTML e-mails from FM - and have a suitable plug in - no problem. All I need to do is build the HTML body text from a text field(s) currently called "textfield" I can use GetAsCSS(textfield)to do most of this - no problem. However, I want users to be able to do indented lists (bulleted and/or numbered) but there is no text formatting in FM to allow this Has anybody else tackled this - I have thought about multiple text fields etc and adding in the tags but am wondering if there is a more elegant way? Cheers Simon PS I suppose this also applies to tables - but am pretty uncomfortable with the co…

Hi, I have a stock field "prod_stock" that displays the products stock "5". I would like to have a checkbox rating from 0-20, so when you have 5 Stock Items, 5 checkboxes are checked. _ _ _ _ _ |_|_|_|_|_|X How could I do this? Thanks

After a bit of searching around I have found this calc to work out the number of working days in a a given date range (based on not working Saturday and Sunday) it relies on a field called StartDate and one called EndDate Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate), Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)), If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) - DayofWeek(StartDate), 0)) + 1 I would like to modify it so it it only counts Sundays as non working days. so I thought this would suffice. but some months are not calculated right. This is what I have so far. Int((EndDat…

Hi all, in a nut shell.. i need to be able to do the following example in an invoice which will not calculate the VAT/Tax in a field: example below of £31.00 should be exempt in part of the Invoice. MOT Charges £16.71 VAT/Tax £02.92 (Field calculates tax of £16.71 @ 0.175 Subtotal £19.63 MOT £31.00 ( Omit tax calculation here Total £50.63 ------------------------------------------------- So i need to omit the MOT field of £31.00 from being calculated with VAT or Tax? Does anyone know how to get around this or know a scri…

Is there a way to create a multi value field from another field specifying a range. eg: Range field = 11/24/2008...11/28/2008 I need a new field to return 11/24/2008 11/25/2008 11/26/2008 11/27/2008 11/28/2008

Hi, I've let my filemaker go bit rusty and I'm driving myself mad trying to remember how to make a calculation that gives a result based on a range of numbers. ie. 1-10 =10 11-20 = 20 21-30=30 etc I've tried case (field >1 <10;10 etc etc i've a feeling that I'm missing something between the >1 and <10??? Thanks in advance, Skip

I have a text field that is defined as an auto enter serial number. I know that the sorting in this field will be 1,10,2,20. I am wondering if there is a way I can sort it like a Number field so that after sort the result is 1,2,10,20. I cannot change the field type to number hence this problem. Any help would be greatly apprciated..

Hi Newbie Here. Right first things first im a novice at filemaker so you will have to bare with me. Right i have 3 fields Actual strokes used,Amount of chemical,Strokes remaining. Now what i need to do is take the amount of chemical minus it off of the actual strokes used and then add the previous records strokes remaining, but i have no idea how to put this in filemaker terms Help

I have searched the manual and here for help with this and strangely, I found nothing. I tried several approaches to format the field within the script and the results. I have a serial number produced with a Loop and want the result if < 10 to be preceded by a zero for sorting purposes. (I believe FM, like spreadsheets, requires a zero before single digits.) The script, i.e., the bcooney/Daniele script, follows below. Thanks, Leonard

I have a "Stock Checkbox" that I can check manually if the item is in stock, then I have a calculation that calculates a products weight and price. So when the price is below $0 the checkbox should be automatically un check (so we know to reorder the product). But it still should be possible to uncheck or check the same checkbox manually. How is this possible? Thanks T

Alright, lets see if I can explain this one. We create FileMaker databases about every 3 or 4 years or rework ones that previous co-workers have worked on. Anyway I have fields that I are calculated values with case statements. Case(Field1="1",1,Field1="2",2) Instead of just assigning a static value I such as 1 I want to pull the value from Record 1 of Field1 in another Filemaker database. Can this be done and I assume I have to set up a relationships for that database / fields? Not working on Filemaker everyday gets to you go to work on it. Thanks for any help? Nathan

I am in the process of writing a database to keep track of my self-employment work. I currently have a portal inside my company record layout and wish to calculate the total money earnt from that company based on the records from my job table. I assign each job a related fk_company_id but can't work out how to get the calculation function to only calculate that company not the entire database. Any suggestions really gratefully received...

Hi, I am a bit new at FM and have a question. I have a field in a related database containing the numbers 1-21. Say they are items they assigned to a customer. The customer can have any combination of 1-21. I have then created fields for each of 1-21 in the original database. How do I have the 1-21 fields insert yes if that number is contained within the original list. I have tried using a calculation but can figure it out.

I'm trying to create a URL from a contacts company email address. I'm using this calculation: Case ( IsEmpty ( URL ) ;"http://www." & Right(eMail_Address;Length(eMail_Address) - Position(eMail_Address;"|@|";Length(eMail_Address);-1)); "" ) Some of the contact records already have a URL entered so I don't want to change that entry if it exists. My problem is I do not get any data from this calculation. I have set this up as an Auto Enter calculation in the URL field and unchecked Do not replace existing value assuming the IsEmpty ( URL ) statement will skip the record if there is data there. What am I doing wrong?

In a mixed environment of Macs and PCs the PCs have trouble displaying images in Container fields. The images are right click copies from images on a web site by the Mac. They are then pasted in to the Container field by the Mac and display just fine. But instead of the image on a PC we get this message "Quicktime and a decompressor are needed to see this picture." I've installed the latest Windows version of Quicktime but this did not help. Has anyone else run across this and been able to find a solution? If so what do you do to fix it? Thanks in advance.

I have been trying to fix a larg complex database, that I have been building for 5 years (74 tables, up to a 120 fields each). and somehow, seems to be unstable after I moved to Vista and FM9. Here is what happens. After I do a File Recovery, it appears that FM 9, which creates a "Recovered Library" Table, and a "Recovered Blob" Container Field, comes up with about 42 records each time. and after about 100 trial of Recovery, after manipulating/deleting tables and fields, I discovered that these recovered Records, are some how container fields, that I have deleted in the past, and FM is now recreating them. For instance, if I have a Repeating Container Field (…

In a given text field, is there a way to calculate the expression "if the field contains" (and similarly, "does not contain")? IOW, if I had five name records: John Smith Mary Jones John White Peter Johnson Jill Jackson I want a calculated text field to generate a result based on whether (or not) the name record contains the word "john" (which would include John Smith, John White, and Peter Johnson). Is there a way to do it?

I'm having a little trouble with a database. The database is used to keep track of items that need to be sent out every so often and calibrated. I have a status field that can be any of the following, OK - Means the item is ok to use Send for Calibration - means the item is ok to use but is getting close to expireing and needs to be sent out for calibration. Expired - Means the item cannot be used So I have a field called Expired flag that is set to 1 if the host date is greater then the expiration date else 0, and due for cal flag that is set to one if the host date is greater than the due for cal date else 0 I also have a field that is set to …

I've got a calculation field (result container) to present a preview of an image file. Field name PREVIEW. Script = "image:Previews/" & JobNumberMonthYear & ".pdf" & ¶ & "image:Previews/" & JobNumberMonthYear & ".jpg" This all works fine. I wanted to create another calculation field (called EMPTY) to let me know if the PREVIEW field is empty (in other words there was no file for the PREVIEW calculation to link to) so that i could do a find based on this result and then create these image files. At the moment, when there is no image showing in PREVIEW it shows the text "The file cannot be found:1115-08". I have tried…

I use filemaker to list tasks. Can I set up a reminder based on the date. Thanks

This is the formula I'm using and it doesn't appear to be working. The value "Q2_EndDate" is a date field and I think this might be the reason. How do I make a date field into a value like the one "Get(CurrentDate)" returns? The logic here is: If today's date is before the last day of the quarter, set the calculation text to gray. Otherwise, just do the calculation. Case( Get(CurrentDate) < Q2_EndDate; TextColor (calculation ; RGB(204;204;204)); calculation )

I discovered an interesting new "feature" of v9 (at least new to me). I have a series of fields that are used to track a process - step 1, step 2, etc. Each step has a whole collection of fields - start date, due date, revised, etc. etc., many of which are calculations based on one another. I need to add more steps to this process. In the past I've used "Duplicate" in the Manage Database interface to do a "copy" of each field - then go back, rename and update the calculations. This was OK, but each calculation had to be manually updated to be the new step number within the calc. What I found today was if I select the fields and do the Copy and Paste …

Were there changes in functions between 8.0 and 8.5? I have a repeater showing a calculated text result that works correctly in 8.5 but not 8.0 where it just returns "?". The 8.0 machines are running v3. I'm waiting to hear on the 8.5 ones. Thanks

Hey all, I am sitting here thinking about a database, my first one obviously. Well actually I am making some changes to an existing one I guess as we already have a massive database, however the designer didn't really create this database the way we wanted it so I am here to save the day and make some changes, or create new ones if need be. Anyways I am wondering how I mark a field as a primary key for a table? Thanks in advance -B

Hi, Its been a while since I have posted, and I like to think I've learnt quite a bit since I last did! However, I am trying to solve a problem which I just can't seem to get my head around. I have read through most of the Filemaker 9 Bible, but still can't seem to solve this... Essentially, I have an events table which logs every time a customer is visited - with fields including Date and Event Description (among others). This table (called Events) has a portal showing every machine located at that customer - the portal points to "machines_Line_Items". This table in turn is linked to a table called "Cash" by a many-to-many relationship with the fields "Service_ID", …

What type of record locking does FM use - optimistic or pessimistic? Please point me at sources of wisdom on record locking.... had a lockup today - ended up restarting the hardware to clear it ... if there's semaphores or other pointers I can look at, I really would like to know how to find them. Thanks in advance.

Hello, I need help from a FileMaker guru here. I am building a FM database for my employer that will act as a CRM database for our global reseller network. I’m relatively new at this, so bear with me. Everything is working well for the most part, except I am having problems with organizing and displaying sales data for each reseller. I am trying to show reseller data through a portal, which will look like this. Annual sales, show as a quarterly sum Year Q1 Q2 Q3 Q4 Total 2008 $100 $200 $300 $400 $1,000 Additionally, I’d like to see a second portal on the same layout, except, rather than quarter results, yearly results by month. Y…

Well I am new to file maker forums, but it looks like a great place to solve a problem or two I want to know if there is a summary that will summarize the contents of one field similar to the count function, but based on the value of the field. As fas as I am aware, the count field in the summary will only summarize the total number of repititions. What if there are only two values in a particular field; 1 and 2. How could I count a total number of occurances for each field entry? Thanks in advance for your help!

I need to calculate the number of days/weeks/months/years a unit is rented. I am using the calculation attached. Here's the issue. We have a 2 week minimum rental, so, anything less than 2 weeks should show as 2 weeks. No problem. But, from a statistical point of view we'd like to track what units are renting and for what period of time. If I have a unit rented from Oct. 1 - Nov 2 the calc shows 2 months. How can I show it as 1 Month/1 Day or 1 year 3 months, etc. without having to have a calculation the length of my arm?

Hey all, I was wondering if anyone happened to know how I might automatically check a check box when a value is selected from a drop down menu. If anyone has any ideas or solutions that would be great. Thank you :)

I have two databases 1. customer_database.fp7 that has multiple records per customer. ie: Name,email,date ordered,product name record1: joe blogs, [email protected],11/4/2008,product1 record2: joe blogs, [email protected],8/2/2007,product4 2.email_database.fp7 is a database for customer service to answer emails. The two databases are joined by email address. The problem I am having is that when I come across an email from [email protected] in the email_database.fp7 it shows his old order from 8/2/07 instead of the one from 11/04/08. Is there a way to sort by newest record or something in the customer_database.fp7 so that the related record from customer_dat…

Problem with lookup data being reset A field called "activity_resources" is auto entered in the PROGRAM table from the RESOURCE table by a lookup dependending on the field "activity" that the user selects. A user can change the text state of field "activity" using check boxes, this text state is coded in the calc of the field "activity" e.g. case( PROGRAM::To Change = "Ch"; TextColor(PROGRAM::Activity ; RGB(255; 50; 50)); PROGRAM::To Change = "N"; TextColor(PROGRAM::Activity ; RGB(50; 255; 50)); Evaluate(Quote(activity)) ) PROBLEM: Each time a check box is checked the "activity_resources" are set back to the default and any changes ar…

Any good methods to generate random alpha-numeric numbers? Certificate Field=7 digit number set by script.User determines start number and amount of records to generate.This number must be unique... Each series will be 10,000 number increments. Unless someone sticks a 100,000 in there... So I guess it doesn't matter if pin is unique but it would be nice... Pin Field=6 digit random numeric. =auto-enter calc =Right("000000" & Random* 999999; 6) I want the pin to be alpha numeric... Thanks in Advance!

I have a field that contains text, such as "Asia Pacific (Korea)" - and I want to pull just the word(s) within the parentheses out and populate it into another field - so I would in this case be pulling the letters Korea - how can I do this?

Greets forum...need some help!!! I am looking for a way to tell what fields have changed when a record is saved. I am interested in knowing when a value is changed in a specific field so I can report on it. For example, in the Inventory table I need to know if the Price has changed (when and by whom) but not if the quantity on hand has changed. I have done some experimenting with 'Validate by Calculation' to try to set a flag in another column but it doesn't seem to be working, at least not working the ways I have tried. Any and all suggestions would be appreciated!!!

Is there a function that will return whether a field on a layout allows entry in Browse/Find mode? I'm looking at various meta-field functions and none of them seem to do this.

Hi all: I would like to produce a list of all my Calc fields, showing the metedata settings (Indexing, Do not eval if empty, do not store, globals, etc ...) The DDR isn't useful for this. Do any of you know of a way to create this report other than manually mushing through the fields one at a time? Thanks!

Hi, I am really new to Filemaker Pro 9 and have been in charge of finishing the layouts for invoicing/inventory/client database since the person hired did not finish their job! I am stumped on Sales Tax.. Could someone help me step by step help me on how to do this script? The business is a gallery, so whenever we ship out-of-state we do not charge Sales Tax. Other wise we have New York State Tax which is .08375 and sometimes people come in from Westchester which is .0675. So somehow the field needs to be optional but still be part of a calculation. Any help would be greatly appreciated! : Thanks in advance, Cheryl[color:green]

Hello: I’m trying to Configure a container field to accept a file path to a jpeg from a text field, and then to display it as a picture in the container box. I have been able to get the container field accept a path through a calculated value using GetAsText (FieldName), and to display the jpeg as a file (which can be opened or exported), but it will not display as an inserted picture. Ultimately I need to automate the insertion of pictures via database generated file paths. Exmp: file:/Macintosh HD/Users/jon/Desktop/Shapes/pic3.jpg is entered in “field_1” pic3.jpg displays as a lovely little picture in container field “field_2” – provided of course that t…

I'm hoping to get some help on something I've been working on for several days and have not been able to figure out! I'm designing a database (I'm a newbie) for a High School Honors Band Audition. I'm working on the front end and trying to get filemaker to assign audition numbers to each student based on the instrument they are auditioning on. So flutes would be A(x) piccolos would be B(x) etc. for all 23 different instruments that we are auditioning that day. Each number has to be unique and they all should start at 1 and go up to the total number of people auditioning on each instrument with no gaps in between numbers. I've got it worked out so that when I enter th…

Hi, In a lookup when there's no exact match, how can I get the average between the lower and the higher value? Thanx

Hi, I've been using FM for about 1.5 years, but have just recently started developing my own layouts for my new photography business. I'm trying to create a unique serial/record # for each new record, but can't figure out how. I want the number to start with a backwards date, like this... 081106 (for November 6th, 2008), followed by a one digit serial #. I'd like the serial number to start back at 1 each day, since I doubt I'll ever need to create more than 10 new records in one day. Someone told me I could have a calculation that said "if day is > previous record's day, then serial # = 1" to reset the serial number each day. I don't know how to create…

I have a simple master detail database. the detail records are very basic: just a date and a number. The number field is not sequential. When I add new records I would like to have be able to have a "dummy" field on the form(or be prompted to enter a date). Then, when I go to edit mode and enter a new detail record I would like to have FM auto enter that date (so I don't have to keep typing it over and over again). Any suggestions are greatly appreciated

Hello all, I'm having trouble with a calculation that I need some help in (FM 9 Advanced). I have 4 fields: A date field called "subscribed date" (ex. 7/31/2008) A number field called "Payment cycle in days" (ex. 30) A date calulation field called "payment date" (ex. 8/30/2008)that adds subscribed date and payment date A calulation field called " next payment date" that has a case statemnt that adds 30 days to show the next payment date(ex. 9/30/2008)(case statement is a few paragraphs below) So lets say I subscribe on 7/31/2008. My payment date is 30 days so the calculation field comes up as 8/30/2008. So far so good. Now here's my problem, I wa…

Looking to have emails sent using Filemaker and Outlook that will allow me to place the recipient's name in the body text, taken from a field in their record. I have the email letter all set up with a generic "hello newsletter subscriber" but I'd rather have it show their name. Dooable?

:) Hi, I have a database that I put in three years ago, originally designed in FM8. It has worked fine until recently when it started missing the dates in the line items for certain items in the invoice portal. It doesn't happen all the time and is generally the first or second portal row in the invoice. The date is defined as a lookup field from the invoice table and the relationship is based on the invoice number. Any ideas why this should happen. Thanks in advance

With much credit and due thanks to this (and other forums) I was recently able to come up with a solution to get the count of unique values in a field on a report. I'm really surprised a program that's been around so long doesn't have a more direct way of accomplishing this. Not a knock - I'm new to FMP and generally like it's ease of use. Which is again why I am surprised this is so complicated. Anyhow, here is how I got my count of unique values: (1) Create a calculated field: 1 / GetSummary(CountOfItem, Item) (2) Create a summary field which totals the above calculated field I'm not entirely sure how this works - really step one is the conf…

I need to number records in a self join relationship. A simplified table might look like this: JobNumber Department Number 12345 Sales 1 12345 Sales 2 12345 Shipping 1 12345 Sales 3 A self join is created on the JobNumber AND Department. The Number field is an auto-enter calculation. The first occurance of a particular relationship has to be Number 1, the second Number 2 and so on. The problem I'm having is calculating Number 1 for the first occurance in a relationship. Obviously no records match the relationship prior to the first record being created. I need to test for that condition where no records exist in the relationship and then set…

I had some help before to format the phone numbers I put in the field. This is the code I got: Let([ Step1 = If(Left(WorkPhone;1) = "0"; "Y"; "N"); Step2 = If(Step1 = "N"; "0" & WorkPhone; WorkPhone); Step3 = TextStyleRemove (Step2 ; AllStyles ); Step4 = TextFont(Step3; "Helvetica"); Step5 = TextSize (Step4; 12) ]; Step5) But now i also need a string in the code that takes away spaces from any number. i.e. 08-10 22 33 to 08-102233. As I don't know how to do that I ask here for some help, and thank you in advance!

I am tracking the number of items per month I have a year and month field in the table. I have a report that has leading and trailing subsummary for year and month. on the leading subsummary by month, I can correctly total the items using "countofmonth" summary field. on the trailing subsummary by year, I can also total the items for the year by using a calculation getsummary(countofmonth, year) What I really want is a to display the average number of items per month. At a minimum, just the simple grand "average per month", but this on a yearly basis would be cool too. What I cannot do is perform this calcuation: If the yearly total f…

Hi, How would you manage counting the frequency of all values in a field within a table? For instance, there are 10 records in Table1. Field1 in Table1 has the following values: Empty, 2, 4, Empty, 3, 1, 4, 4, 1, 4 The information I need would be: Empty = 2 times 1 = 2 times 2 = 1 time 3 = 1 time 4 = 4 times I guess this can be handled by a self cartesian relationship but I do not want to create more than one relationship or several different calculated fields. Field1 values are not part of a list: they could be any numeric value, included zero or even the field could be empty). Thanks in advance

I'm new to Filemaker though I have experience with MySQL. I have a very simple, one table database of stock quotes: Date Symbol Open High Low Close Volume ----------------------------------------------------------- 10/31/08 ABC 10 12 9 11 9990303 10/31/08 DEF 20 22 18 21 1909933 . . . I'm trying to create a calculated field that lists the trailing "moving" average for each stock based on the last 5, 10, 30 day: Symbol 5dayAVG 10dayAVG 30dayAVG------------------------------------------------------------------------- ABC 11 10 9 DEF 22 20 18 . . . In MySQL this is straightforward,…

I have a database that is used to keep track of student awards for each month and print out certificates with each students award total for that month and also a total for each student for the whole year. I'm not sure what the best way to go about it is. A simple drop down with the numbers from 1 - 10 would work until someone gets more than 10. A button that would increment the counter by 1 might work, but I haven't figured out how to write the script. Any suggestions?

Hi Guys, Need some help with a calc. I have a database which stores contact details which includes email addresses. I use Outlook to send the emails out from FileMaker 9. Unfortunately, outlook has a limit of 32000 characters and occasionally when we send out bulk emails, they exceed the character limit which means we dont send out emails to those recipients at the bottom in the list. What I would like to do is split the field (collection of all the emails) I have which contains all the emails into two and send two emails out, one with the first half of upto 32000 characters, and then a second with the remainder. Can someone tell me how I can calculate obt…

Hi I was wondering how you can find a pattern in text ONLY from the begining of the text. I have for example the string 3.1.1 and I want it to say "true" when matching with the string 3.1.1.23.43.5 but NOT with string 5.4.3.1.1.43.5.3 how can you do this in Filemaker Calculation? Regards Jocke

I have table X which is related in a one-to-many relationship with daughter table Y. I am wondering if its possible to create a calculation field in table X that will automatically calculate any of the following: 1. the value of the first related Y ID field 2. the value of the last related ID field 3. the number of Y related records. I can imagine how this could be done via a script but I'd like to have this available without requiring a buttonpress.

Hello, I hope somebody can help me with a calculation formula. I have a database of artifacts which have been collected according to a local grid consisting of X-coords and Y-coords. In the database artifacts thus have two fields for X and Y. Most finds are related to their exact position within the grid, ie 114,33x 115,45y. On the other hand, the position of some artifacts are only related to the 50cm2 square units within the grid where they were found. Each such is named according to its orientation within the square meter (NW, SW, SE, NE). The position of these will look like this: 114x115y SW. To be able to perform my analysis, all artifacts need to be transformed …

Hi all, I am new to database development and run into an unexpected error. I am sure someone here can help. I got this error when I tried to store a calculation: "The calculation "field_name" cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage." I think the problem occurred because I am doing a calculation and reference a field in another table so it does not allow me to store the result of the calculation. It works like this: Table 1 has a date field. Table 2 has a calculation: year = year (table1::date_fie…

I haven't come here begging for quite a while... and hope I'm posting in the correct place. I'd like to create a (text?) field that requires no less than and no more than six alpha/numeric characters (in any combination - all of one or the other, or mixed). Possible? If so, will someone please shove me in the right direction re: how to accomplish?

I am sure there is a simple solution and a misunderstanding on my part but, I am trying to delete strings of carriage returns from filemaker so that if someone enters the following: blah blah it will be reformatted to this: blah blah I have tried this and it works to a point: Substitute ( text_field ; [¶ & ¶ ; ¶] ; [ ¶ & ¶ & ¶ ; ¶] ; [ ¶ & ¶ & ¶ & ¶ ; ¶] ; [ ¶ & ¶ & ¶ & ¶ & ¶ ; ¶]) However, it only works for two of them above that it is useless. I need it to only leave one no matter how many there are... stupid copy and paste! Thanks for the help!

I'm trying to figure out how to tell if a word/phrase/text string is the 1st word/phrase/text string, 2nd or 3rd in a list. I need a number (1-3) returned. It's after midnight and my brain is spinning circles...everything i've tried returns me either text or the the character position. Help, is there a way? Ginger

I have a customer table related to an orders table. I'm trying to make a calculation or write a script to calculate commissions. The commission will be calc'd on a set of 4 Orders for customers that I've flagged as "commissionable". For new customers it will be the first 4 orders. For old 'Inactive" customers it will be on their most recent 4 orders. I'm not sure the best way to get only the records that fit the criteria. I've had no luck with a calculated field. I've tried a script but it just loops through all the orders. Any pointers are much appreicated. Erick P.

Sorry I fill this forum, but I truly try to find the answer in the manual and googling before posting a question. So, I make a number field with the price, named Price. I format the price like this i.e. 2,995.00. So far so good. But when I on another layout use the price in a text like <> this text does not get formated with the decimals and commas. It will be written 2995 only. So my question is if I can solve this?

I have 3 fields: InfiniteSupply_List (value list with choice of Yes, No) Available (auto enter calc: If (InfiniteSupply_List = "Yes" ; "" ; Available) Do not replace value is unchecked) InfiniteSupply (auto enter calc: If (InfiniteSupply_List = "Yes" ; 1 ; 0 ) Do not replace value is unchecked) This is the work-flow: The user will select Yes or No depending on if the supply is infinite or not. If Yes is chosen the Available field will be empty, even if they enter a number by mistake it will revert to empty due to calculation. At the same time InfiniteSupply will be set to 0 or 1 depending on InfiniteSupply_List. So far so good. It works nicely and…

I have a question regarding partial orders received. We keep track of all orders and when we receive an order we push it to property and the assign it to a department and the department assigns it to a user. Some of the orders will come in two or three shipments and sometimes arrive weeks apart. The ordering agent needs to get the product into the system ASAP so it can be properly assigned and documented. What happens now is if an order comes in it will ask for the entire order to be entered into inventory, whether the entire order was receive or not. What I would like to do is make a few calculation fields, as well as, a few IF statements and would l…

I have a portal with line items. On each item I have a yes/no tax drop down. I want to add up the items that have Tax=Yes and put them in toTaxTotal. I have: If ( lineItems 2::taxYesNo = "Yes" ; Sum ( lineItems 2::sellExtended ) ) I end up with all the line items being totaled. Not just the ones that equal yes. How can I get it just to total the ones that equal Yes. Thanks in advance.

In order to speed up data entry I am trying to create calculation that formats any four valid numbers, (with or without a colon), to the format required by FileMaker for a time field i.e. hh:mm In other words- 1. if the person enters the time correctly as hh:mm the calculation does nothing. 2. if the person enters hhmm the colon is inserted 3. if an incorrect 24hour time is entered eg 26:35 or 2a:25 the calculation returns for example a question mark. At present I have three fields that I use related to the duration of a procedure: StartTime, FinishTime and Duraton. For all three the type of field= “time” StartTime and FinishTime are entered manually, …

My database has a table called SHOWS with a layout of the same name, and a table called LINKS EXHIBITORS TO SHOWS ("LETS" for short) with a layout of the same name. SHOWS has a portal that points to LETS. One of the fields in LETS is "Booth Number" and I'd like this to be a unique value *per*show*. I tried a couple of ways of doing this and ran into dead ends, so I need help. I first tried creating a calculation field ("calcShowBooth") in LETS that puts the show ID, a space, and the booth number into a string. This worked but there's no way to have a calculation designated as needing to be unique. I next turned that calculation field into a text field, auto…

I have a timesheet that shows a summary for the total time spent on each job using the Related Records function. A job might have 50 time entries and the time for each is working nicely plus a summary field of total time for each job is working fine. All times for all jobs are in a single table. A summary field shows the total for all times spent for all jobs. Just as it should. The times come in 3 types currently designated by a letter in a text field. In case it matters the designations encode Quoted, Unquoted and Non-Chargeable times. How can I show the total time filtered for each of the 3 types for each Related Record? I've been hacking at this f…

I've been stumped here for a few days. I have a customer table and an order table related via the CustomerID field. I have 3 basic products that customers order. I want to store the count of each order type in a calculated field inside the customer table. I need to store it because I have to export those fields. For product 1 I created a calc field that says: Case ( _orders::productA = "Yes" ; Count ( _orders::order_id ) ; 0 ) This works perfectly. The strange thing is when I add a second calc field for productB, it only works for some records. For example, customer #80 has 250 orders for productB but the calc field below returns 0. …

I have a script that pulls up events that student can signup for. But I want it to only show student the events from the date they login and greater how do I work this into my script?

Hi all This could have come under relationships, or even exporting, wasn't sure where to post so tried here, hope that's right! What I am trying to do is a 2 stage process... 1) I have a table, this table only contains 1 field (my calc field) and this table will only ever have 1 record. The field is a text calc field I would like it to look at another table (called base) and look at 1 particular field (called Name) Then return the Name for all records in that table So the text field would just look like Fred Mike John Phil And so on My next task is to then export this one field as a text document, containing the format So the …

What calculation do I need in a calculation field for displaying only 2 dezimals? Thanks TMAS

As usual, hard to summarise in a heading! My database processes text for a mail order catalogue. Various ranges of products need a dagger (†) to appear after their description to show they are exempt from a money back guarantee. I have a field for each product called 'Dagger' which is currently defined as follows: If ( Position ( Line1OfDesc ; "Earring" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "fridg" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "freezer" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "washing machine" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "washer" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "condenser" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "dishwasher" ; …