Calculation Engine (Define Fields)

I need help with a formula to capture a person's age at the time of a specific event and have that specific age remain in history without recalculating. Is there anyone out there that can help me?

Hello! Anyone know how to construct a calculation to convert long title/text to a short easily displayed short title? example: The Wonderful Walruses Went Home ~ to ~ The Wonderful Walrus... I started this, but don't know how to finish it off, or if it is right to begin with: Case( Length (title) > 10; "Too Long" ) obviously "Too Long" is NOT the result I want. Any help??? :)

HELLO! I worked up the attached calculation that works off getting current date and comparing to set deadline date, resulting in a user intuitive label. Example; "Due Today, Tomorrow, Wednesday, Etc..." I would like to use this calculated label... BUT!.. I have a concern: Sense the calculation works of the Get (CurrentDate) it will not have a correct result unless it has in real time got the CurrentDate. Example; deadline field was set sometime in the past and record was then viewed sometime in the future, label would not have correct results unless forced to re-check real time CurrentDate. How can I keep this from happening? Maybe... I could run a…

Howdy, howdy... I'm close but just can't figure out the rest of this calc so it takes a company name, makes its first letter red, and the rest of the text white (default color). TextColor ( Left ( CompanyName__lxt ; 1 ) ; RGB ( 255 ; 0 ; 0 ) ) & CompanyName__lxt & ": " & JobTitle__lxt (What kills me is that once I receive a reply I'm going to slap my forehead and go "D'oh!" because the answer will be so simple.) TIA for your help!

Let me explain my topic. I have a portal that brings in data with these fields. Course; Decription ; Credit; Absences, owed The owed field is used for up to 7 different rows depending on the student's schedule. So for example a student may have only 3 rows looking like this: Course Descrip Credit Absences Owed E16 Eng1 1 21 3 F20 Span1 1 22 4 12J Alg 1 1 17 5 What I want to do is have a calculation that totals all the numbers in the "Owed" field. I used the following but of course it took the first number and kept adding it. Sum (MakeUpHours;MakeUpHours;MakeUpHours) I can't make each…

Can anybody start me in the direction of setting up my database so I can get a report like this. Each account will have multiple (job tickets) at multiple locations at multiple (branches). This is a broad total summary, month over month with Year to date. ] Account jan feb mar april may Hess Dunkin Holdiday in I couldnt figure out how to post the jpeg graphic of the excel spreadsheet sample.

I have a numeric calculation field called cAgeAtTheTime which calculates a person's age (to the year and month) on a specific serviceDate. People often participate on many different serviceDates. For example, on one serviceDate their cAgeAtTheTime is 22.3 (22 and 3 months old) and if they attend an event six months later their cAgeAtTheTime would be 22.9. If searching between a range of serviceDates I want to create a calculation that results in the average of cAgeAtTheTime during the specified serviceDate range. What would the calculation be that would give me such a result?

