Calculation Engine (Define Fields)

I have a table with over 100,000 records and I'm trying to show the top 5 accounts by premium for each month in a portal. One way of doing this is to create a portal and then filter the portals by premium size (so I get the top 5 at the top of the portal) and then filter or show only the top 5 records I'm just worried that with the amount of data, every time this portal loads it will take significant calculations. Is there a better way to store this or create another table to keep the usability quick for my users? Thank you in advance

Hi I have this fonction in a calculation field : "If ( Get ( TotalRecordCount )-Get ( RecordNumber ) >0 ; "perform record: present - (record +1)")" what I need is : if the current record is not the last then..... take the present value - next record value I also tried like this but I get "?" on every new record created. If I modify the calculation script then it run the calculation and everything work but for only old records, it s like the calculation run only one time on the creation. If ( Get ( TotalRecordCount )-Get ( RecordNumber ) >0 ; GetNthRecord ( FIELD ; Get ( RecordNumber )+1 )-GetNthRecord ( FIELD ; Get ( RecordNumber ) )) …

I'm looking for a formula that calculates the date of the second Tuesday of any month, based on the month number. Any suggestions.

I have a table with a field that's called Premium and another field that summarizes the premium field. In the premium field we have premium amounts and negative premium amounts. Is there a way to create a calculation field that only counts the positives?

