Calculation Engine (Define Fields)

I've been looking around to try to find this info: When in the process does a field that is set to Auto Enter: Creation Timestamp actually enter the data? On creation? On commit? Is it the same for all creation and modification auto entry? Thanks!

Hello I am fairly new to FileMaker - and wanted to know the best way to count records based on a field, as a global calculation so it can be listed on every layout. For example - I have an "assignee" field which is a username, I would like to count the records based on the logged in user.

I can't figure out how to subtract the total of several child records from a starting value in a parent record: Sounds simple, but it's got me stumped! My database is designed to issue Emissions Reduction Credits to companies for reducing or eliminating sources of air pollution. Those companies then later use those credits to offset new construction, sell them to other companies for cold, hard cash, transfer them to other companies, etc. The initial issuance is all on one Certificate and there can be between one and five pollutant credits on each individual Certificate. A company can own many certificates, but a certificate can only belong to one company. Once i…

I have a uniqueness criteria for a particular number field, but the uniqueness is based on a combination of the number field with a state field (e.g. the number must be unique within all records for that state). I built a custom function to return a boolean if the number+state combination already exists in the database, and that CF uses a simple SQL query, Issue is that the CF seems to work flawlessly when tested via Data Viewer, but when inserted as the field validation calculation, returns a validation failure for any value entered. Can anyone enlighten me as a to a limitation here I may not be aware of? My work around path is a field level trigger to a vali…

Hi all I've created a calculation which is on each case record. It displays the time passed, but within the hours of 9-5, monday to friday. e.g. at monday 10am, the time passed will be 2 hours, if that start time was friday 4pm. The cases are accessed via portals, via a relationship from a single record table. One layout has a portal that shows just 10 records, and another has 8 portals of 15 records each. We now have a problem where the database simply freezes when trying to access these layouts. It is hosted on Filemaker Server 11. I couldn't find a custom function which would work out hours passed within particular time frames, so had to make my own,…

Hi, I am finding the solution for sort value list. Example: Current value list: A 1 A 10 A 11 A 2 A 21 A 3 A 4 I want the value list look like this: A 1 A 2 A 3 A 4 A 10 A 11 A 21 P/s: I need to use the data field = Text. Anyone can help me? Thank you.

Hello, I have an error from the following calculation when inserted as merged text, in that it adds a "0," just before the rest of the merged text. I can't get rid of it. It was not present until I used the "is not" phrases in this calculation to eliminate responses that would not be appropriate for a child using an object-based schedule. Field into which the merge is placed reads: "According to the results of the questionnaire, <<Name>> <<scheduleinteractioncalculation>>." Output looks like this: "According to the results of the questionnaire, Jeremy 0, may respond well to controlled choice built into the schedule. …

FM 12, Adv. We have our meetings on the first Monday of each month. I’m trying to create a calculation field that returns the date of the first Monday in the month following the current date (system date). For example, if the current date is June 3, 2016 it would return June 6, 2016. Or, if the current date is June 17, 2016 it would return July4, 2016. Any suggestions?

I recently tried to implement a Base64 Encode/Decode approach to create a vCard from FMPro as previously described in this post https://radicalappdev.com/blog_02/. The file resides on an iMac at my front office desk running FMPAdv13. When opening the file and running the Base64 script to export the vCard on this Mac, I get an empty card however if I run the file from from FMPAdv15 from my laptop on the same network (hosted on the FMPAdv13 Mac) the vCard is exported as expected. I looked at the encode function and found that in the FM13 file the field has a single "?" however in the FM15 view it is filled with data (screenshots attached). Can anyone shed light on…

I'm not sure if this is the right section in the forums. I have a table of product titles with a single field containing text like this: Sony headphones Bose headphones Panasonic headphones I have a separate table that is a list of brands, like this: Sony Bose Panasonic How do identify the brand name in the product titles, by checking each product title to see if it matches one of the brand records? I'd like output like this: (field 1)Sony headphones (field 2)Sony Bose headphones | Bose Panasonic headphones | Panasonic In other words, I have a list of product titles, and I have to classify each record again…

"Amount" has tax applicable defined by Value List ("Yes"/"No"). I am trying to sort by "Tax Applicable" (taxable and non-taxable). Data is in another table so i have included a "Sum" part to the calculation. Tax Applicable with Subtotal, tax, and Total; Not Tax Applicable; and then a Grand Total for everything. I have tried this type of calculation: If ( Tax Applicable="Yes" ; Sum ( Amount );0 ) and in another field: If ( Tax Applicable="No" ; Sum ( Amount );0 ) It doesn't work. Any ideas? Thanks.

I am trying to convert text to date using GetAsDate function I referred to an old answer on below link: But I couldn't get it working , my system's date format is 2016-01-05 and I used the syntax: GetAsDate ( Right ( date_start_printing_text;4 ) & "-" & Left ( date_start_printing_text;3 ) & "-" & Middle ( date_start_printing_text ; 5 ; 2 ) ) to convert the value format 01-05-2016 date_start_printing_text is a calculation field, returns text value, which calculates its value from a text field with timestamp value using Left function Left ( timestamp_printing ; Position ( timestamp_printing ; " " ; 1 ; 1 ))