Dear colleagues, I tried to do this by myself and I am kind of ashamed that I have failed so far. Well, I am building a database for my research. It is a group of workers inside an organization. The organization has several levels in terms of jobs and usually you enter in the bottom and (if you are lucky) you reach the top of the pyramid. Each worker has a record and in it I created the fields for the date he reached a new level in the organization. My purpose is to enter the dates in the fields and have a field showing how many years, months and date it took from going from one to another. I am using here two fields Level1 and Level2 (both dates in format day/m…

I have a portal that filters records for students absences. Two fields that are important are "Credit" and "Abs_count" Credit is either 1 or .5 Abs_count is the total absences over a certain allowed amount. 1 credit = you are only allowed 18 absences .5 = you are only allowed 9 absences I am trying to do a calculation that will look at the credit field and see if it is "1" or ".5" then take the appropriate number either "18", "9" and subtract from the total abs_count. I have looked a IF statements and Case and having a problem with both. I made a field called "MakeUpHours" Case ( Get ( Credit ) = 1 - (abs_count) = Sum Get (Credit ) …

I am way out of my league here but I would like to give it a go... At the beginning of each new topic (every 3 lessons), I send the parents an email with what their child is studying at the moment (vocab, grammar etc.) I tradionally did this by hand in a spreadsheet, but since moving the curriculm into FM, I want to see if it is possible to export a list with this data to then use it in a mail-merge email. Let me explain. I have a big curriculum database as follows; Grade Lesson # Topic Vocab Grammar (...conversation, song, phonics etc.) Primary 1º 1 Animals cat, dog I have a Primary 1º 2 Ani…

I have a series of fields pertaining to physical addresses: they are Street Prefix, Street Name, Street Suffix, Post Directional Situs_St_Prefix - ex: North, South, NW, ect or blank Situs_St - Main, Elm, FM 157, County Road 6, Keller Elliott, SW 5th Situs_Sufix - Road, Street, DR, Circle Situs_Dir - N, S, E, W These need to be combined into a full address, but I can not simply use &" "& because often some of these fields are blank and that generates extra blank spaces next to non-existant fields. My next thought was to use a series of If statements, to create rules for combining the fields into one, but it does not seem to be working p…

Dear All, How to count number of days passed in old financial year and number of days that will come in current financial year for a given one year contract.It should not count the date before old financial year. i have to made four quarterly report for the each quarter revenue at the end Dec ,March, June and Sep based on number of days falling in each quarter. Following example will make it more clear. CS Date CE Date Contract Amt. 01/Jan/2012 31/Dec/2012 730 Suppose current financial year starts …

How do I determine if a calculation produces a null result. Eg: I have a calculation max(field1). If no data has been entered in field1 this should return null. How do I test for this? I tried using null and ""; neither worked. Cheers.

I do studies with manta rays. I have two tables, SURVEYS and MANTAS. Each survey can have multiple mantas (MantaIDs). For each survey, the CountMantaIDs field shows the count of mantas for that survey. I have a second field to show the cumulative IDs for all prior surveys plus the current survey. This calculation field is called RunningCountMantaIDs with the result being number and looks like this: If(Get(RecordNumber) > 1;GetNthRecord(RunningCountMantaIDs; Get(RecordNumber) - 1); "") + CountMantaIDs The goal here is to get the cumulative total of all MantaIDs from that survey and all those prior. For 177 of my 375 records my calculation is working fine,…

I am using a simple pattercount function in the middle of a let statement. _stk = patterncount(myfield,"IM") However I need to distinguish between records where myfield contains values like "slim", "blimp" and just "IM") Can the patterncount function use operators like the find statement? I really need to know how many times the the letters "IM" turn up in the field by them selves i.e. not Slim or Blimp etc. Anybody got any ideas to help please? Thanks

Hi everyone, I'm updating a database (originally created in FMP5)New HR.fmp12.zip for our HR Manager and not having a lot of success with a couple of calculations. In the attached file (Username = Manager, password = pass), the main layout is "Employee Info". The calculation for Years with firm is presently "If(Work Status ="Active";(Today-Date of Hire)/365;0". This gives a rough length of employment but isn't accurate due to leap years. Nor does it provide a calculation for Inactive employees. I would like to give the result as 4 years 3 months - days aren't necessary. For Active employees the calculation should be today - Date of Hire, and for Inactive em…

My files are controlled with a field called Customer Number, a simple 4 digit number. I also have a field called "Premium," that each year gets updated with the customers current premium How do I set up a field/calculation to find last year or the years prior premium? Basically I'm trying to create a small table that says: This years premium is: ... Last years premium was: ... The year prior was: ... and then create a chart showing those numbers. Any help would be greatly appreciated Matt

Hello, I'm trying to sum a field "lu_lot_qty" but removing duplicate "ITW" numbers. So in the list there are 2 occurrences of ITW 1369 and 1439. I don't want the sum to include duplicates of these. The sum needs to be by "lu_lot_no". For lot 00000000, the correct sum should be 7,215 (not 13,119).

I am working on a solution that requires the tracking of an applicant's social security number. On the surface this seems like a relatively easy task, however I'm running into a reporting issue in that some customers want full social security number displayed while others want a partial (xxx-xx-1234) and yet others want nothing dispayed. The problem at hand is that the data the applicants are providing can be incomplete or full SSN's. Is it best for me to store the literal number that the applicant has provided and append the appropriate X's where appropriate, should I store a value of xxx-xx-xxx and then append the appropriate numbers as they are provided, or is t…

What check can a script perform on a container field to determine if it contains a sound file? My immediate need is for PC file formats, but a cross-platform check would be useful. Thanks in advance for any help.

I have a group of records, one of the fields is a a Customer Name (not unique). On another table I have a list of names that have been added to a blacklist. I need a calculation field that will compare the customer name to the black list to see if they have been flagged.

Sound recording has no place in my application ... yet a Sound Record dialog pops up whenever the user double clicks on an empty container field. How can this be stopped? 10 years ago, someone posted this method on another forum: "Double clicking will only open sound record if empty" ... implying: put a blank in the container. Is FileMaker's technology still that crude? Isn't there a centralized way to disable the sound recorder function entirely? ... or at least for an individual container? ... or intercept the double click? Having a blank in a container derails the easy check for Field="" means empty ... and may impact other uses of field. I try to avoid…

I have a file we are trying to use in a high school parking lot. The file tracks parking fines. I have the file adding the total of fines owed, but I want to deduct the paid fines from the running total. THe fines are listed in a portal below the ticket by means of a self joining relationship. I have attached the file for you to look at. I guess I am looking for a formula or calculation that will deduct the paid fine amount from the current balance when an "x" is placed in the paid box. Any help will be appreciated. Thanks Lester I am using FMP 11 ParkingFines.fp7.zip

For some reason this very simple If statement isn't working the way I thought it would in a calculation field. If(Day(EntryDate::Date)>15;0;Rates::V_HrsPerMonth) The field EntryDate is a standard date field MM/DD/YY So, I enter a date like 08/25/2012 . The calculated field then should equate to: If 25>15 enter 0. Instead I get the Rate from the second portion of the formula in the field. Any suggestions...it is probably something really simple but I just can't see it today. Thanks in advance for your help! Donna

Any way to check for the presence of a given word in any location within a field?

Is there a calculation to know if the user is entering via web publishing or by the host computer. I am using Supercontainer and if the user logs in via web publishing then the calculation will include the WAN IP address of my router, but if I am logging in locally, then the IP address must be my local host? Thanks so much

I have many receipts, some are paid by one pay others by two or three payments. I use the same date for the same receipt, so I’d like to count them as one. I.e. ReceiptDate Customer Amount DateOfPayment 08/10/2012 100 $25 08/10/2012 08/10/2012 101 $32 08/10/2012 08/10/2012 100 $20 08/12/2012 08/10/2012 102 $15 08/16/2012 09/10/2012 100 $14 09/10/2012 Customer 100 -> 2 (08/10/2012 and 09/10/2012) Customer 101 -> 1 (08/10/2012) Customer 102 -> 1 (08/10/2012) Can anyone point me in the right direction? Thank you.

Ok hoi guys.. I making a DB tracking income and expenses. It has a dashboard to show useful data quicky. It has is own table with one record and lots of fields. In the table expenses i have 1 record for every expense. Every expense has a.o. a category field (from a editable valuelist) and a total amount. I'd like to sell this solution to different costumers so the categories in the value list change per user.... now i want the dashboard to show a list of categories and the total amount spent on them so i made a field dashboard::CategoryList thats calculates a list of categories used. Car Food Office supplies etc. So now I would like to…

Hello Board, I have a table with a list of randomly generated alpha-numberic codes in it. When hitting a button, I want a script to go to that table, to the first field, copy the contents of the field and then put it into the field of another table. There are additional steps but this is the first bit I'm having a problem with! I've tried using the Get (ActiveFieldContents) by first using GoToField but that hasn't returned anything. I've tried GoToField, SelectAll, Copy All, GoTo(target)Field, Paste, but that doesn't work either. Should i use SetField or similar? After it has got the number, i need to delete that record in that RandomNumberTa…

Hello Guys and Gals, I'm a "rookie" FM aficionado (bought my first copy back in FM5 but only did very simple solutions) and my guilty, secret pleasure is playing with Filemaker. I'm trying to do something with a solution and I can't quite figure out how to do it. If someone can give me an idea in which direction (and what functions to study) it would be greatly appreciated. I have a field I'd like to be able to past some keywords or keyword phrases info. It could be 1 to 4 word phrases. I then need to move these these keyword phrases info another field and format the phrases. (either with a quote or capitalize each word, etc) Any idea on how I c…

Hello Board. I have a Products table with ProductCode, ProductGroup and ProductTime in it. I have another table that logs product being inspected. My report details who has inspected which ProductCodes, based on Product Grouping and does a Count of the ProductCode, so I get: Product Group 10/ProductCode 12345/ Total 22 Product Group 12/ProductCode 54321/ Total 9 etc etc What i want to do is take the ProductTime number and multiply it by the Summary Total. So ProductCode 12345 has 12.34mins (decimal time) per item. So I'd want 12.34*22. Then, I'd like a GrandSummary on the bottom of my report. Do I need to build a ProductTimeSummar…

Hi there, I have list of classes (6 on Mon, 7 on Tue etc. - 40 records in all) in list view that I would like to print with a thick balck line between the days, not between each class. I was having a think how to do this and the only solution I could come up with was to create a an empty text box and create a conditional formatting in which if the 'Day' field in the current record is different to that of the 'Day' field in the next record, then the empty text box would be conditionally formatted as black fill. However, I am struggling to create a formula for comparing the data of a recordm in a current field with the data of a record in the next record... Would …

I'm currently working on moving a rather large .fp7 file into .fp12. One of the strange issues I've encountered is that apparently any script with a "Show Custom Dialog" script step that has an Input field defined as a global field is not saving the user entered value into that global field. This is very strange behavior and it works perfectly in Filemaker 11. The only way to fix it is to complete replace the Show Custom Dialog script step that was imported into FM12 with the FM12 script step. Has anybody else encountered this?

Hello Board! I have just bought FM12 and upgraded a very small database run on FM6. I have not really used FM before, only to play around with. I've been asked to write a report to summarise the data but i'm stuck and here to ask for your help. My table has these fields: TimeStamp, Operator, Product Code, JobNo, Everyday i want a report to print out automatically onto the bosses desk. So, i have run a search on the entire table for '//' which is todays data. Then, i need to build a report that groups by JobNo and will count the instances of ProductCode in that data set. A label with this data is made into a table and printed out. We want to know …

I have a budget calculation field that calculates: price * quantity * percent. I created a summary field to calculate the total budget for a given item (this number is correct.) I have also tried to create another field that is Sum (Budget). These 2 fields, that are basically doing the same calculation (or so i think) are coming up with different results. The Summary is correct. The Sum result is coming up with some funky numbers! I am trying to make another calculation which takes total budget (number from summary field or Sum) minus budget spent. I have no idea why the 2 are getting different results. Any insight would be greatly appreciated. Th…

hi, i have one date filter field which stores current date ,i have made 31 date field which contains the auto enter calculation field which is "11" & "/" & Month ( Date_Filter ) & "/" & Year ( Date_Filter ) but the result of date is not calculating properly it shows question marks. I have attached the screen short

I hope I can explain this. I have a subsummary part which creates 3 groupings each with summary total. I want create a grand summary which sums the three - less one particular grouping. eg result; total-bbb (=8) aaa...3 bbb...5 ccc...5

Arrrgggggg. I love these undocumented features! !!! I have four fields. Period = (1-60 months) BeginningBalance = GetNthRecord ( EndingBalance ;Get( RecordNumber)-1 ) Credits = Say, 100 EndingBalance = BeginningBalance + Credits So, Here are the Records Period...BegBal........Credits..........EndBal 1...............0.................100..............100 2............100................100..............200 3............200................100..............300 4............300................100..............400 ....and so on (the dots are spaces) It calculates perfectly in the root table. However, I have another table that …

Hi ya'll! I am wanting to implement a Estimate/Invoice system. The plan is to use the same table for both and when a new record is created it is considered an estimate(in the eyes of the user through conditional text etc.) and then once the estimate is approved they will click a button that says "generate invoice number" and then the record will become an invoice(once again the text will change and show the word invoice instead of estimate) I am doing it this way because I don't feel that there is need to have these as 2 separate tables( I don't need to look back or have record of estimates made once they become invoices) I want to generate an unique invoic…

I thought it would be easy to simply add 20 minutes to a time field by; Time ( Timetable_Hour; Timetable_Minute;"" )+20 With the calculation result being 'Time'. However, not to be. Had a look online but couldn't find anything. Could you please point me in the right direction. Thanks so much and have a great weekend.

I have a script and it has a user input field, I'd like to be able to say "if FIELD is not in the format MM/DD/YYYY then..." Is there a way to do that? Thanks.

Am I missing something? I can't figure out why this won't work... Fields: Notice to Move Out Requirement Notice to Move Out Date If ( Notice to Move Out Date - Get (CurrentDate ) < Notice to Move Out Requirement ; 1 ; 0 ) So if the Notice to Move Out Requirement is 30 days, and they set their Notice to Move Out Date for tomorrow, shouldn't it say Notice to Move OUt Date - Get ( CurrentDate ) is ONE, which is less than 30? Thanks!

Hello, I know there's an easy way to do this but right now I'm drawing a blank. I am calling a script in Filemaker with the following script parameters: List ( "$fileList=" & Quote ( $$COMBINEPDF ) ; "$fileListName=" & Quote ( "$$COMBINEPDF" ) ; ) I want to be able to reset the value of the script name contained within $fileListName to a null value once the script is completed.

Is there a way to have a calculation that says: If any field on this table has the value "True", then "Warning On" and if not, then "Warning Off" ? Thanks for the help...

Is there any possible solution for creating a ken burns type slide show within FileMaker via script Javascript , plugin or other? This would be between records containing pictures in container fields. Any help of suggestions would very much be appreciated. Thank you

I've watched all the Lynda.com tutorials at least once, and I'm having trouble getting off the ground. I have an existing database with many different fields, and I need to import new records to add to it as time goes on. The database consists of records of my piano customers over many years. Every time they schedule a new service appointment, which they can do online at my website, it creates a new record in that online database. Periodically, I download records from that database, which may include records (customers) that I already have. I need to create some kind of unique ID using first and last names so I won't be creating a new record in my database (and cre…

I have been using FM for a few years, and I have always seen repeating fields loitering around but never used them. I am creating a a printable envelope whereby the user enters the starting months and the remaining 11 months of the year appear on the envelope in order ready for printing. I thought this might be a good time to have one field that produces all 11 months in order instead of 11 other fields with calculations. But somehow, I just can't get any good instruction videos or explanations on 'Repeating fields'. Would this be an appropriate time to use them? How on earth would I acheive what I have set out to with 'Repeating Fileds?... or should I just stick wit…

Hi, I need a field should always display the record's position number. Example: Job No. Job Type Position Number 101 School 1 102 College 2 103 office 3 104 Hospital 4 If I sort the above list by "Job Type" then the following will be the result Job No. Job Type Position Number 102 College 2 104 Hospital 4 103 office 3 101 School 1 But I need , the Position Number should be remain same and just to display the record's position number like the following Job No. Job Type Position Number 102 College 1 104 Hospital 2 103 …

I love these undocumented features. I have a calculating field named "TakeHome.Gross.Monthly" in the table LeaseCalc The calculation is: ((LeaseCalc::TakeHome.PerPayPeriod.Person1 * PayPeriodFreq1::TimesPerYear) + (LeaseCalc::TakeHome.PerPayPeriod.Person2 * PayPeriodFreq2::TimesPerYear))/12 So, if LeaseCalc::TakeHome.PerPayPeriod.Person1 = 5000; PayPeriodFreq1::TimesPerYear = 24; LeaseCalc::TakeHome.PerPayPeriod.Person2 = 0; and PayPeriodFreq2::TimesPerYear Then "TakeHome.Gross.Monthly" would = 10,000 or $10,000 per month. The variable appears on the second tab of a tab object. Here is the problem. The first time my script r…

Hello, I am not english speaker so sorry for my written english. I have a textbox on my layout. I would like to fill the text box with a bar code scanner. The bar code scanner return the value of the barcode + enter keystroke. I would like to be able to scan the code with the scanner. Then when the enter key is detected, the textbox value is cleared. The text is linked to a field text. I worked with the button trigger keyStroke. I can detect the enter key. Then I want to delete the value to be able to scan a new code. And it doesn't work. I can remove the text. But I can't remove the new line created by the enter keystroke. Could you adv…

I have a bunch of different facilities that each have a (variable) number of production lines. Each production line record has a field (called "LineStatus") to indicate whether the line is active, undergoing maintenance, idle, or shut down. I need to be able to print a management report with each facility listed and how many production lines are currently active, undergoing maintenance, etc. I tried to use a calculation field: Count(Case(ProductionLines::LineStatus="Active";"")) [and so on for all LineStatus field choices], but all i get for an answer is the infamous question mark. Any help would be greatly appreciated. Thanks, Guy

LS ! Situation : All fields are being displayed on the layout. I have salesinvoices with lines. The total is of course situated in the salesvoice itself. The calc is something like this : total_invoice_ex_vat : Case( IsValid( fvf_FVFR_|_phasing::_k_sales_invoice_id ) ; Sum( fvf_FVFR_|_phasing::_c_total_sales_ex_vat ) ; Sum( fvf_FVFR_|_k_sales_invoice_id::_c_total_sales_ex_vat ) ) However, when a value of _c_total_sales_ex_vat changes, the result is not displayed / refreshed correctly. If I click into the total_invoice_ex_vat-field then the value is refreshed. I can probably do a go to preview > go to browse mode, but is so FM…

I am trying to define a field to produce a "text" result based on a "text fragment". Case ( Description *"Home Depot" or Description *"GENERAL PAINT" ;"Production"; ) This is a ledger. When my 'Description field contains the text "Home Depot" or General Paint" I know the field "Expense Type" will be a 'Production' expense. This automates a small part of my book keeping. Thanks

Hello everyone, I have been having some trouble trying to come up with a calculation. I have a field called Bar set to 100 repetitions with the following calculation. If ( 50 ≥ Get ( CalculationRepetitionNumber ) ; "█" ) This gives me the value "█" for the first 50 repetitions, which is fine. However, when I reference a field instead of the number. The calculation only gives me the value for the first repetition. If ( value ≥ Get ( CalculationRepetitionNumber ) ; "█" ) The field called value has a value of 50. But it doesn't evaluate as the first calculation using the number. Can anyone let me know why this…

Hi All, Please could someone help or advise me, i am building a database where i will be scanning barcodes into a table each product has two barcodes a product and a lot, all the products begin either 01 or A1 and lots always begin 17 or B2 In access i could validate with an after update event to check the information in the field began with 01 or a1 but how would i go about in file maker 12, just started to use it and have only attended a one day course which this wanst covered could someone please help me out?, i have been looking at this for ages and im sure it must be somthing i could use with the onobjectvalidate but yet again could be completely wrong…

I've been looking for a function to get date created and date modified data, for a database with records created without those fields using auto enter. I don't know: 1) whether that information is stored in records automatically (i.e., without a 'date modified' auto enter field); 2) if so, if there is a function to retrieve that data. thanks