I have the following Calculation field in my database: "<tr><td>" & Quantity & "</td>" & If (SELL_Speed1 > "50000";"morethan50000"; "<td onclick=\"\" class=\"snipcart-add-item\"" & ¶ & "data-item-name=\"" & Product_Name & "\"" & ¶ & "data-item-id=\"" & id_SPEED1 & "\"" & ¶ & "data-item-price=\"" & SELL_Speed1 & "\"" & ¶ & "data-item-taxable=\"false\"" & ¶ & "data-item-description=\"" & "Qty: " & Quantity & " " & Product_Description & "\"" & ¶ & "data-item-url=\"https://www.reliaprint.co.uk/prices/" & id_FOLDER & "/" & …

(I know this has been covered multiple times here but after after performing multiple searches I can't find the answer, so my apologies for subject repetition.) Happy holiday season, all. This is pretty basic but I'm blowing it calculation-wise: I have a report that has two, summarized sections, the first by Test Code (TestCode__lxn, a number field) and the second by name (Name_LF__lct, a calculated concatenation of the Name_First__lxt and Name_Last__lxt text fields.) What I want is a (distinct) count of students per test code (FieldName: Count) in the summarized Test Code section. I first tried Name_LF__lxs--a summary field set to count Name_LF__lct--…

Hi everyone, thank you in advance for your help and support! I have a field called Total Premium and over 10,000 customer records in my database. I use a card layout as a "Dashboard/Summary" to show sales and other data for the month. Currently my script is as follows, new layout/card, enter find mode, find records with dates for this month. perform find. At this point for about 12-15 seconds filemaker is spinning in the background summarizing the Summary:Total Premium Field and then the data is displayed perfectly. Is there a better/quick way to calculate and store this data on the fly, perhaps on the server or in a table that makes this summary …

Gents, need your help on the following issue: I need to track my expanses. There are 4 tables: Products, PriceChanges, Transactions. The problem: I need to make a script which will make calculations in transactions tab, accounting for the latest price change. Example: 01 jan17 I bought one bottle of wine for $1 03 jan 17 I bought the same bottle of wine for $1,5. So the latest price is $1.5 In Products table there is one record which is wine. In PriceChanges table there are two records: $1 and $1.5 I need that Transactions table automatically take corresponding wine prices while adding new or past records.

I have attached a sample database as this is hard for me to explain. But I will give it a go. Any help, I would be very, very grateful as I can not figure this one out. Outline: Two Table-Product and Boxes Primary Key:Boxes:pk_BoxName Foreign Key: Product: fk_BoxName and fk_AltBoxName Relationship: What I am trying to achieve--In the product table I am trying to fill in the Width, Height, Length by way of a calculation. The catch or issue I am having--Case( fk_BoxName "" then use fk_BoxName to get box Height; fk_AltBoxName "" then use fk_AltBoxName to get box Height; "Error") The issue is I need to check fk_BoxName and fk_AltBoxNam…

First, I am sorry that this is most definitely a repeated topic and probably pretty common, however, I could not find it in this forum or on the internet and had to post the question. I am stuck with a mailing list that has 13,000 names set up with "Last Name, First Name," which obviously needs to become First Name (Space) Last name with no comma. This name field contains all of the headaches associated with people's names. Examples as follows: Smith, Bob Smith Jr., Bob Smith-Wesson Jr., Bob Smith Wesson IV, Bob Peter The good news, is that the list is solid with the respect that the there is only ONE comma. I need to simply rever…

I love GetNthRecord, but it does not work in the same is a portal view as it does in the list view. In a related portal I want to hide fieldA when fieldA is the same result in the portal record above. Is there a way to do this?

I got a very helpful reply here once regarding calculating shipping costs using this formula: Case ( BillableWeight ≤ 1 ; 8.99 ; BillableWeight ≤ 10 ; 8.99 + 0.5 * BillableWeight ; 9.99 + 0.4 * BillableWeight ) Not however, the terms have changed and I need to evaluate charges that are BETWEEN certain values.... For instance: ≤ 1 ; 8,99 ; is no longer true... Now it's .01 - .69 = 4.99 etc... How would I go about modifying the calculation to evaluate between two values instead of less than a value?

I'm struggling... does anyone have a quick/easy way to format the below as a standard date? Input: 2018-11-14T11:31:26.113 I would like it to format to: 11/14/2018 so I can use filemaker to sort by date, etc. Any help would be greatly appreciated!

I’m using the following formula to count the number of people in a compound name. If[WordCount(name)>2,2,1]. It gives the following results: Name Result Smith, Jane 1 Smith, Jane and Joe 2 Smith, Jane Agnes 2 The last result is incorrect. It should be 1 since Jane Agnes is a first name. What would be a correct formula to use?

I'm trying to create a calculation field that calculates time and days it took to quote. I have record created date and record created time fields and then record quoted date and record quoted time fields. I'm hoping to have a field that shows average time to quote 3 days, 34 minutes Anyone have experience on something like this? Thank you

Hi All, Please could you assist? I have a layout with just a container field to view PDF. when opened, it displays the PDF logo and the name of my file but the actual PDF does not display. If I insert a picture it displays fine, just not the PDF Have it set up in browser mode any suggestions?

Hey... I have a database with 60,000 plus names and addresses. I need to mail to 40,000 exactly. I can do this manually...but I was wondering if there was a way to have FM extract 40,00 exact addresses randomly. Thanks in advance. Mike

Hey everyone! I am trying to pull out multiple alphanumeric serial numbers out of a giant text field. The serial number could show up in any number of places, but has a fixed format. I need the calculation to pull all occurrences of the serial number and basically put them in a list, to which I will then be able to create a join field from. Honestly, I have no idea where to even start. The serial number is always going to be 2 Letters and 3 numbers - example: AB123 Source (Text Field): Big and boisertious rock track, but time selling point, Track is a version of AC101, KD205, GR771. Also check FE454/GG123. Result: AC101 …

Hello, I've been using this Excel formula: =DATEDIF(DATE(MID(A2;LEN(A2)-6;2);MID(A2;LEN(A2)-8;2);LEFT(A2;LEN(A2)-9));TODAY();"y") It has been used for calculating the age of a person from a social sec. number with 11 digits. The first six digits from the left is day of birth, day/month/year. Is it possible to get the same calculation in Filemaker?

I encountered an issue today that I'm reflecting on a bit. The DB I manage has been around for a long time now and has been in hands of various developers. The original DB started as a FM Starting Point and was changed along the way until the guy left the company. Another developer started redeveloping the solution copying various structures from the original solution, and essentially making a bigger mess. Then came along me, fresh, never played with FM before and took it on board (this is now some 5 years ago, if only I had the time to write a book what I learned). This week I started developing a Production Dashboard where production staff can see the various produ…

Hi, What is the possible solution for this, See my attached sample. As you can see i selected Doe and Smith from the checkbox, But the result is John and James. It should be Doe and Smith also. Sorry for my bad grammaring i hope you understand. Example.fmp12

I've set up an email feature that allows the user to store 'boilerplate' text to be inserted at the beginning and end of a message when a Send email script is triggered. It's working fine on my desktop version of the file. However, my client is finding that when she changes the boilerplate text in those global fields and closes the file, when she reopens the file, the changes have not been retained. Simple text fields, global storage. My client is using the files via Filemaker Server 16. Can anyone suggest why this would be happening?

I have 3 fields timestamp start, timestamp end and timestamp list ( list of timestamp from start and end ). My problem is when searching a timestamp from the list, It always shows result even it is not included on the list. Ex the timestamplist is. 9/25/2018 9:00 AM 9/25/2018 10:00 AM 9/25/2018 11:00 AM 9/25/2018 12:00 PM 9/25/2018 1:00 PM 9/25/2018 2:00 PM 9/25/2018 3:00 PM 9/25/2018 4:00 PM 9/25/2018 5:00 PM 9/25/2018 6:00 PM 9/25/2018 7:00 PM 9/25/2018 8:00 PM 9/25/2018 9:00 PM 9/25/2018 10:00 PM 9/25/2018 11:00 PM 9/26/2018 12:00 AM 9/26/2018 1:00 AM but if search 9/26/2018 3:00 AM it always shows a result.

I have a field I am trying to clean up that has had inconsistent data-entry practices over time. One area of variation is capitalization. When I popped open the index I saw things entries like things like: Drama / comedy / Silent cinema / Silent Cinema / silent cinema . . . So, I used TextStyleAdd(FIELD ; Titlecase) to replace all the values with consistently capitalized words. Worked great. Then i went to the field's Options panel and turned OFF indexing, saved changes, shut down the database, reopened, and then went back and set indexing to 'Minimal' since I'm just interested in the Values, not the individual words. I saved, quit the database …

Hello, Not sure if this is the correct area to ask but I've been racking my brain and am not getting anywhere. I have an Event table (Fire Incidents) and related to the ID of the event you can create attendance records in the attendance table for the members responding to the fire emergency. I do the attendance adding using a portal on the event record and add the members by capturing their Personnel record ID and then setting the last row of the attendance portal using their personnel record ID and the event ID. That has been working great and there is no issues there. I wanted to add a new validation that looks to see if that Personnel record ID AND E…

Hi, I have a customer who would like to pay a commission on the 1st invoice by customer id but on all other invoices by customer id he does not want to pay a commission. I can build the relationship to show the invoices ... but since the invoice have been in play for some time now, the 1st invoice has already happened and I see many invoice by customer id; January, February, March etc ... Is there a way to write a calculation that would place a "1" in a field for the 1st invoice by a customer Id and in all other invoices by customer id place a "0", is this possibel ? This would allow me to build the relationship to the "1" and only see "1st" invoices …

I have a Text field in which a client has entered multiple keywords separated by semicolons, like so: Animation; Experimental; Comedy I have created a sub-table to hold individual keywords so they can be better manipulated in various ways. So I need to run a script to break out the three values and create 3 sub-records for the above examples. Fortunately, the records are very consistent in their use of semicolons and a space to separate the values. (And semicolons are not used for any other purpose within the entries). I assume I'll create a looping script to perform an iterative process on each record, using the semicolons as markers, right? I'd could …

Hello, I have a need to convert a timestamp into a very specific date/time format. I need to convert a timestamp into a single string of numbers in this format MMDDYYYHHMM. Month(2digits) Day(2digitis) Year(4digits) Hour(2digits/24hr clock) Minutes(2digits) [No separator characters in between any of the groups] I have no idea where to start on this. Any ideas on where to begin? Field name is :TimeRecieved Thanks Chris

(I'm sure this calc is here somewhere in the forum but I couldn't find it, so my apologies.) I have a solution that captures test codes and the dates students take those tests, but what I want to do is flag the earliest date a student took a specific test with the phrase, "First Attempt". I included a set of sample records for a specific student with this post. As you can see, there are three instances of TestCode 0061 but I want the flag to appear in the earliest-dated record, 07-25-2009. I figure it will require a separate T.O.- keyed on StudentID and TestCode--and that somehow it's a comparison of one-to-many, but that's about as far as I got; I tried us…

Hello All, Its been 2 months since I started using Filemaker. Now this is my first post. In the attached file Im trying to make something like Bill of Materials. I have a Product Table, Stock table and Components Table. The relationship is Below My Problem is when I update my Stock table using a portal in my Popover it always shows the balance to my Stock Portal. What I want is It will show the Individual Stock as I choose each Product. Please help me correct my mistake & Please see attached file for reference. Thank you so much in Advance. Product Allocation.fmp12

I'm looking for a calculation that determines the number of tuesdays in a year.

I'm looking for some help with a calculation field for final keywords that needs to substitute keywords from a related table. I have this working fine using Substitute and GetNthRecord. However, I'm missing some obvious logic to do this correctly. I currently have something like this: Substitute ( text ; [ GetNthRecord ( searchString ; 1 ) & "," ; GetNthRecord ( replaceString ; 1 ) & "," ] ; [ GetNthRecord ( searchString ; 2 ) & "," ; GetNthRecord ( replaceString ; 2 ) & "," ] ; [ GetNthRecord ( searchString ; 3 ) & "," ; GetNthRecord ( replaceString ; 3 ) & "," ] ) This works fine, but I have over 100 …

I'm racking my brain with this one. I put together a summarizing report (sub-summarized by date range then test code (i.e., 5002, 5003, 5004, 5005) but where I'm getting hung up is in the last two columns, % Above Passing (score) and % Below Passing (score). As you can see, the numbers repeat themselves for each date range sub-summary...and for some strange reason, the 5003 averages are identical to the 5002 averages, but they shouldn't be. To calculate the % Above Passing (score) and % Below Passing (score) numbers, respectively, are these calculated number fields: PercentAbovePassAvg: ( ( AvgPass_ScoreAbovePassing__lxs - TEST_CODE::MinPassingScore__lxn ) …

Is there a way to do the opposite of the filter function? For example: Field 1 contains the text “dog cat giraffe crocodile” Field 2 contains the text “dog giraffe” I would like field 3 to be a calculated value which displays “cat crocodile” ie it removes from field 1 whatever words are typed into field 2.

I always get hung up trying to create a working calculation when parse text from the right side of the field. (To my credit at least I'm consistent.) I have a text field that concatenates values from three different fields into one, e.g., Lewiston|ID|US, where left of the first pipe is the city, the two letters between the pipes is the state, and the last to characters are the country. State and country are always two characters long whereas city can be any length. Extracting the country's text is easy: Right ( textfield ) ; 2 ) ...it's parsing the city and state text where I'm flummoxed. I'm pretty sure the Length and Position commands come into play but I…

I am updating a database where the legacy solution has separate Fields for Date and Time, which were set via "auto enter" options. In the new solution I'm thinking perhaps I should replace them with a single "Timestamp" field, but wondering whether there are reasons not to . . . so 1) Can I simply concatenate the legacy data in the two fields for the existing records, with a space between them? Will they "behave" like the fresh records where the full timestamp is auto-entered? 2) Is there a way to display *only* the date, or *only* the time when placing a 'Timestamp' field on a layout? I'm not seeing a way to do this . . . so is it best to just have separ…

Hello All, I have two tables, Material table and Material Transaction table In my material table I have the Quantity on Hand a Calculation Field (Sum ( Material Transactions::Units In | Out )) and on my Material Transaction I have the following Fields Quantity Dispensed ; Quantity Returned ; Type (Auto Enter Data "In") ; Units In | Out which is a calculation field(If ( Type = "Out" ; 0 - Quantity Returned ; Quantity Returned ) In my portal from Material Transaction I have a button bar (In | Out) which there is a single step script that set the type into In or Out which is not working and my Quantity on Hand and my Quantity Returned is not matchin…

Hi guys, I’m brand new working with FM and would like your help with a calculation if I may. I want a calculation to display an estimated number of paint tins needed to paint a boat based on it’s length and width. With this information I would also like the cost of the estimated paint. I'm assuming IF can not be used as there are too many variables, can CASE? With the boats width, I've got anything over 7 classed as wide, so I've been using Jobs::BoatWidth > 7 to define narrow and wide (only two classes of widths). I'm using the table below to give our customers a rough idea at the moment but would like information to be displayed in FM specific to boats len…

Hi there. I have two tables. One for accounts payable and a second one for monthly ongoing payables. The last one holds 10 records which are recurring expenses. I am struggling to transfer the recurring expenses to the accounts payable table. What would be the best approach to deal with this issue?

I want to create a function that works like "Countif" in Excel. For example, to count only the text value "7" in a series of cells in Excel I use: =COUNTIF(A2:Z2,7) which ignores any other text value and counts the occurrences of the value "7". In FMP how do I get the same result?

We have exemptions for students based on various criteria. I need calculations to determine if the student is exempt or not depending on the various criteria. It gets kind of complicated. I'm not very good a figuring out complex calculations. Here is the criteria: average 95 or above, maximum of 3 absences, maximum of 3 tardies average 90-94, maximum of 2 absences, maximum of 2 tardies average 70-89, maximum of 1 absence, maximum of 1 tardy any violation of conduct is an automatic "not exempt" (I have fields for ISS, U, and Academic Integrity) Here is what I tried to do for 95 and above: Case ( P1_Rounded ≥ 95 and P1_Absence ≥ 3 and P1_Tard…

I need help with this calculation please. I need to designate which counselor the student has according to the students last name. I have a calculation field that only looks at the left 2 letters of the last name. That is called "left last name". Then I created a field and used this calculation... but it only is calculating the first part(grades 9-12) and not the second (grades 6-8) Case(Grade Level="09" or Grade Level="10" or Grade Level="11" or Grade Level="12" or Grade Level="9"; Case( left last name>="A" and left last name<="E"; "Counselor 1"; left last name>="F" and left last name<="Le"; "Counselor 2"; left last name&gt…

Hello guys, good evening! Currently I'm making a solution "Ageing of Accounts" to a certain Government agency. I'm still in the part of an account's computation to get the ending balance of every account. But I have a problem in putting some Penalties. (See the picture below) These are situations that needs to be Penalties. Penalty will be posted if: 1. If the Bill of a certain account exceeded the Due Date provided. 2. If the person has not paid and it exceeded the Due Date. 3. If there is a remaining balance of a certain account and it has not been paid yet. I have attached a sample, for you to test and help me with this solution that I'm curr…

(sorry for repeating the entry; i didn't know where to post it!I Good afternoon! I'm having some trouble with something I'm trying to do in Filemaker. I need to perform a calculation by using an auxiliar table, which is unrelated to the one in which I want the calculation field to be. That table shows the available doses in miligrams and the cost associated to each one (cost is not proportional to the dose). The problem is that I don’t know how to retrieve the cost field depending on the dose we choose; let me explain: if the doctor prescribes the 75 mg dose, I want to perform the calculation by using the proper cost (177.6237$) from the auxiliar table. I’v…

I found a thread regarding this from 11 years ago, but couldn't find anything else so thought it may be best to check if the newer versions of FM have a solution to this problem. Is there any function or custom function that would allow me to check if the value in a field is numeric? I have a field that is supposed to be only numeric (primarily only positive integers) however occasionally there are entries like '1 + 4' or '10 + 2'. I'd like to enter the contents of this field into another field, but only with the entries that are numeric only. Any thoughts? Really appreciate any help.

Hello guys, good evening. Okay here's the story. When the Payment Date is exceeded on Due Date, there is a 10% penalty charge. But I got a problem here, I want to put a Penalty amount beside the Balance column. How can I get the Penalty charge there beside the Balance? (See the image below) Thanks and God bless!

Hello, Maybe someone can help me with a report. As you can see in the following image, I have 2 issues. 1- I want to get all the percentages that exist from a certain user in only one line. Its creating multiple lines for the same person and then each one with a diferent percentage. I want one line for the name, then a line for the percentage, and every line corresponding to that percentage. Then another percentage, and all the values of that percentage. and only then it goes to the next user. 2- I created a field that is a calculation of the amount * percentage. But its always nil. The numbers are set and saved in the database but I cant get a simple…

Hello guys, good afternoon. Just wanna ask if where does my apps or the path of my apps put on Filemaker Go? What folder should I access in the iPad? Thanks!

Hello guys, good afternoon. I really need your help guys. I couldn't solve this problem. Currently I'm making a solution about Meter Reading Schedule. My problem is, I couldn't skip these dates November 1, December 25, and January 1. What I want is to skip those dates. My Reading Date column works great. I don't have problem of it. The problem is my Due Date and Disconnection Date column. Here's the sample calculation of my Due Date: Let ( [ $itself = ReadingDate14 + 10; $investigation = Case ( DayName ( $itself ) = "Monday"; $itself; DayName ( $itself ) = "Tuesday"; $itself; DayName ( $itself ) = "Wednesday"; $itself; DayName ( $its…

Hi , the following is very simple just can't to the bottom of it i have a field which has a value of a color. i want to get the found count of the record but it should only count every color once meaning record 1 - red record 2 - green record 3 - blue record 4 - green my result should be 3 i would appreciate if someone could help me out on this.

I have a data file that I get from our sales system; I have no control over this data, and it looks like this (skipping the extraneous fields) in one big table: SaleDate, SaleTime, ProductName, Qty, NetRevenue, CustomerInfo, plus lots of other minor data I get one row for each ProductName, even if a customer purchased multiple products on one transaction. As a result, there could be many rows for the same SaleDate and SaleTime. What I need to do is create an output that contains one row for each date, with all quantity and revenue for each product on that date. Assuming we have two products, FOO and BAR, I'm looking for something like this as the final output: …

hi i have a table with 4 fields field 1 = number result 1 field 2 =number result 2 field 3= percentage result field 4 = calculation by taking in whichever is lower field 1 or 2 and multiplying by field 3 the question now is how do i script the calculation it should pick up which ever is lower but ignore if field is empty. i hope i am clear , i would appreciate if someone could help me out with this calculation.

I need to write a script that will examine a field (A) in a set of records. The desired result is to increment a counter (B) based on the contents of A. A is always either empty or 1. I need a script that will examine the contents of A and increase the contents of B by 1 (if A = 1), or decrease B (if A is empty) There will always be 5 records to be examined, thus the result of the calculation will be somewhere in the range of +5 to -5. Please forgive an old Fortran programmer, but I just can't seem to find the answer in any of the FMP books I have. Any help will be greatly appreciated,

Hello FM Mavens, I am likely missing something. I have a script step that calculates the dimensions of an image in a container field. There are many such fields so the script should work with any arbitrary one. I get the name of the field by using script parameter Get(ActiveFieldName). Let's say I have a container field "cont1". The script is triggered by entering the field. I am trying to calculate the height of the image by using GetContainerAttribute (Get ( LayoutTableName ) & "::" & Get ( ScriptParameter ); "height" ) The results are question marks - I am not sure why. If I explicitly identify the fields then the expression…

hello, I have a field in my database, the field is set as currency. Now we are having a problem when insert the prices: 1. we insert the price with the dot, as the image shows 2. when we click outside the field we get the number in thousands (not as we introduce it), as the image shows. 3. then we go to the field and change the dot for a comma, as the image shows. 4. then we click outside the field and we get the right value, ( filemaker replace the comma for a dot), as the image shows please let me know how to fix this? right now we have to introduce the prices with commas to get the right value. thank you ve…

Hi , i have a portal of outstanding tasks , the portal consists of all tasks and is filtered to display only required that are still outstanding (2 rules) i would like to get the foundcount of this in a calculation , how would i get this ?

Am I righting in thinking I can use a case statement to evaluate two fields and return an answer into a third field ie: Field A has the number 27 in it. Field B has the number 32 in it. Could the case statement look at the difference and put the difference in Field 3.The difference being 5? I’m also trying to get this to do the math on data entry, so the figure in Field A is 27 but when the user enters the new figure in Field B (i.e 32), it looks at that difference between A and B, copies the difference of 5 to Field 3 and then also copies the figure 32 from Field B into Field A thereby updating Field A to the new value. Help, suggestions or thoughts will be g…

I've got this trouble that I hope someone can lead me down the right row of thinking on this.. I've got a simple sales order document that has a related table for line items.. For each line item I've got a Filed being used as a Boolean.. (0 or 1). For this document I need to know per order if a mixtures of 0 or 1 line items are in the related table. .. They don't care if they are 0's or all 1's.. Thanks

Hi, I'm trying to implement NightWing's Duplicate Hierarchy v2 technique in my solution. The file (my solution ) has been in use for years, but does not appear to be corrupt. But the technique relies on the RelationInfo function, and it does not appear to work at all in my file, although it does in other files. Any clue as to what could cause this? I've taken screen shots of the Monitor window showing that it is passing correct parameters, and part of the graph showing the table & its relations. Thanks for any help!

I just have a quick syntax question. I have two tables, Contacts and Work. In Contacts, I keep name and email information. In Work, I keep records of projects, where sometimes 4 or more people are involved. Contacts::Name is related to Work::Name. I have a portal in Work displaying email addresses. Work::Name is a repeating field, and the Work::(Contacts::Email) portal is also repeating. For whatever reason, the Email keeps displaying mail in reverse. As such, I want to make Work::NameSwap, in which I reverse the order of the entries, as I need a quick fix for now while I work out the real problem. For that, I just need NameSwap[1] to have the same value as Name[2]. …

Hi, I’d like to show days since last race for a horse where the first race returns 0. I played around with ‘last’ and ‘GetNthRecord’ but something went wrong. I had some problems with races where the horse was scratched, but I can live with that. Thanks, Paul

I would like to have options to enter either fields (selling price or margin) and the other should be automatically calculated for instance: Cost Price = 100 (Available) Sell Price = Calculation field or ?? Input amount Margin = Calculation field or ?? Input percentage If i put 160 on sell price it should automatically put 37.5% in margin or if i put 37.5% in margin field it should automatically fill sell price for 160. File uploaded Login name: admin No Password: Thank you in advanced. TEST.fmp12

HI, In My DB- Table ( Product) have a Container field. i want Export Container Field. & save in The create folder. i have write these script:- In this script Folder Create successfully. & also Image Export Sucessfully On Desktop. But I want export Image in The newly created folder. . can any one help me to release this. thanks in advance Austi

G'morning, all: Please refer to the attachment. What I'd like to do is have the 1st Attempt (text) field auto-calculate whether a record's (Test) Code has been recorded with an earlier date; "No" if it has (e.g., there's an existing record for Code 5004 taken on Test Date 01-20-2017 so the record with it being taken on 01-08-2018 will have its 1st Attempt field set to "No"); "Yes" if it's the first time it's taken (e.g., Code 5002...and subsequent dates with Code 5002 would be set as "No".) I know I could script this by sorting by Test Date then Code, then comparing the existing record with a previous one...which would then set the 1st Attempt to the appropriate…

Is there a setting somewhere I can change so that when I create a new field in the Manage Database view, it drops immediately below the currently selected field? At the moment it drops to the very bottom of the list and dragging it up is a time-consuming pain! Thanks for any assistance FMP15

I am trying to design a recursive field that will give me an IRR of an investment. To calculate IRR, I have to understand recursion. As a guide, I downloaded this very good example.... but do not understand it, Let( $i = $i + 1 ; Middle( "abcdefghkjilmnopqrstuvwxyz" ; $i ; 1 ) & If ( $i < EndLoop ; ¶ & Test ; Let( $i = "" ; "" ) ) ) Can someone explain to me was "Test" does? I feel silly... but can find no documentation on it. And what does the "Let( $i = "" ; "" )" do? Thanks, #1 Solved... Test is the field name... hence the recursion

Hello Forum. I'm having trouble trying to figure out how to make some volume calculations in FileMaker. I seemed to have figured out the first 5, but I'm stuck on a Horizontal Capsule, and a Vertical Capsule. My goal was to make one long calculation for volume branching for each type of cylinder. But as you'll see it's getting rather confusing, so maybe in the end I'll just break them out and match calculation to volume-type measured. I attached a file with some helper text-formula and images. I figured someone who is super sharp in math/geometry can help. Hopefully this is challenging enough to get @commentout of retirement :). I suspect Jeremy B…

I have an import field that has Id numbers that look like (TWC-1707 A , TWC-1707 B , BNC-123 , RWA-34567 A) I'm looking to remove the A,B,C from the Field and put it in another field along with the first part so a field "Id" will have TWC-1707 A , field "MasterId" will be TWC-1707 and field "partnumber" will be A. Is there a calculation to split the field after a space? this is importing from a 3rd party database that cannot be changed.

Hi, I am using this calculation in portal which calculates days in portal Date - (GetNthRecord ( Date ; Get ( RecordNumber ) - 1 ) ) how can i modify or do something else to avoid (?) in the first row. Sample file attached for any modification/help. Thanks for any help DateCalc.fmp12

Hi there I was wondering if it was possible to send a text to a phone. If so, what functions are involved to make this possible? Thanks for your tips

Hi I've got some calculation fields which use the ValueCount( FilterValues( List( Table::Field1); "ValueToSearch")) calculation to perform an Excel-like CountIf operation, but I'd like to have a calc field which can perform a value count based on the contents of 2 fields to allow some conditionality, ie count where Table::Field1 = "ValueToSearch" and Table::Field2 = "AnotherValue" How is the best method to do this? Cheers Nick

I have a database that we use to update our website inventory. A few years ago we began offering customized merchandise that gets dropshipped direct from suppliers. Suppliers give us data feed files with their inventory levels, pricing, etc. and this file manipulates the data. It takes our current web database, compares values and exports those products with their updated values. Importing the data and exporting used to take less than hour but now it takes several since the size of the web database has grown and the number of suppliers has grown. Everything is automated through scripts. In the main table (web database), the proposed quantities, pricing, leadtime, v…

I'd like to bump up some decimal places, not sure how to do this? Basically want .00, .10, .20, .30, .40, .50, .60 etc and .05, .15, .25, .35, .45 etc. 0.01 to 0.04 = 0.5 0.05 to 0.09 = 0.10 19.50 to 19.50 (no change) 19.25 to 19.30 19.26 to 19.30 19.01 to 19.05 19.02 to 19.05

I need to compare then return a result in a auto-enter number field (avoiding a calc field). Ideally this is built into a field and not scripted unless overly problematic. Contacts table > Role field (text) is a multi-word job description. Titles table > Title field (text) stores key word(s, sometimes more than 1 word ie: 'Tech Support'). Titles table > Rank field (number) stores a number value. Ultimately I need to rank a contact based on the job description against the key pairs in the Titles table. If the Title key word(s), an exact match of all words in the key, are matched in the job description it would return a rank number. There can be m…

Yes, Its been 4 days im trying to solve this and still didnt get it right. I illustrated the problem on attached picture. Basically in I9 cell i want to copy the value $25 on the left table, so i need to make function that find "adam" , pay "books", in period "1". And then on J9 cell it should be find "adam" , pay "books", in period "2". How to solve this in filemaker? i find out its very easy on excel. Please help Thank You

Hi, I have a customer who would like to to change all his date fields to display 2018 Feb 9 ( YYYY/MM/DD ) vs 2/9/2018 ( MM/DD/YYY ). All worked except to for the "DateTime" stamp option. Why !!!!!! If FileMaker manages its date and time by a numeric value 123456789 ... why would FileMaker care how I want to display. So I created two fields; one was just "DATE", the other "DATETIME STAMP". Date: 2/9/2018 custom formatted in ( Inspector ) to >> 2018 Feb 9 - Not a problem it displayed perfectly ( YYYY/MM/DD ). DateTime Stamp 2/9/2012 custom formatted in ( Inspector ) to >> 2018 Feb 9 - yet FileMaker ignores the custom format and di…

Hi, what is a general approach in FM for calculating values without storing them in database? Can I add values from 2 fileds and print a result?

I'm sure there is a simple solution to this problem, but I can't seem to figure it out! I have a custom app that allows the user to write letters amongst other things. Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this. Does anyone have any suggestions? Thanks in advance.

I have two tables: Contracts and Payroll. In the contracts table each record has a start and end date. I want to auto enter the pay period code based on these dates. Each record in the payroll table has a pay period start and end date, as well as a Pay run ID. The tables are related: Contract primary key in Contract table to Contract foreign key in Payroll table. I don't know if I should use If, Let, Look up?? I don't want to use Case, because then I'll have to change the calculation each year. I'd rather add 12 records to the payroll table for the 12 months each year. These are two calc's I've tried, each calculations return "1". If …

Help Please, I am trying to figure out how to extract field contents via a calculation as follows: My lookup field is ABCDEFG-01-12345.jpg I want to automaticaly extract only 12345 to be placed into another field? I tried the Calculation Right ( Sorce field; 5 ) but that returns me 5.jpg any adivise is most welcome. Thank you

I have a layout that includes two sub-summaries. The topmost includes a summary field called studentGradeCount, which counts the number of students in a given grade. The subordinate sub-summary contains applicationStatusCount, which is a summary field providing a count of applicationStatus. Adjacent to the status count, I would like to provide a percentage, indicating what percent of the students have applications at each status. Logically, it would be applicationStatusCount / studentGradeCount But FileMaker doesn't return anything when I attempt this. Any help would be appreciated. FMPA 16.0.3 macOS 10.13.3

Can anyone explain how this calculation: List ( PRSN::c_seventh ; Length ( PRSN::c_seventh ) ; PRSN::c_seventh = 5 ; GetAsNumber ( PRSN::c_seventh ) = 5 ) is returning this result 5 1 0 0 The "PRSN::c_seventh" field is an unstored calculation that I've triple checked returns a Number. This arose from a more complicated calc that was returning the wrong value from a list. Let ( [ _levels = List ( "Very Low" ; "Low" ; "Moderately Low" ; "Moderate" ; "Moderately High" ; "High" ; "Very High" ) ] ; _levels & ¶ & ¶ & PRSN::c_seventh & ¶ & ¶ & GetValue ( _levels ; …

Totally not sure if I've put this thread in the right topic category, as I'm not even sure how to go about this. First, I'd like to be very clear on the fact that, if possible, this will be done without scripting. I'm hoping for some kind of calculation. IDK. Anyway, I'm trying to copy or Calc one field from an older record to its newest match (using BatchNum field to match). The two fields are different fields (FormNum is the ID number field) (RefID is the the reference field for FormNum ). RefID would allow the user to find the last time the same arrangement of data (FormNum being its ID number) was used. It's meant as sort of a skipping stone effect, if that makes sens…

Hi Everyone, How do I change this timestamp ... 2017-08-12 08:24:55 -0500 to this type of date format ... 08-12-2017 ? Thanks for your help. :-)

When you have a calculation, what is the difference between the storage options "Indexing - None" and "Do not store calculation results - - recalculate when needed" To my mind the first one should equal the second one, but Indexing-None does still seem to store the old result.

So there's probably something simple, but do you guys know of a way to automate dates from one record to the next? Each record date needs to be seven days after the previous one, and I'd like for my client not to have to enter it manually. I tried doing Let ( Today = ( ReleaseDate ) ; Date ( Month ( Today ) ; Day ( Today ) + 7 ; Year ( Today ) ) ) and some others, but none appear to automatically fill in. Help!

I am building a custom database for a law firm. I am rapidly approaching 60 tables, as it records everything you could possibly imagine related to plaintiffs, defendants, judges, courtrooms, filing dates, home addresses, business addresses, injuries, settlements, etc. I was about done with the database, when they added a new wrinkle. For every single field in the database, they want to record the source of where the data came from. (Where did the first name come from? Where did the last name come from? Where did the address come from?) They want to be able to interact with this information during data entry, as well as having it show up on reports. …

Hi, how can I calculate the agreement end date when not all months have 30 days? In my calculation Months is a number field. Agreement start date + Months/30 Thanks in advance....

I have a number field that some data was imported into incorrectly. Some of the numbers are supposed to be negative numbers like -10 but instead they were imported as 10- How would I go about moving the - to the beginning of the field in front of the integers?

I have a field with various types of data in it. I only need to keep the first 21 characters and remove everything thing else in the field to the right of the first 21. I've looked at Trim and some various other functions but I just can't seem to figure out how to make it work.

Hi. I have a ton of calculations in my Fire Dept database yet I can't seem to get this simple one to return what i expect. I have a "Date of Hire" field with a year in it. I am trying to have a calculation field return a simple year. ie. 12 years. I had found the calculation here in the forum for the calculation result of XX Years, XX Months, XX Days but that doesn't work when the fire dept only enters a year of hire instead of a full date. Here is what I was trying to use. Thanks in advance for all your time! Chris

Hunting for Newbie Errors: I'm importing data (tens of thousands of records) as a text string that looks something like this: AaAa.4.100 I need this imported string divided into three fields (AaAa, 4, 100). There are plenty of text tools to do this. In fact, it's simple, provided each part is a known and fixed length. However, the imported data, while in a fairly narrow range, is not fixed. I need a more generalized approach. I'm wondering about the MiddleWords function. I understand what it does -- manipulates lists, which are normally delimited with a paragraph character. This function is exactly what I need if only it would accept "." rather …

Hello again, I have another question for my fire dept database. We have an EVENT REPORT layout and table that stores all the data from our fire calls. Linked to our ATTENDANCE table via a "kp" are attendance records for each firefighter. The chief of the department wants a time delay on their ability to sign in. Sign in was usually allowed as soon as the call was created and until the chief clicks the CLOSE REPORT button on the EVENT REPORT layout which changes a REPORTSTATUS field to "0". Then the attendance add button doesn't work for some users anymore. My question is how to I add in some script elements that would also not allow the member…

Hello, I have a fire dept database that has a field for "Date Of Hire", a field for "DateInactive" and then a calculation field to give the Years, Months, and Days of active service. The calculation seems to only run when one of those two fields are modified which doesn't update the other members years of service. Is there another way to have the Years Of Service calculated that wouldn't allow that field to become out of sync or what looks to be stuck because the calculation wont reevaluate until one of it's formula fields is modified. Below is the calculation for Years of Service that I have been using. Thanks in advance for your help. …

I'm running into an issue hiding a text object in a sub summary part when a summary field in the same part is empty or zero. I created a little demo file to show my issue. I resorted to using Get Summary, but that still doesn't work in my solution (but it's much more complicated report there). I'd appreciate feedback as to why I needed Get Summary. tia, Barbara summaryhide.fmp12

Is there a way within filemaker to limit the decimal places in a number field to 0? I'm trying to create a field where in if a user enters 100.4 it will set the value to 100 and not allow any decimal places. I see you can do this in the inspector but I don't think it actually changes the data within the field. Thanks

Hello From a FM Newbie. I'm looking to build a report but I need to add a calculation with a summary field. I added a new field called "Orders" that is a calculation / number with field STORE_ID=1 Next, I added a new field called "Order Total" that is a summary / count of. Next, I added a merge code for "Order Total" to a part sub-summary to a report but the result are given all orders from the field STORE_ID regardless if STORE_ID=1 or STORE_ID=2.

Hello, I am a firefighter and have made a few files for local fire departments around me. We regularly respond to incidents on the highway and collecting data from motor vehicle accidents can be hectic. We normally manually type all the data from a drivers license and registration but there has to be a better way. I have found a bunch of online blog posts for scanning Drivers License data(PDF417) and vehicle information (VIN) using the URL to open an external app and call the data back but I'm not to sure how to implement any external apps or internal filemaker calculations that would be something I would be able to figure out. Plus it would be another c…

Hi, I have been trying to copy a formula from the web into a calculation field ... however my results do not match the results of the web calculator ?? The web calculator is only asking for two pieces of data ... and I am assuming it is width and length ?? Here is my calculation I have written based off of the web's formula ... ( Pi * Sqrt ( 2 ) ) * ( ( ( FinishSizeW / 2 ) * ( FinishSizeW / 2 ) ) + ( ( FinishSizeW / 2 ) * ( FinishSizeW / 2 ) ) ) Web instructions if you don't use the calculator ... The formula for an "Oval" is PI * SquareRoot of 2 * ((1/2 long axis)squared + (1/2 short axis)squared) I have assumed that the 1/2 long axis and the…

Hello, I am new to Filemaker Pro and need help. I use prorata calculations for my payments. Here goes: Our month runs from the 26th of the month to the 25th of the following month. (That is a full month without the Sundays - 26 days) If our clients contract starts on the 30th and the cut off is the 25th he will receive a prorata payment for the first month. It would have to be the full monthly amount divided by 26 days times the prorata days equals the prorata amount How do I get the calculation right for my field and how would I enter it there? Can anyone help me? Thank you in advance. Patricia