I have 20 fields, and I want to select one to highlight in a random manner. (I'm trying to build a "wheel" of sorts to select a number of points to play a game). This will return up to a two digit number: Int ( random * 100) However, there are a lot of numbers greater than 20. How can I pick just between 1 - 20? Would it be easier to have 20 records versus 20 fields? Many thanks for any great ideas!

Hello guys. Small question. How can I check whether a Date is within a date range from two other fields? Thanks

I have a Calculation field (in FMPro 12) that includes the following: Char ( 21 ) & Char ( 22 ) & Char ( 97) [note: Calculation result is set as "Text"] I had expected the result of this calculation to be !"a but instead it is 2 blank spaces and a Can somebody explain why the result doesn't include ! and "

I have a calculation field that sums time from a portal. Because the portal defines time units in decimals, so does my calc field but I need it to convert the decimals into proper format: hh:mm:ss (ex. 1.75 hours needs to show up as 01:45:00) My current code: Sum ( Time Sheet::Hours )

I have a very strange situation here. We have a layout with statistics, that are calculated with a script. If you run the script with a FM13 client or a FM15 client it all works as intended, but when we try FM14 client the result is empty. BUT, if I use Script Debugger on FM14, the result is as intended. Note, the debugger is run with just "play" and no breakpoints - works fine. We have a FMS 14 for the solution. The Server is not a local one - it's located on a remote server hall. Anyone else run into this kind of problem? A note! We have actually had A LOT of problems with FM13 clients connected to FMS14 servers, where the data is not fetched correctly. W…

Hi Guys I have a summary layout thats shows me the "count of" my field named "events". This part works ok. But I also have fields named "events2" and "events3" which are populated by the same value list as "events". I want the summary to show me the "count of" all instances, event +events2 +events3. In the summary define dialogue I can only choose 1 of the above, my question is does anyone know of a way to get the result of all 3? many thanks for any advice offered.

I am importing a csv file and need to use matched field names when importing: the provider of the file adds fields from time to time. However one of the field names in the cvs file ends with a space and when managing field names I do not seem to be able to define a field name which ends in a space. Every time I do the field name has the trailing space removed when I save it. Does anyone have a solution? Thanks in advance.

I have written a calculation to display sales prices (from multiple tables) on my main product table. Each product is unique and has only one selling price, but it may have been offered in multiple sales events. The calculation works, but I am wondering if this is the best way to approach the task. Case ( not IsEmpty (Auction 16_04::Hammer) ; Auction 16_04::Hammer for Inventory ; (Auction 16_02::Hammer) ; Auction 16_02::Hammer for Inventory ; (Auction 15_11::Hammer) ; Auction 15_11::Hammer for Inventory ; (Auction 15_09::Hammer) ; Auction 15_09::Hammer for Inventory ; (Auction 15_07::Hammer) ; Auction 15_07::Hammer for Inventory ; (Auction 15_05::Hamme…

Hi i want to be able to count the rows in a portal which meet a certain criteria. It is a portal of tasks assigned to an employee. One of the fields in the portal are the tasks status i.e. Overdue , Due , Outstanding or completed. I want to display on the opening page the amount of tasks that are due . i managed to get the total of all tasks by using the foundcount function but unable to split it down this function down further by adding another rule. i would appreciate ones help, thanks.

Hello Forum, Can you please assist me with date range calculation for the following situation/problem/challenge: I have a date field = TG_StartDate and would like another field(Season(text field)) to display "HighSeason" if the date is between 15.08.**** and 14.11.****, that is from August 15th to November 14th all years, any year and the rest of the range display "LowSeason" Many Thanks, Miss-Amen!

Hi, everyone! In my database of weekly music charts (FMPro14) there are 4 linked tables: [Hits] - list of the hit singles [Entries] - list of different entries, remixes, reissues [Runs] - list of chart placings for every week [Dates] - list of dates of the weekly charts —————————————————————————————— [Hits] contains: <idHits> index field <Title> text field [Entries] contains: <idEntries> index field <Title> text field <idHits_fk> number field, a foreign key which links [Hits] table with [Entries] table [Runs] contains fields as belo…

Hi on my layout on want some objects to hide if they match one of two rules, however they only follow the first rule how can i get both rules to work ,i have tried using the "or" and "xor" and "and" however get the same results - only the first rule gets followed. (both rules are working as i have put them both first and that one showed) i would appreciate some support on this issue

Hi everyone I have a script trigger in a field inside a portal on 'Layout 1'. The script goes off to a related table (same file), does some calculations, then returns to Layout 1. When it returns, I want it to scroll down TO THE PORTAL ROW IT STARTED FROM, so that that portal row is visible and active, and the user can see that the calculations have been done. Some records have 25 lines in the portal, so it’s tiresome for the user if the script comes back and display portal row 1 instead of (say) portal row 16, because he/she then has to hunt for the correct portal row. My script in the original layout starts: Set Variable [$ActivePortalRow; Value…

I am stuck I need a calculation Odd number -1 if even number - 2

I have created a table to record company vehicle fuel usage, which records the date, vehicle, mileage and fuel (litres) for each time a vehicle is refuelled. I can calculate a vehicle's overall fuel consumption based on max(mileage) - min(mileage) / sum(fuel) for all records related to that vehicle. But how can I calculate the fuel consumption for each individual record. I.e. current(mileage) - last(mileage) / fuel I need some way to only compare the current record with the last fill-up for that vehicle. Any ideas?

(Building Height *12 - Pallet Height ) / ( Pallet Height+ 3) (10' x 12 = 120 - 57" = 63" ) / ( 57 + 3 = 60" ) = 1.05 I need to round this to 1

Hi i want then when i print my pages it should show me X / Y X =current page Y= total pages "X" i have worked out can be achieved through using the <pagenumber> field how cani achieve the Y??? i would appreciate some help , thanks.

Hello All! Newbie to the forum! I am using FM11, How can I separate out this address field? 2323 Lexington Avenue, Columbus, Ohio 43211 I need to have Street, City, State & Zipcode in separate fields. I have tried some calculations I found from various places but didn't work. Thanks, Kevin

I am trying to Round Up to 5 different sizes In one summary field how do I do this 85_96" = 96" 97"_108" = 108" 109"_120= 120" 121_132" = 132" 133_144" = 144"

I am new to FileMaker, but am familiar with MS Access. In Access, what I would want to do is use a combo-box so I could type a person's name (e.g. Smith, John) and what would really be entered is the Person ID (i.e. the key on the person table). How do I do this in FileMaker?

Im currently using a portal to relay certain information from my main database, the information that is related between the two are the fields of 'Brand' and 'Part Number'. The only other fields i have in my portal will be 1. Sale Or Purchase (a drop down list to select either sale or purchase). 2. Quantity 3. Date 4. Price Is there any kind of calculation so that when i select either the 'sale' or 'purchase' option in the drop down list that it will either add or subtract the quantity i then enter into my quantity field, this way my stock list can keep up to date with the correct quantities? …

I am having difficulty removing the .JPG extension from the file name of my imported pictures. The Pictures are actually students pictures saved with the student's ID Numbers. Example SLT-098-00034.jpg , SLT-088-11123.jpg I am trying to extract the real ID Numbers from the File names which should be in this format SLT/098/33322 Accomplishment So Far.. Using the Substitute function, Substitute (text; searchstring; replacestring) , I have been able to convert the file name from SLT-098-00034.jpg to SLT/098/00034.jpg Now, I want to remove the .jpg extension, but can't achieve that using the Substitute function.. Please is there a better way to…

Hi I just need some help with the correct syntax for calculating an average in a Sub Summary report. I have a database with share parcels in it & each of my companies holdings in other companies are recorded there. Now I'm reasonably used to Summary & Grand Summary Totals in an FM report and for me they have usually been a mere Total or Sub Total of a figure but I am having difficulty in getting an average price to appear correctly in a Sub Total. The Grand Total Average 'share price' appears correctly in my reports but no matter what syntax I try I can't get it to appear correctly in the Sub Total. The field 'AverageEntryPrice' is derived by Type: Calcul…

Hi, I'm missing a bit of this filemaker report puzzle for an education database i am building. I want to generate in a report a yearly figure that is the average attendance of visits per month. This is a related database "attend_.fp5", all necessary related fields are set field rather than a direct relationship calc so GetSummarys can work. 2x part definitions ('date_month', 'date_year') Sort by ('date_month', 'date_year') The visit figure is defined as the result of a -selfRelate ('SelfID_Date_combi') -a calc 'cAttendCountIndividual' (1/count(SelfID_Date_combi::@AttendID) -a total Summary 'SUM_TotalcAttendCountIndividual' This provides…

Hello FM Experts! I have started to develope a FM database that keeps the data of patients of a hospital and different kinds of tests that they should do regularly. for easy understanding, I have attached some pictures. In one table should be defined for each test, which patients have been examined. (plz see patients and tests pic). In another hand, different combinations of "done or not-done tests", make different type of Lists (list num 1, list num 2,.....). Therefore I should create a seperate table for keeping these infos. (plz see test and lists pic). Finally I need a table ( layout) somehow as a combination of 2 previous tables. (plz see connection …

Trying to come up with a way to auto enter a serial number that resets each year. user enters in the a date field "production date" the year of that should prefix the serial number 161001 on average < 4000 records per year created Preferred not to be scripted and want it to auto increment on if record is duplicated. And not change if someone changes the production day (as long as its in the current year)

I have a table with field Trainer_Location and Trainer_Name. I want to be able to choose from field Trainer_Location (Club1, Club2, Club3 or Club4) and have the next field Trainer_Name show the related trainer's name. So example: Example 1 Trainer Location: Club 1 Trainers Name: John, Jason, Mike Example 2 Trainer Location: Club 2 Trainers Name: Julie, Todd, Don, Fran

I'm needing a calculation that will subtract x number of work days from date. "x" will be a variable number I've used this calc before but I'm not able to get it to work as I need to subtract working days. http://help.filemaker.com/app/answers/detail/a_id/3926/~/calculating-a-finish-date-given-a-starting-date-and-the-number-of-work-days Thanks

Hi all, I recently “inherited” a FileMaker database at work, which I finally got mostly running despite being completely new to database building. There is just one part I simply can’t get to work. There are several records which use a checkbox. On a different layout, I linked this checkbox to a portal, so that it shows which records correspond to the checked boxes. This works just fine, except that it only uses the logical OR function, and I want it to show the logical AND function. So for instance, if my checkbox has Fish, Meat and Vegetables as options, and I click Meat and Vegetables, I want the portal to show only the names of people who clic…

I have a Personnel table and a Timesheet table, related by a Personnel UID (unique ID field). (One Personnel record to many Timesheet records.) The Timesheet table includes a field "Approval Status" that can have 4 possible values based on a value list (New, Submitted, Approved, Needs Revision). I'm trying to find the cleanest way to show how many related Timesheet records have a status of "Submitted" on each Personnel record. The ultimate goal will be a "dashboard" for managers (based on Personnel table) with a portal of their direct reports (self-related table PersonnelSelf_DirectReports based on a manager field) that displays how many records each direct…

Hello Everyone Very new to FileMaker here. I have an Event Table with fields EventID, EventDate, and EventDuration, AND an Attendees Table with fields EventID, PersonID, and SignupCreatedTimeStamp. I would like to add a Field to the Attendees table that that will tell me how many hours of activities this person is already signed up for in the 30 days before/after that event. I can do this using the Find Records method, but I think this could be done in a Calculation Field using GetSummary. The point of this all is to prevent people from signing up for more than 20 hours of activities in a month. After I get this calculation worked out, other checks will …

Is there a calculation or custom function that can accomplish the following number notation? 1 --> $1 10 --> $10 100 --> $100 1000 --> $1K 10000 --> $10K 100000 --> $100K 1000000 --> $1M 10000000 --> $10M 100000000 --> $100M Thank you Anthony

Hi All I'm running into a bit of a calculation dead end. I have an "order" table displayed in a portal where my users can create orders. The user can add a group of records (a kit) from the inventory table. The first related record is a "master" record which shows a description title and a subtotal (see attached image) and where I would like the user to be able to adjust the quantity for the overall group of records. When initiated the script assigns these related records a GroupID which is currently set as a self join (OrderLine::GroupID = OrderLineKit::GroupID). The system calculates accurately if the master record quantity is set to 1, but if the user increas…

Is there a way to use same record with other user? basically i select the record using checkbox but if another user use the same record, A popup message appear saying you cannot modify this record.

I need to create a 'Duration' field for a Table of movies to enter the Hours / Mins / Seconds. The 'Time' Field type seems to be designed for time of day, not a length of time, and it seems like it could be used for my purposes here, since no film would be longer than 24 hours. Since the Time field is stored a numerical value of seconds, it would seem to 'behave' as I'd want it too, and I could use calculations to render the time in minutes or hours and minutes as needed. But is this a 'best practice'? Am I overlooking some potential unintended and unwanted consequences using a Time Field for Duration as opposed to time of day? Something that will come back to …

Use-case: Full birthdate is not known… only the year. Better to have a valid 4 digit year OR a full, valid date than whatever the user feels like entering. The field is Contacts::Birthdate (Text). Field validation by calc is turned on with this code: Let( [ d = Self ]; IsEmpty(d) or d = GetAsDate(d) or (d = Filter(d; "0123456789") and Length(d) = 4) ) If I enter nothing in Birthdate, it is accepted. If I enter "4/25/2016" in Birthdate, it is accepted. If i enter "2016" in Birthdate, it is rejected as not meeting field validation requirements. BUT, if I run the code below in Data Viewer, it evaluates as true (or "1"): Let( [ …

As the title says is there a way to process this.

Here's a link from Filemaker's support knowledge base: http://help.filemaker.com/app/answers/detail/a_id/8389/related/1/session/L2F2LzEvdGltZS8xNDYwMDU0Mjg1L3NpZC9GSm80R3JObQ%3D%3D I have this calculation below in an 'Opening Balance' field but it doesn't work after 29 records. Is there any work around for this? If( Get ( RecordNumber )=1;combinedRecords_GL Accounts_AccountExport::Bal Fwd;GetNthRecord ( Closing Balance ; Get ( RecordNumber ) - 1 ))

I'm needing to create chart that display a breakout of orders by week for the last n weeks. I'm able to get the chart chart I needed by having a field called week that a calculation = WeekOfYear ( CreateDate ). But I don't like week number for display. Would prefer either a Date like "4/17/2016" or "4/17/2016 thru 4/23/26" Any tips would greatly help. Thanks

Quick Quesstion, I have a number that I want to be formatted automatically upon entry to keep everything the same. The number is 11 characters and broken up as 2,3,3,3 or eg 11 111 111 111. Now I have used a calculation Let( @NumbersOnly = Filter(Self; "0123456789"); Case( Length(@NumbersOnly) = 10; "("&""& Left (@NumbersOnly; 2) &")"& "" &" "& Middle(@NumbersOnly;3;4) & "-" & Right (@NumbersOnly;4) )) to format a phone number specifically and that is fine. But for the love of Mike how would I break up thea bove example to the 11 111 111 111 format when enterd as 11111111111

Hello, I'm creating my own FM solution to help me with prices,.. for making frames for canvas prints. Actually It says me all the parts needed, size of the canvas,... Only option I got problems it's a calculation to optimice cuts of the wood. The wood strip got 250 cm. and I get a maximum of 10 differents lenghts to mix (5 fields with width and height) Any idea of the calculations to follow to get the the cuts optimiced?

I have a large DB of some 500K records. I have set up a number of scripts to find specific items in the DB. How can I concatenate these to find a combination of, say, 5 scripts. For example, Script 1, finds all white cats, script 2 all black cats. If I create a script which simply has Perform Script 1 and underneath Perform Script 2 then the script will find all the white cats but then forget this and find all the black cats whereas I want black and white cats. It is too laborious to use the Find requests because each find request for each original script has a number of other elements. I suppose one way is to create a field which I could put an 'x' into (first …

I recently imported records from an excel sheet into my filemaker solution. I just noticed that the dates in the excel sheet were entered in this format 1996-04-19 and as a result, all my date calculations are not working. For example, the Age and other functions. Manually changing the date to 4-19-1996 resolves it. Is there a way i can do this without manually going to each record? because there are a lot of records to deal with.. Thanks for your anticipated response

Hi all I'm trying to create a calculation (order::list) to list related records that have the value of "1" in a field in the related table. My tables are "order" and "order lines". I use a portal for "order lines" on my Order layout. Right now they're connected by Order::OrderID and OrderLines::OrderID. Two fields in order lines table are OrderLines::Description and OrderLines::Flag. If the flag field is "1" I need the list field to list what's in the description field with a carriage return for all related records where the flag field is 1. If it's 0 or empty then that record needs to be ignored. The List function works fine to return the contents of the …

Been trying to format SS#s so user entry of 123456789 becomes 123-45-6789. I've successfully auto formatted telephone numbers with area codes using a calculation code so I know the basics. But, the SS# has me stumped. P L E A S E... someone help !!!

Any idea why $FieldName would not work in a Set Field by Name script step? $FieldName = Table::FieldName I've tried to use Evaluate($FieldName), but that didn't work either, resulting in a 102 error - field not found. Using Table::FieldName directly works, so I know the string is ok.

Hi Guys, I have an IF Statement which makes a calculation. Basically I have 2 multipliers, in CalculationField is an IF Statement that is Result 1 = X * Multiplier1; Result 2 = X * Multiplier2. The CalculationField IF statement works perfectly. But what I want to do is get a total of this. The simply way seems to be to do a SummaryField is Total Of CalculationField. But it's not working! Is it not working because the CalculationField is an IF statement, or is there another reason? Cheers Neil

Hello, So I'm a very much newbie, and perhaps staring to walk before I run - but I'm getting on OK with getting my head around things. Quite possibly the most complicated thing I'm adding to my database is a Serial Calc. The way it would ideally work is that each Client has a 3-figure Prefix unique to them. So ABC, XYZ and so forth. I have this as a Field. My next Field is a 'start from', which has Auto Data entered as 001, although it can be changed for the needs of that Client. Ideally, I am looking for a formula and/or calculation which does the following: 1) Compares the ID of the Client against the 'start from' number 2) Once compared, a…

Please help, I have 4 field date and time from and date and time to This the sample format 4pm 07/07/05 and 8pm 07/08/15

So I have a situation where I want my unique ID field to be a serial number with the prefix of "16-", with the 16 representing the year 2016. In a perfect world this would update on January 1 of next year to "17-", but I'd be just fine with going in and updating the necessary field for it to show 17. Surely there is a way to do this, and its just me having no such luck? Any help greatly appreciated!

Hi Guys, Right, so I'm tearing my hair out over this one, as I just can't seem to understand why it won't work. Here is my calculation field: So I'm asking FM to get the date. If the date it larger than the due date (which is another calculation) AND the Paid Field is "No" then the invoice is obviously overdue. The alternative of "show nothing" it just as a test. So, the due date very clearly says 15 April 2016. The Paid field is "No". So why the heck is the calculation coming back as Overdue? The calculation is set to refresh, so there is no issue there. It's completely odd. That said the Paid Yes/No function seems to work. So if t…

Hi Guys, I have the following fields: 1) Invoice Date (which gets the day the data is entered) 2) Term (which is a number of days from the above date the invoice is due). 3) Due Date. I'm struggling slight on the Due Date. I've made the field a Calculation. My Calculation is (Invoice Date) +Term. So, for example with a 30 day term, and the date as 02/04/2016 my calculated result is 736086 (it should be 30 days from 2nd April).......hmmm not quite the date I was hoping for! I've tried Data Formatting, but can't get the date up. Clearly I''m doing something wrong, and I'm sure it's blindingly obvious? Cheers N

Hi there, I am developing an HR system. I have a field that calculates the employee's employment age in the following format: "1 day, 1 month, 1 year": "GetAsText ( Year ( Get ( CurrentDate ) ) - Year ( First date of employment ) - If ( Get ( CurrentDate ) < Date ( Month ( First date of employment ) ; Day ( First date of employment ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( First date of employment ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( First date of employment ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( First d…

I have a field string->Dog; Cat; Horse; Frog; People I am trying to make a calculation field that will put the value within separate fields. So I got the 1st one--Left(field;Position(field;";";1;1)-1) This works to get the word Dog. But my next calculation field is not working- Left(field;Position(field;";";1;2)-1) Of course I did not get to Third, 4th, 5th. what am I doing wrong? Can someone enlighten me.

Dear forum members I would like to ask for a helping brain at this forum. My questions could be very easy for one expert, if would spare a little time with me. I’m a professor at business, and using filemaker from many years for my student’s records. Now I have to coordinate a whole grade, and I’m using a relational database. With some Students (1, 2, 3) and some Subjects (A, B, C) I’m creating a record for every one (1A, 1B, 1C, 2A, 2B, … , 3C). The question is: Can I enroll every student, with automatic field creation? I wish create all registers for every student at once. I don’t be able to understand if a “get” function or “if” or “calculate function”…

Not sure if this is the right spot to post I apologise in advance if it isnt. My Scenario is this. I have a tax Table which includes GST (VAT) , State Tax, Wine Tax and Alco Pop Taxin the table I have the tax_ID, description and a multiplier for the purposes of calculation specific to each tax. i.e GSt 10% multiplier 1.1, Wine TAx 29% multiplier 1.29 and so on. Now some products are not subject to GST and some are that is OK and I can work that out. However, some products are subject too GST, Wine Tax and State Tax.(for simplicity in this example I will say that State Tax is not included). I have 2 fields set again for simplicity call it Tax 1 and Tax 2 + 2 Fields for the…

So I'm confused on a calculation , I'm not sure if I should use a substitute calculation for what I'm trying to do or how to use that calculation by formatting a unformatted number; Example Unformatted Number 21100 Example Formatted Number 02-1100 So I need to add a "0" in front of the number and then always separate the first two numbers with a "-" the numbers be hide the "-" could be 1 or 20 numbers Ideas?

I am really struggling with coming up with a solution for this one...so hopefully someone can help me out. This is for a visual effects database on a film that is tracking statuses of multiple VFX shots. I was asked to come up with a way to compare anticipated dates from each vendor based on when the vendor plans to final a shot, the previous date that the shot was anticipated to be final and the actual date that the shot was final or PTF (Potential To Final)...all based on the week ending date. I have successfully created a separate layout for each scenario where I have a summary field for each vendor calculating the amount of shots per the week ending date. I am wo…

I have a global email template field on dashboard like: "Please find purchase order #<<PO Number>> attached. Please note we require these goods on or before <<ETA>>, if you can not meet this time please contact <<Production Manager>> as soon as possible." Now I am in Purchase order table (Layout) and I brought this above field from dashboard. How will I change PO Number from current record and ETA, production manager from current record of purchase order table. Should I use substitute? Can I use substitute and change more than one fields text? Slightly confused. Please help.

Hello, This might not be the best way to do it but i have a form that needs to be signed by an inspector and i wanted to auto insert his signature on the form when his name is selected from a dropdown. so i thought if i had all 3 signatures on the form and made them hide until there name appears in the dropdown field. Ive tried several versions even this one but too no avail. Get ( WindowMode ) = 1 or "Dustin Bright" or IsEmpty (Form 29::Assessors Name) but it doesnt do what i want. In fact it shows up for all 3 names and only hides when empty. many thanks Paul

I need to be able to uppercase all cardinal directions (NW, SE, etc) in an address field. I have this resolved using a Perform Find/Replace step for when these occur in the middle of a field ("[space]NW[space]"). However, often these occur to the far right of a field. Is there an easy way to simply uppercase the last two characters in a field without changing anything else that's been entered?

The Insert PDF context menu item is greyed out on my container field when I have my database open locally. My Google Fu has failed me on solving this. My container field is on a layout, not in a portal. I have Interactive Content checked. I have a PDF viewer working in IE. (Zeon - I do not know where I got this plugin - I very rarely use IE.). I also have an older version of NitroPDF Pro which I use daily, but no IE plugin at the moment. The container field Storage settings are "Store container data externally" and using Open storage option. I also tried the secure storage option which was the default. I have FM Pro 14.0.4 and this database …

Hi I am trying to find away to calculate the the total sum held in a field named "sub total" on all FOUND records, can anyone advise please? eg. record1 shows a sub total of 21000 record4 shows a sub total of 5000 therefore it should show the calculation of 26000 in the target field. records 2 & 3 are not in the found set and should be ignored. Thanks in advance for any help offered.

Want to figure out provision if the field contains various amounts , eg 850 kr = 0,10 % but if the score is 0 , the answer is ? it gives wrong summary if value is 0 its get ? and the result is row 1 ( *,1;) how to get ? to zero i have this now Case ( Omsättning per timme ≥850 ; Summa *,1; Omsättning per timme ≥ 800 ; Summa *,0807; Omsättning per timme ≥ 750 ; Summa *,0588; Omsättning per timme ≥ 700 ; Summa *,0338; Omsättning per timme ≥ 650 ; Summa *,005; Omsättning per timme ≥ 600; Summa *0; Omsättning per timme ≤ 0 and 600; Summa *0;0)

I'm trying to find a calculation to count the number of records with a certain value of a field. For instance if I have 5 records and three of those records have a field with a value of “completed”. I want to find a calculation that tells me there are 3 records with the word completed. I’ve spent hours trying to figure this (what seems to be probably something simple) out and can’t seem to find what I’m looking for.

Hi, I need a small help. I want to restrict ( Validate) one field as It should only accept two alpha followed by 6 numerics. Like EN123456 or DS654321 Can anyone let me know, how I will do this. Thanks in advance. Regards, Tusif

I have a question concerning getting a calculation to refresh. I have a calculation that counts the number of unique records in a database using this method that I found on the forums: TWO SUMMARY FIELDS, ONE CALCULATION, AND SORT METHOD If your version of FileMaker Pro does not support the ExecuteSQL function, you can accomplish the same results using a couple of summary fields, one calculation field and sorting your database. Start by defining a summary field called "TotalCount" which is a Count Of LocationField Next, create a calculation field called "FractionOf" with the following expression: This calculation gets the reciprocal of the Tota…

In our legacy system (currently FM 12), I set a global date field "Record Lock Date" which is the period close date from a financial perspective. Users cannot create timesheets with dates prior to the Record Lock Date (because they will have already been invoiced to clients at that point). Validation for Date field in Timesheet table looks like this with CAT being me: If (Get ( AccountName ) = "CAT" ; 1 ; (Date > Globals::Record Lock Date) ) Monthly, when we move to a new billing period, I close the file on the server and log in directly (without sharing), and I manually update that Globals::Record Lock Date field. I'm designing a new system in FM 14, an…

Hi, I am a newbie to FM, pease excuse my ignorance! I have a table of data; REGION | SALES A | 500 A | 400 A | 300 B | 600 B | 700 C | 300 C | 200 I am tying to produce a report that will show REGION | TOTAL AMOUNT A | 1,200 B | 1,300 C | 500 I have attached my meagre efforts but as you see, the report I have built doe snot produce the desired result. I have added a TOTAL SALES field to my table but I cannot get the result that I desire. What have I done wrong? Test.fmp12

Howdy, all: I'm using this ApplesScript calc to create a folder on the desktop of my Mac: "set hfs_desktop to (path to desktop folder) as string¶ set hfs_subfolder to hfs_desktop & \"" & DateFormatter__CFpub ( Get ( CurrentDate ) ; "yyyy-mm-dd" ) & " Job Chop-Chop Backup\"¶ set posix_subfolder to quoted form of posix path of hfs_subfolder¶ do shell script \"mkdir -p \" & posix_subfolder" ...but I'm having a devil of a time trying to amend its syntax so the folder name is changed from 2016-03-11 Job Chop-Chop Backup to Job Chop-Chop Backup 2016-03-11. Admittedly, I'm a little confused with the escapes for the quotation m…

Hello We have a set of databases we've been running without incident for years, though many FMP upgrades - all served up by FMP Server. With 14 - suddenly I have instances of numeric fields whose display I cannot control - the Inspector seems to have no effect on the number of decimal places, nor can I control percent or currency notation. Context We are a consultancy and the file is our timecard, with daily hour totals and reporting ratio of billable and unbillable time. Calcs A summary field to total the hours, and the ratio calculated by dividing that total summary field by another summary field totaling the unbillable hours. The summary f…

Hello Everyone, Im using the Inventory Starter Solution for my inventory needs. but I am having a trouble in changing the "Units in Hand" Field. I want that if I enter a value to the "Units In" in the Stock Transaction Portal it will not add in the calculation on my "Units on Hand" ; and I want also that If I enter a value to the "Units Out" field in the Stock Transaction Portal it will automatically enter the remaining total quantity in my "Units In" and it will also update my "Quantity on Hand" field. is this possible to achieved? In the attached file If I received 15 Stocks and 3 is out i want that in the "Units on Hand" will show 9 not 21. Ple…

Hi, I'm new to the forums, and I have a question that I hope someone can help me with. I'm sure its rather basic, but I'm pulling my hair out! I have a database with 2 related tables. In one layout, I would like to be able to choose a record from another table, and three fields from the chosen record appear as non-editable text in the layout. For example, I have 10No records in the Table ‘Resources’. Each record has a 4 fields: ‘Reference No’, ‘material’ ‘finish’ and ‘element’. In the Table ‘Schedule’, I have a layout where I want to create a record where I can choose one of the 10No record from the Table ‘Resources’ by selecting the record’s ‘Referen…

Hi Forum, Please help me solve a calculation for this problem, i have a table with various records, i would like to implement a field called Order_Check that would display a "1" if the table field Order_Num begins with "PT00" if otherwise it must display "0". This field Order_Num for invoice records appear to be either empty, has value ID that begins with PT00* or just have some random values, i tried the calculation: IF(Order_Number = "PT00*", 1, 0) Thank you for assisting, Miss-Amen!

Hello, i have a custom dialog, i need to put 4 character and then click ok could i make the custom dialog box validate automatically if i put 4 characters ? Thanks you

Trying to figure out how to handle this. Have 4 Tables.. Order, Lineitems, Products, and PriceMatrix. Products and PriceMatrix are related via SKU. Products has a Portal to PriceMatrix to show all the unit price based on QTY. Example Up To Quantity 100 $1.00 200 $0.90 300 $0.85 The thought is if a customer buys 90 of this item they will pay $1.00 x90=$90 If they buy 205 it would be $0.85 x 205=$174.25 because 205 is over the 200 qty they get the pricing of the 300. I'm needing a way write a calculation that can do the math based on the QTY ordered. Thanks

I'm trying to set up a button bar that displays the current months names based on the following Current month Last month Next Month I can use Month (Get(current date)) - 1 (for example) but I get a number of 2 for february or 1 for january If I use Monthname (Get (Currentdate) - 1 my result is (-1) Thanks in advance, thanks!

Hello, I have this Calculation script: Address_Mailing1 & " " & Address_Mailing2 & " " & "¶" & City_Mailing & ", " & " " & State_Mailing & " " & "¶" & Country_Mailing & " " & ZipCode_Mailing which is successfully combining multiple address fields into one field so I can cut/paste out of FM easily, except for one problem. When I use this script and enter only one zip code number (3 for example) I get this result: 411 Teal Street, No. 1773 Green Cove Springs, Florida United States of America 3 But with the same script and a full zip code (33316 for example) I get this result: …

I have data validation on a field in the main database, however when I edit that field using a separation model, the validation does not seem to work, it allows invalid values. I can see in the help topic that ODBC data sources do not bring across data validation from the data source. Is this also the case if the data source is FMP? If so, do I just need a script trigger in the separation file to do it's own validation?

Hey all, just curious if any of you have seen a calculation or a custom function to convert a DNA sequence to an amino acid sequence or vice versa. I figure it wouldn't be too terribly difficult to make one myself, but if somebody has already conceived of this thing it would certainly be a time saver. Thanks.

I am using the following: FilterValues ( Daily Shift Report::Primary Locations List No Recent Visit ; Daily Shift Report::Primary Locations List All Locations No Recent Visit ) It works fine but I also want to reverse it. In other words, instead of returning the ones on the list I want it to return the ones not on the list.Thanks.

I have a phone log table thats connected to my main database, this phone log is displayed and sorted by date with the most recent date at the top. I'm trying to create a new Main table layout with a portal to the phone log table that sorts the main table by the Most recent date of the Phone Log Table. I can sort by date of the phone log table but it pulls different dates and not the most recent. Any thoughts on this? Many thanks in advance! Matt

Consider a standard timesheet application with a parent "timesheet" table and a related "timesheet details" table connected with a one-to-many relationship. On a layout based on the timesheet table I have a portal that outlines the details for a two week pay period. One of the elements on the timesheet details is what type of entry is it (i.e. regular hours, support hours, etc.). I would like to be able to summarize the hours of each of those entries on the timesheet layout. I have been able to create a calculated field that does sum the total hours well and I thought if I made the calculation something like this: If ( Timesheet_Details::Type = "Regular" ; Sum ( Time…

I speak with a translator, If anyone can help me I have a question Can you we used a fusion or other variable substitution in a formula? Thank you I did some test but it does not work following my example Shmuel Definir ( [ machaine = " f1" ] ; SupprimerEspace ( Substituer ( Minuscule ( f1 ) ; [ "Bonjour" ; "<<l::A1>>" ] ; ["hola" ; "salut"" ] ; ["gracias" ; "merci"] ; ["adios" ; "bye-bye"" ] ; )) )

Hi guys I've created a small database to track my hours as an independent contractor in the entertainment industry. I have everything working great and want to implement one more feature into my time calculations, which I think is a little more complicated. I have been doing this manually so far and then adjusting my "Total Pay" with an overwrite field. Currently I'm tracking time as decimals (i.e. 1:15PM is 13.25, etc). In my industry we have what are called meal penalties which an employee can start incurring if they haven't been broken for lunch within 6 hours and again if they're not broken for a second lunch 6 hours after the first lunch ended. 1 (one) meal…

I have a field that receives a bunch of first names from a portal, thanks to the List function. The result might look like this: Alice, Bill, Chuck However, I need for the list to read: Alice, Bill, and Chuck I need to find the last comma in the text and add an "and" to it. I have been messing around with the Position function stuffed inside of a Substitute function, but I can't get it right. Can someone help? FMPA14 Rick A. Pleasanton CA

I am going to insert a file. When the folder opens up, What i want is the filepath based on the text on the field i entered. Sorry my grammar i hope you understand.

I have several databases that have more than one page. If the number of pages grow, I'd like to be able to identify how many pages are in the set and so indicate in the footer. For example: Page 2 of 10 Page 1 of 20 Page 12 of 15 and so on. How can this function be created? Thanks...