I figured it was time to reach out to the community as I'm beginning to tear my hair out over this. I need to be able to count the number of unique values in a portal based on the "participant" field. I've tried a number of solutions I found online but can't get any of them to do what I want. Example: from the portal rows items below I want to return a count of 3 (Judy, Bette and Jim). It seems like this would be so easy I MUST be making it way more complicated then it is. Judy Judy Bette Jim Judy I'm including a small zip file of my data. Any chance someone could take a look at it for me? Just sign in as a Guest and you should have no problem o…

Hi Have a sports db with a goals table and a players table goals are added to the goal field in the goalstable using the players jersey number. I'm trying to do a count of each players goal totals in the players table by using a multi condition if statement, both the goals table and the players table have team and division fields but its not working. Here's the if statement: If ((Division = Goals 2::Division) and (Team = Goals 2::Team) and (JerseyNumber = Goals 2::Goal) ; Count(Goals 2::Goal ); "" ) The key in the relationship is the goal field in the goals table and the jerseyNumber in the players table What happens is the calculation gives all…

Howdy, all: This is a little tricky: I need a calc that will determine the year a student will graduate from high school...which I thought was simple enough by using a string of multiple Case statements, e.g.: Case ( GradeLevel__lxn = "5"; Year(Get(CurrentDate)) + 7 or GradeLevel__lxn = "6"; Year(Get(CurrentDate)) + 6 or GradeLevel__lxn = "7"; Year(Get(CurrentDate)) + 5 or ... ) ...but the problem is the result will differ by a year if the calc is made before January 1 vs. on or after January 1. For example, if a student is in 6th grade in the current school year 2012-2013, he will be in the class of 2019 (2013 + 6); if I were to run t…

Hi All, How can i trim "==" from the record, i.e, ==Motor, Ph. from calculation. Example: Original Value = "==Motor,ph"; Output Value = "Motor,ph". Thanks in advance.

Hi everyone, I am newbie and expecting help for how to use few of my excel due date formulas in FM.The formulas are...... DATE(YEAR(H2),MONTH(H2)+(12/K2),DAY(H2)-1) H2 is start date K2 is Number between 2 to 4 IF(1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(1)),DAY(F2)))) F2 is date K2 is Number between 2 to 4 Thanks in advance.

I have an employee table with an employee leave child table which contains a date field and an hours taken field. My employee table contains a field that determines the allotted hours allowed to be taken each year. I am currently calculating the total time taken for the current year and displaying the remaining balance left for the year. What I'm trying to calculate is the balance of time not taken for all previous years. I'm just not sure how to how to arrive at the time not taken totals for each year. Thank you in advance for any help. Tony 11 Advanced Mac/PC Employee Leave.fp7.zip

I'm trying to make a search of a date range. I can make of the last 2 months, the next 3, last 3, but doesn't work if i get into a month of the last year or the next one. Example: Month ( Get ( CurrentDate ) ) - 5 & "..." & Month ( Get ( CurrentDate ) ) WORKS, it shows the last 6 month period. But if I want last 9 months it doesn't. Month ( Get ( CurrentDate ) ) - 8 & "..." & Month ( Get ( CurrentDate ) ) The script shows 500 error. This seems to work only for the current year. Could anyone tell me what am I doing wrong or where the problem is?

Attached simple button in version 11. I want to name the buttons and have conditional format know itself and turn color if it is the same as the layout name. I do not have the text as the button text but it should not matter. I have LeftWords ( Get ( LayoutName ) ; 1 ) = LeftWords ( GetLayoutObjectAttribute ( "button" ; "content" ) ; 1 ) I thought I understood how it works. I named the text object 'button'. I want to name all my buttons 'button' but I want them to evaluate only themselves and not other buttons. As shown the layout is simple "home" one word, no spaces but it will not match. How can I see this with data viewer? I cannot see condition…

Does anyone have an elegant and efficient calculation for getting the number of days between two days EXCLUDING weekends? I have an old calculation that I used way back in the FM3-FM6 era, but I imagine there is a much better, efficient calculation out there.

In order to build a very dynamic layout I have the following scenario: Users can select multiple "Resources" from a checkbox set and then the associated text label and icon will appear elsewhere on the layout for each selected resource. Also, each resource has a status associated with it (e.g., new, updated, cancelled); the aforementioned icon for each resource will be determined by that resource's current status. It works as follows: 1) User selects 0...n "resources" from a checkbox set (i.e., Resource1, Resource2, ...). This is stored in the field "Activity_Resources" 2) Each resource has a field for storing its current status, e.g., ResourceStatus_Re…

Hello, I'm very new to filemaker, and am essentially trying to find date ranges using calculations, and then wether the record falls within that range determines what is displayed in the calculation field. I have a date field, and a tax year field. I want the tax year to show, e.g. "2011-12" if a record in the date field is between the range 06/04/2011 and 05/04/2012. Any ideas how to do this using a calculation field or are there better ways of approaching this? Any help would be much appreciated!

Hi, I need to get the Name of the Field when the user clicks on it! Why, I have many fields that I do not want to allow access to if my Project Closed fields has the letter "C" in it. And Filemaker's Field Validation is completely confusing to me GetFieldName ( field ) GetField ( fieldName ) And since I don't know which field a user will click, I was hoping to assign the name of the field to a variable to use in the next to last last script step. GetFieldName ( Field ) to me, means that if a user clicks on a field and I've assigned a script to trigger, Filemaker would get the name of the field I've defined in the database. I'm not inte…

I want a statement on each record that says: Last Modified on (date) by (user). The date bit is easy - I just set a field to auto enter the date of the last modification and use that. But how do I capture the account name of the last modifier of that record, and get it updated in the second (user) field? I've tried using a calculation field with Get(AccountName), but this simply records the account that created the record, not the last user. It's got to be a simple job, but my head is spinning.....

Hello to all. I am trying to get a calc to a field that will get data from the last row in a portal? Can it be don. Thank you dani

I have a table of Customers, and a table of Jobs. Some customers have 1 job, and others have 100+. I have a field called Customer Worth, and I'd like to show on each customer record the total they have spent in the past. I don't need it to be year to date or anything, just a running total. If I use the Sum function, I think it only shows current jobs... Thanks...

it might be me, but can some one look at this file. Flip through the sort buttons and note that correct totals are intermittent. summary-glitch-fmp12.zip

I would like to add a particular "meta-data" field to each table in my solution. I want this field to calculate and display the table name. I read a little on the functions "TableNames" and "TableIDs" but from what I understand they will return a list of ALL table names or ALL table IDs used in the file. Since there is no "Get ( TableName )" function that I can see, how does one go about using either "TableNames" or "TableIDs" to return just the table name I am trying to get? I would like to do the same with Layout names as well - so if there is a solution - I imagine the same logic would apply. Much appreciated if you can help.

I am trying to modify a few calculations which removes duplicates. I have tested probably 20 of them I found on various websites and all fail because my need is probably strange but here it is: Remove matching values in one list from another list. Sounds simple but it is a big deal and very complex from what I have read. The two closest calculations I have found is substituteValues (by Comment) and omitValues (by Geoff Graham), which are in this file but they both fail according to my strange rules and I need to adjust something. I have been trying since last Thursday and I am at the end of my ropes, having tried everything to modify both of them and even create m…

Hello After a bit of help with adding leading and trailing zeros to a number that I enter in a field. When I enter a number into field X I would like the following calculations to be made to change the field contents. It is a unit cost type number field, so there could be several decimal places (i.e. it could be 0.10 or it could be 0.102444 ). Obviously if a field is entered in a 'proper' currency format @ 2 decimal places (i.e. 1.10, or 0.35 ) then obviously nothing would need to be done to field contents. Hope this makes sense. If "1" is entered, I would like it to add 2 trailing zeros , like ".00" - so "1" would be come "1.00" If "1.1" is entered,…

hello I have some urls the user can see in a web viewer. While they have the urls open i would like to see some quotes (text) change in another part of the layout. I would like them to change every 30 seconds. The problem is when a script is used it displays continue or cancel options. I am asking if there is an other way to show these different quotes without straining the look of the layout and no need for user interaction. It would just change at regular intervals. Thank You

Hello, I'm adding a field to an existing database. I'd like this field to get a value from a field in another table based on two criteria. The tables (A and B are related by two criteria (1 and 2). Right now I have: If ( A1 = B::B1 AND A2 = B::B2; GetField ( B::B3 );"") But nothing happens. I'm a novice, so any suggestions on better code would be greatly appreciated.

Hi, I'm a basic user of FM pro and would like to know how to achieve the following. I have a store room. In it are boxes. Inside the boxes are cables. I receive boxes to put into the store room and I take cables out of the store room (I actually take them out and have them in a holding area until they are used). When I use the cabels I would like them removed from the stores count. My problems are that I can't see how the relationship would work in FM, but I'm sure FM can do it. I also can't see how to have it setup so that the boxes are added to the stores tab (I was thinking to have a stores tab and a floor tab) and the total number of boxes for each cabl…

I have this text Receipt-Bel Air Thu Jun 14 2012 $34.61 and this calc Trim ( Let ( [ text = allReciepts::Description ; prefix = "Receipt-"; suffic = "Thu" ; start = Position ( Text ; prefix ; 1 ; 1 ) + Length ( prefix ) ; end = Position ( Text ; suffic ; start ; 1 ) ] ; Middle ( Text ; start ; end - start ) )) Returns the correct "Bel Air" but my records can use any day of the week name, first three characters as shown. I tried the Choose Function, but I can't seem to get it to work. TIA for your assistance, Kiele

We have a button which allows our users in increase or decrease text to fit into a field. The botton set up is simple: textSizeChg ( [field] ; "DOWN" ) and there's a corresponding button, textSizeChg ( [field] ; "UP" ) However, we recently started importing a wine called 'KM501". Whenever the business tries to Up or Down the text size (which begins without a font size attached to it), FM is interpreting 501 as being the font size - not the text to apply the up or down to. So the down button takes the font size to a gigantic 500 and the up button takes it to 502! Once at that size, the business can't click down to get it to "10" where it's optimal (…

I'm building an inventory tracking DB for a rental company. Most of it is pretty straight forward but the one thing I'm not sure how to approach is calculating the remaining inventory on a given date. It would have to subtract all items reserved for any date range that covers the day in question. Something like: On: 7/24/12 ITEM ON HAND TOTAL Table 5 7 Chairs 30 80 -where TOTAL is the total number of that item owned by the shop, and ON HAND is the number left that is not being rented on that date Then, if one table and 10 chairs are returned on the 25th (and nothing else is rented) the 26th would look like: On: 7/26/12 …

Hello, I got a problem with a calculation, perhaps someone can help me out? I got these user entered fields: DE_Purchase Price DE_Sale Price Tax Free (single checkbox) I got these calculated fields: DE_Original Price calculation: 100*DE_Sale Price/85 DE_Profit If ( DE_Sale Price > 1; DE_Sale Price - DE_Purchase Price ; 0 ) What I would like to achieve is that if the user checks "Tax Free" FMP refrain from subtracting the 15% fee from the profit. I mean the program does correctly calculate the profit and subtract the fees as intended but wheter I check or not the "Tax Free" field it does not recalculate (or calculate at all) t…

Hi, I don't know is possible to define a field with an automatic intro of 12 digits number: year/month-Day-hour-minute-second (12/0726184752). Is possible? Thanks!

What is the recommended method for someone to set a sort of global type of variable so that it will be the value for a field in all of the records and only need to be changed very occasionally (two or three times a year)? I'd like to have a separate table that just contains one record and one field, e.g., current term like "2011-2012". Then I'd like a calculation in another table for the Current Term field in that table to be the value of the current term from the one record table. I can't reference that field in a calculation because the tables are nor related. There must be a much better way to solve this. The reason I don't automatically calculate this from th…

Hi, I am new to this. I want to track stock and have a portal on my product page which shows invoice line items relating to a given product. The qualities in each of these line items are summed (via another filtered portal from a sum calculation in line items). I have an opening stock for each product and I want to subtract the total sold (from line item portal) to this. Is this possible? Many thanks, Alex

Hi all The first example on the sheet is done with a find and looping script but i would like to get all the dates to line up as shown on the second example on the sheet attached. Thanks in advance Lionel continunity form.pdf

Greetings, I've got a portal row which displays the time for runners in each row. Outside the portal, I have a field which calculates the Sum time of all the runners within the portal. What I have been trying to accomplish now is to have another field which makes a Sum of the top 3 times within the portal. Let's say we have 4 runners with: Runner 1 - 1 minute Runner 2 - 4 minutes Runner 3 - 1 minute Runner 4 - 1 minute Top 3 times sum: 3 minutes Does anyone know how to achieve this? Thanks

I have been asked to do typists' jobs for them. If they put have a paragraph and have more than one carriage return they want it removed. And if it ends or begins with extra carriage returns they want them removed. I have tried auto-enter please see test file. I also need to remove formatting if they paste bold or other sizes and fonts. It does not work. Is it better to use script trigger? test.zip

Hello all, I'm trying to figure out whether to make field a Summary field or a Calculation that does the same things (Count; Sum, etc) They seem to have to same options and serve the same purpose. I am wondering if I am missing something that might adversely affect my data.... Any insight be greatly appreciated.

HELLO This i a real hard one. I have a movie that plays on a webpage and I VIEW it in filemaker via the web viewer. When the movie finishes playing on the web page it automatically goes to another URL that i will call www.abc.com. I would like to know if there is a way for filemaker to detect the change in the url and could move to another layout i will call 'FEATURED" when the web viewer changes to the url "www.abc.com ? THANK YOU

Problem: I want to populate a calculation with 5 repetitions to reflect the values of a list. The list is returned from a custom function that is parameterized with the field name. Eg: GetMyListOf5 ( GetFieldName( Self )) returns value1 value2 value3 value4 value5 and should end up in the repetitions of my calculation field accordingly: repetition1: value1 repetition2: value2 repetition3: value3 repetition4: value4 repetition5: value5 I've red of Extend() and Get( CalculationRepetitionNumber ) and tried this and that but came to no conclusion. Thanks for your inspiration, Rewolfer.

Hi all, I have a problem I can't seem to solve on my own. I have products in my database that each have 1 of 3 different taxtypes (TAX_A (high tax), TAX_B(low tax), TAX_C(no tax)) and 1 of 3 different producttypes (RENTAL, BUY, PACKAGING) Now on my invoice I want the following TAXES to be displayed. RENTAL (=products that are TAX_A and RENTAL) BUY HIGH (=products that are TAX_A and BUY but not Items::IT_Type= "Drinks 19%" or Items::IT_Type= "Drinks 6%")) BUY LOW (=products that are TAX_B and BUY but not Items::IT_Type= "Drinks 19%" or Items::IT_Type= "Drinks 6%") DRINKS HIGH (=products that are TAX_A and Items::IT_Type= "Drinks 19%") DRINKS LOW…

I have a situation as follows. Ref FieldA x FieldB = fieldC I have values which range from 0 to 1000 as Field A FieldB will calculate if: values are between 0 to 1 I need to multiply fieldA by 250% values are between 1 and 30 multiply fieldA by 200% values are between 30 and 1000 multiply fieldA by 100% but I can't seem to make this work. I am not sure how to calculate if a number is bewtween 2 values... as detailed above. TIA

Hi filemaker forums, We have an existing database (fmp12) that contains the fields order numbers, order status (Delivered, current, invoiced) amount of tasks (lists) and delivery dates. What we want to do is see what orders need to be done and how many tasks are left. I created a field calcData, that concanates the order with the number of tasks to be completed (lists) using this formula. GetAsText ( Order No ) & " " & "(" & If(IsEmpty ( Lists ); " "; GetAsText ( Lists )) & ")" This works, but now, we want another field that displays only the orders with the status CURRENT. So i created another field with this formula. …

I can't seem to find it, but I know its here . . . I need to get a return-delimited set of the unique values for a particular Field -- for instance if I had a Table of Members with a COUNTRY field and I wanted just a simple list of the Countries represented in my Found Set, without repetitions. . . . Canada Egypt France etc. I'm needing this to provide the Y-Axis values for a bar chart that shows how many Members are from each country . . . I'm figuring defining a Field like this is the best way? thanks

Forgive me for adding another topic so quickly. When wanting to populate a certain field with the contents of another field I understand that you can us Look Up or a Calculation. I understand the basic difference between using Look Up and Calculation. But then I ran across GetField today, and now I am wondering what route is best to you use in certain circumstances. Can anyone warn me about the dangers of using a certain method in certain cases? Thank you

I have a series of Jobs, each with a move in date, move out date, and daily rent rate. I would like to option to view each month's rent that's due, based on those dates. Most of them jobs will have a full month, for example, Jan 1 - Dec 31 renter will have a full month of July. Some people may move out in mid-July, so I'd like to know the totals. Mainly, somehow to calculate "if Bob is moving out on July 5th, and his rent rate is $100 per day, July's rent due is $500." Any ideas? Thanks!

Hi all, I'm hoping someone can at least point me in the right direction on this problem I'm trying to solve, as I'm struggling to grasp the best way to do this. Sorry for the length of my first post here, but I figured I'd better explain this as best I can! I am trying to create a report that summarizes sales and inventory values by any combination of product type, product brand, or supplier. Currently, I have the following tables: Sales (Each item has a single record, each record has fields for each month, so, for example, there are fields for Product Name, Jan 2012, Feb 2012, Mar 2012, etc). Inventory (Again, each item has a single record, with…

Hello All, Event mangement problem here. I would appreciate any suggestions. A number of estimates may be created for the same client for the same event (until the client figures out what exactly he/she wishes). I have a field called EstimateVersion and I would like it, if possible, to automatically calculate the "number" of the new estimate (in other words, if this is the 1st estimate it would populate with "1"; if it is the 4th estimate, it would populate with "4"). I've tried various ways using GetField, and ways using related records; but can't seem to make it work. In the end, it is not much of a problem to input this myself, but it would nic…

I am working on a timecard solution. I have three criteria involved in this question. One is the day of the week the pay period starts on. The second is wether the pay period is weekly or Biweekly. If Biweekly then there is a selection for wether the pay period starts on an odd or even week number. Based on those criteria I would like to create a value list that lists all the pay period starting dates in the current year. Current selections in bold; payPeriod - Weekly or Biweekly payPeriodOddEven - Odd or Even periodStartDay - Could be any day of the week. Current Selection Thursday I was hoping I could get some help creating a calculation field …