Calculation Engine (Define Fields)

Hi all I'm looking to display to the user when the last payment was made for a lease (from the invoice table) to include date and amount paid. The payment table is related to the lease table by a LeaseID. Inside the invoice table I have Type (which defines the record as a "payment" or "charge"), Date, and Remittance, which is what needs to be shown. I'm assuming this would need to be an SQL statement where it gets the latest date from the invoice table where the record is marked as "payment" in the Type field. Then an additional calculation field to show the remittance amount. Can this be done natively?

Hi forum! I'm having trouble with something either very simple, or I made it too complicated. This part of the database is used to measure the volume of different types of fuel tanks. Different tanks records are populated in the TANKS table connected to a TANKS_NAME table for the picture. There can only every be 7 types of tanks. The TANKS table can consist of many sizes of each type of tank. To do Volume measurements I use the Volume table. Using dropdowns, I first select a Tank Type, then an (existing) Tank Name. At this point I'd like to pre-populate the initial parameters. It appears I need another relationship to make the Lookup …

Is there any way I can assign a user (non-administrator) to create other users? I want to keep the admin account, but I want to allow a certain user his role to create other user accounts and not giving him the full admin right? I have a working script to create users but it seems to be ONLY working when logged in as an Administrator. I was just wondering if it's possible to just allow this privilege to a non-admin. Thanks.

Hi! I have a question about the capitalization of sentences via calculation. I have a text field to which I usually import certain amount of text (cca. 10 sentences) in the form of sentences but without the capitalization of the first letter in a sentence. Is there a way to capitalize the fist letter of each sentence via calculation? Any help is appreciated!

I am having a problem getting the above field to work on the sub-summary part. There are 2 summary fields (TotalAmtOn, and TotalAmtOff) which display as expected. The other field (with 3000 in it) is CurrentHolding and should display the amount left when TotalAmtOn, and TotalAmtOff and summed. So the 3000 should be 2000 in the abpve example. The same problem is in the $49000 field?? Any assistance will be appreciated please. Trades Temp.fmp12

Good evening. I have a solution whereby the client wants to see if the record is empty or not in a given table. I know I can create another calculation field in that table doing a heap of IsEmpty() checks on all the other fields and create a flag to that effect, but there are already many fields in this particular table and this calculation field would need to be updated should any other fields be added or deleted. How can I determine if a record is empty or not. Is there such a thing as a record length, and this can be interrogated? Any suggestions are appreciated. Greg

I have an Employee Records database, with contains a related table of wage history contained within a portal, which is sorted by date. Employee - John Doe Date Description Wage %Change 1/1/2021 Annual Raise $20.00 1/1/2020 Original Rate $18.00 I'm trying to create a calculation, which will display the percent difference between the data in the subject portal, and the next portal record in the sequence. I've created the below calculation, but the results are out of sequence and/or inaccurate. It appears that my formula is not grabbing the proper values?? Let ( Salary1=GetNthRecord ( Wag…

Hello, I need help please calculating texts from a portal. (Image attached) I have questions with answers (Positive, negative, N/A), I am trying to report the questions and answers in a sentence, but having difficulty. The positives in 1 paragraph and the negatives in a second paragraph. I am using the following calculation: If ( Current SOAP Answer::AnswerHistory = "Positive" ; "John has" & " " & Substitute (List (Current SOAP Questions::QuestionHistory) ; "¶" ; ", " ) ; "John does not have" & " " & Substitute (List (Current SOAP Questions::QuestionHistory) ; "¶" ; ", " ) ) My issues (1) The sentence only r…

Hi, everyone! Greetings form Brazil. I have this database that keeps track of all games of a basketball team. I have a table called GAME that has info like date, opponent’s name, score (for and against), venue, etc... and a table called LINEUP that have individual statistics (pts, rebounds, assists, etc...) for each player for each game (I only keep statistics of the team I support as I’m not interested on opponent’s data). I recently added a field to the GAME tabel called “Type of Game” which has a drop down list with two options: competitive or friendly. The GAME table links to the LINEUP table in a one to many relationship thru the “Date” field. I also…

Hi I’m trying to calculate how many jobs employees complete on a day sorted by shift. We have 3 Shifts: Early Shift - 06:30:01 to 14:30:00 Evening Shift - 14:30:01 to 22:30:00 Late Shift - 22:30:01 to 06:30:00 I have 3 fields CompletedJobTimestamp CompletedJobTimestamp_TimeONLY CompletedJobTimestamp_DateONLY I'm not sure if I should be converting "CompletedJobTimestamp_TimeONLY" to seconds and running a if statement to see if it falls into 1 of 3 ranges to give it an Early, Evening or Late result. Then I can sort using this result under a date search. The problem I have then is the late shi…

Hi all I've searched online to see if an existing calculation has been made available online to achieve my goal here. I'm actually quite surprised I've been unsuccessful in finding one as I believe it would be a neat feature, though I can see why it may be considered complex. I currently have two date fields (startDate, endDate) and I'm trying to calculate a plain-English read out of the time frame. For example: (startDate) 01/01/2021 (endDate) 01/01/2021 "1 Day" (startDate) 01/01/2021 (endDate) 01/03/2021 "3 Days" (startDate) 01/01/2021 (endDate) 01/08/2021 "1 Week" (startDate) 01/01/2021 (endDate) 01/09/2021 "1 Week, 1 Day" (st…

It's the first time in a while that I've had something to really get my teeth into with Filemaker. I'm needing to run some analysis on an invoicing database. The basic structure of the database is pretty much as you'd expect with a table each for customers, orders, and line items on these orders. This is essentially the conversation that gave rise to this task: "Small orders don't bring in much money and are a lot of hassle. Let's impose a minimum order limit or find ways of discouraging these kinds of orders" "Ah, but customers start off with small orders, and come back to us with larger ones later" So what I'm essentially trying …

Hi all I'm trying to display a list inside one field on one row that contains the field values of the related child records. Using UniqueValues() I'm able to filter that list down to remove duplicates, of which there are many for some records. The following calculation works as expected, however UniqueValues() seems to be counting an extra blank record at the end and I'm struggling to find how this is happening. I'm using Substitute() to replace carriage returns with a comma to keep the data on one line. If left as a list there is a blank row at the bottom of the field. When subbing returns for commas the last returned item is followed by a comma. See below: …

Hi guys, I'm scratching my head trying to use ExecuteSQL syntax. How can I prevent a division if a value is null or zero in a SELECT statement? Here's a simplified version of my query. "SELECT exp.labels, SUM (exp.budgeted) as budget" & ", SUM (exp.expenditure) as expense,"; "IF (expense > 0) SELECT (budget / expense) ELSE NULL"; // <--- THIS IS WHAT I WANT TO FIX "FROM expenditures AS exp"; "WHERE exp.id IN (" & ids & ")"; "GROUP BY exp.labels" Thanks

Is there a way to find what causing this issue? I'm hard time checking the culprit. Also which is less impactful on the performance. Filtered portal or filter the portal through relationship.

Hi guys! I am new to the forum and certainly not an expert on FM, I am currently implementing a Code128 barcode for some labels a client of our small business requires us. Created a field that combines the data I need into one string of data for the barcode generator plugin of FM19 to use. The issue is with two of those fields First a date field: I have a production date field 01/05/2021 and I need that data displayed without the separators (01052021) Second a lot field: I have a lot field formated M00045 and I need that data without the letters (00045) this I somewhat acomplished with the GetAsNumber calculation but it also remo…

I'm trying to get the first occurrence of each unique value from a returned separated list using the While function. Can anyone tell me why this calculation doesn't work? SetRecursion ( While ( [ ~values = "Apple¶Apple¶Apple¶Orange¶Orange¶Orange" ; ~prevValue = "" ; ~result = "" ] ; not IsEmpty ( ~values ) ; [ ~value = GetValue ( ~values ; 1 ) ; ~result = If ( ~value ≠ ~prevValue ; ~result & "¶" & ~value ) ; ~prevValue = ~value ; ~values = RightValues ( ~values ; ValueCount ( ~values ) - 1 ) ] ; ~result ) ; 10 ) I have been staring at this all morni…

I'm looking for a simple calculation for the date of the last day of the previous month, based on Get(CurrentDate)? I can use month(get(CurrentDate)-1, but wondered if there is an easy way to calculate this, other than a complex calculation for the day, based on 28, 29, 30, 31 days in the given months? Thanks, Bill

Greets, all: Since I can't insert a portal within a portal, I'm stuck: I have a parent (EVENT table) - child (DATE_TIME table) relationship where I want to list (the calculated) multiple timestamps in the Upcoming Events column. I used the List function but it apparently doesn't sort timestamps; I also used the SortValues calculation but didn't get far with that, either. More than likely I'm bollocksing-up the calculation (or using the wrong approach altogether) so I'd appreciate some assistance. TIA for your help! Rich

I'm currently building a layout to display data of reported errors during our production. The layout consist of: A chart that display the errors title and a summary count of that title. A Sub Summary sorted by the error title, that shows the error title and summary count of that title. I can display the data for the current selected month just fine, my problem is that I'm also interested in displaying the count of shown errors from the last 3 months, and I'm unsure how to achieve this. I'm thinking of 2 global fields where I store my two found summary count, but how is that count going to link to the the error title? Any help appreciated. …

Hello - Im looking for solution to this problem: If [text style of selection = X ] then A else B. There does not seem to be an obvious way to get text style of selection. So I tried this workaround: If ( TextStyleAdd ( Middle ( Get ( ActiveFieldContents ) ; Get ( ActiveSelectionStart ) ; Get ( ActiveSelectionSize ) ) ; HighlightYellow ) ; "1"; "0") thinking that if the selection is styled as in the TextStyleAdd expression then it would evaluate as TRUE but it doesn't work. Is there any way? Thanks.

Hi Forum, I have a text field called "Plate1 Options" that is defined as a checkbox set. This field has 14 options that can be selected (single or multiple selections). An output of what I'm looking for is a second field called "Plate1 Option Labor Time" that adds up all the calculated results based on the selection of the checkbox set. I was hoping the second field "Plate1 Option Labor Time" to have all the calculations with in it, based on the selection using the "If" statement (See attached image). If this is confusing, here is the premise of what I thought would work, but doesn't: "Plate1 Options" - Text field defined as checkbox set: Option#1 - Ba…

Hello all, I am currently trying to use a calculation field to display a series of text and images together. For example, I have images of controller buttons and would like to create a calculation to display something like "Press <button graphic> to guard against attack." where <button graphic> is the actual image of the button. I've come up with a calculation that loops through a few repetition fields to construct the field, but my problem is that it displays the container field as a path and not the actual image (i.e. "Press image:Graphics/Interface/x button.png to guard against attack." I tried setting the calculation field to display the res…

Hi All, Hope you all are doing good. We are having cutting text/contents when printing or printing/exporting to PDF. We usually print stuff on Letter Size, however sometimes a different page orientation or size is requested. Is there an option to auto fit all the contents (fields, text, headings, graphics etc.) on the page no matter what page type and orientation is selected? Please help, thanks

Hi All, Hope you all are doing well. Not very FM savvy, need your help! I'm looking a syntax solution for something like this: If condition meets FormIsGood is Yes, then a button appears that will be able to trigger script that imports/copies(append) current record(or few of the fields of current record) into our MASTER DB. In a little more detail: We have two databases, one is transition and other is master. The registrations we receive stores inside transition, an admin staff verifies that registration record and then copies(appends) the record (some fields) to the master database. I want to automate this step using two concepts: 1. Staff …

Hi I'm trying to get the first numbers of a text string that has the following format numberxnumber,numberxnumber (5x2,10x6,20x30)..n times. The field can also be empty. And i want to take the result and put it in another field and look like this: 5+10+20 I have something like this: Left( text; Position ( text; "x" ; 1 ; 1 ) - 1 ) But that only works for the first number, and I'm not sure how to get it to iterate through rest of the text Note. the calculation is done on a on object exit script. Any help is appreciated.

I have table called Resources that contains a field named "Name". There are a number of other fields but the relevant ones are a field named "Category" and field named "Project". I want "Name" to be a calculated value. If I use the following simple calculation it works fine: (Right(Year ( Get(CurrentDate )); 2)) &"-"& (Month ( Get (CurrentDate ))) &"-"& Substitute ( Project ; " " ; "_" ) & "-RetargetingPixel" This is the result: 21-02-Project_Name-RetargetingPixel However, if I enclose the same calculation in an IF or CASE function, for example: If ( Resource_Category = "Retargeting Pixel"; (Right(Year ( Get…

I'm trying to make basic addition with values in a field. Field A contains a text of numbers 10,4,2,5 Field B is my calculation field where i want the result of field A. (10+4+2+5) Sum (Field A) just gives me 10425 instead of 21, and i cant find other functions to solve this. Any help is appreciated

I am new to FM and am wanting some help with a calculation. I have four fields, rate1 & rate2, quantity & result. What I am trying to figure out is how to have a calculation that will calculate the result of the value in the quantity field divided by the rate where if the field rate2 has no data (is empty) the calculation formula will use rate1 to calculate the result. However if there is a value in the field rate2 then the result should be calculated based on this rate2 and not use the value found in rate1. Appreciate any direction and help. Thanks

I'm in a bit of a quandary here. You can use the custom display of a date for it to appear as a fiscal quarter, eg Q1 2021. But then, unless i am missing something, it doesn't appear that you can do a search in the date field for what is displayed "Q1 2021". The date field only manages to find the precise date which is not displayed. So how do I go about finding a given quarter without knowing the precise dates? Is there a work around for this?

Hi, I have a parent table "Animals" and a child "Exams". I would like to chronologically number each Exam for a given animal based on Exams::date (i.e. first exam = 1, second = 2, etc.) and I need this number as a STORED value. Additionally, as exams are added, I need this number to update across all child records for that animal in case an exam is added 'between' two existing records (eg. Exam 1 = 1Jan21, Exam 2 = 31Jan21.... new exam added as 20Jan21, so would need the previously labelled Exam 2 to become Exam 3, etc.). I can do this with calculation field no problem with a self join relationship from Exams... but I do need the value stored. I can update…

Hi everyone I have a Calculation Field called "extract_field_1" which extracts the data of "field_1" (ie, anything between "field_1:" and "field_2:" in a text-field called INPUT which looks like this: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Let ( [ lineStart = Position ( INPUT ; "¶field_1:" ; 1 ; 1 ) ; start = lineStart + Length("¶field_1:"); end = Position ( INPUT ; "¶field_2:" ; start ; 1 ) ] ; Middle ( INPUT ; start ; end - start ) ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - This works nicely (many thanks to comment for this). However, sometimes the result will start with one or more "space" chara…

Hi, Table has field called Field1, I would like summary total of the current found set to say how many time 'Apple' is in the Field1 Is this possible?

Hi all, I hit my logical thinking-limit and FileMaker knowledge with the following, seemingly simple problem: I would like to filter a list of values to only show values with four or more words. How can I do that? Sample Input: Test Test Requirement Test Requirement Example Test Requirement Example for Test Requirement Example for HBS Test Requirement Example for HBS 5.0 Expected Output: Test Requirement Example for Test Requirement Example for HBS Test Requirement Example for HBS 5.0 Many thanks in advance!

How to replace the last "," with "and" Here my variable test, test2, test3 it should be test, test2 and test3

hi, I have a Client table, with a related table containing certification dates I need a calc to look at the related table and check that at least one in the list has not expired (older than todays date) I also different query to look at the the same table, but need to specifically look at a type as well, so in this case need to check that type1 has not expired Thanks!

I am working on a Filemaker solution (using v16.x Advanced) that requires the auto-generation of SKU codes that are built from 2 separate numbers. The first number, a ContactID, is auto-generated as a simple serial number in a "Contacts" table when a contact name is added. The second number needs to be generated in a separate "Items" table and needs to be the highest value for any given ContactID plus 1, call that number the ItemID, resulting in a "SKU" code of "ContactID-ItemID". (I don't think the "max" formula will work on a local table) Resulting SKU Examples: 1-1 1-2 2-1 2-2 etc. In the "Items" table the user would first select the desired co…

I have a calculation field, with a text result, that I want to give some sort of visual indication of how expensive an item is without displaying the actual price. In a list view, I would want that text calculation field to provide quick visual reference as to how expensive an item is: ITEM 1 - $$$$$$$$$ ITEM 2 - $$$ ITEM 3 - $$$$$$ ITEM 4 - $$$$$$$$$$$ ITEM 5 - $$ Short of nested Ifs, what is an elegant way to achieve that calculation? Thank you

I have a table with a course total (£600, or £1000) in each record. I am looking at this table via a relationship, I what I need to do is count how many £600's there are and how many £1000's there are (not total, but display number so if there were 6 values of £600 in the related records it would display 6) Thanks!

Hello, I don't understand why FileMaker changes the value of a number when converting an Excel file to FileMaker. Please help me understand... Thanks in advance

I'm updating my company's Expense Tracking solution, and have borrowed doe of the example content from this older, FMP template file. There is a feature of this solution, that I cannot reverse engineer for the life of me. The attached solution has a single DATA table is used in two relationships - one for "Expenses" and one for "Mileage". The Form view shows the data in two portals, which are filtered by a related field, Type | MATCH FIELD. When you add a new record to the respective portal, the Type | MATCH FIELD auto-enters one of two values - Expense or Mileage. I cannot figure out how "Mileage" is auto-entered for that portal?? The calculation is: If (…

Hello I hope all is keeping well. I have been looking for a couple of days now on getting a calculation for my sample input. I have a table called Jobs and a Table called samples I have made a relationship between the jobs and samples I have made a portal on the jobs layout which I have written a script to add the samples to the job table which all works well. In the job list view I wanted to add a total count of not sampled test and a total count of the samples what has not been tested. How would I go about this I have tried calculations but with no joy. Could some one please help me with this. Thank You Screenshot 2020-11-22 at 2…

Hey guys, I am working on a brewing recipe file and I have created a IBU calculator. I have the calculation working but it needs to be based on all of the portal rows. Currently it only reads from the first portal row and will not make any further adjustments to the calculation for additional rows. I don't know if I am missing something in my calculation or what. This is the formula for the IBU field, and it is basing the findings from a portal called "Hop Database Copy" Brewing Recipes = ( Hop Database Copy::Quantity * ( Hop Database Copy::Alpha Acids * 100 ) * Hop Database Copy::Percent ) / 7.25 Thank you for your help, Alex Bre…

Hello FileMaker Wizards, I'm helping a car club with a task that either is beyond me or may be impossible given the structure of their poorly designed database. To ultra-simplify, I've exported all of three fields which are all we need to accomplish the task...here goes. Every car has a 4 digit serial number, a current owner, and a list of past owners. All we want to do is generate a list, sorted by owner's name, showing all the cars they own or have owned in the past. If they currently own a car, it's serial would show as bold. That's all! It should look like this (fake data): John Doe - 0075, 1736, 2567 Jane Smith - 0362, 1883, 3002, 3182 The probl…

Looking for a bit of help with the LIST and Substitute functions (I think). Grocery Store mapping application. An Item is in multiple locations in a single store. Item: ABCD is in locations 1A, 5B, 12A and at all the Checkouts (in this example, 5 checkouts) The table ITEM is the parent and the child table is SKU (which is all occurrences of an ITEM in a STORE) Using List, I get this for Item ABCD 1A 5B 12A Checkout Checkout Checkout Checkout Checkout Nesting the list in Substitute to change the RETURNS to COMMAS and a trailing space and get this: 1A, 5B, 12A, Checkout, Checkout, Checkout, Checkout, Che…

Hi, My database has a relationship between Company and contacts. Each contact is a different type, say type1, type2 etc I want to be able to do a simple calculation so it displays a number of how many type1 contacts each company has. Thanks!

Hi all. I need to upgrade my Xero integration to use OAuth 2.0 and I'm struggling with 1 element. I have my Client Id and Client Secret and I can get an Authorization Code but I can't then get the required token. The Xero documentation says the token request requires: POST https://identity.xero.com/connect/token authorization: "Basic " + base64encode(client_id + ":" + client_secret) Content-Type: application/x-www-form-urlencoded grant_type=authorization_code &code=xxxxxx &redirect_uri=https://myapp.com/redirect My request always returns {"error":"invalid_client"} I'm using an insert from URL with the following cURL options: "-X PO…

I have two check box’s in my Products file. One is an indexed text field and the other is an indexed number field. I have a fairly large number of records that I’ve imported and I now want to put an “X” in both of the boxes (fields). Is there a way that I can automatically have both of these check boxes checked? Thank you

I'm trying to create a formula to average some fields, but am currently stumped. I have a FMP database that I created to use as a journal for my stock trading. I have a field set to calculate the average price of the stock based on the initial price, plus any additional stocks that I purchased after the initial purchase. The average price of the position should "average down" as I scale in more shares at a lower price, or average up if I buy higher priced shares. The fields related to this formula would be openingPosition (how many shares purchased), openingPrice (cost of initial shares purchased), scaleInPosition (number of additional shares pur…

My JSON data contains dates in one of three formats: YYYY YYYY-MM YYYY-MM-DD The field is: Items::Initial_Release Instead of the text, I would like to have it display that as a proper date based on what's there. 1984: Year Only 1984-08: August, 1984 1984-08-08: 08 August, 1984 Based on the available options for formatting the date, I gather that the FileMaker Date format probably can't deal with less than the full amount of data? Or even changing it, having it be Day first if there is full data, otherwise Year, or Year, Month only. Otherwise, it looks like I would have to have the date field in three diff…

I have a master table that needs to calculate the total value from many fields in a related, child table. The total must be shown –not necessarily stored– in the master table layout. I wonder what's the best option: Create a non-stored calculation field in the master table with Sum (child::value) or Just display a child table summary field with the total of child::value Both options seems to work but I would like to know if there's any difference in performance, is option 2 better than 1?

Any suggestions on how to define "yesterday" and "tomorrow"? Finding Get Current Date + 1 and -1 no longer works.

I have converted a spreadsheet to FMP. I have duplicate records that have a field with different information. I want to combine the info in the field together, if the ID number is the same. How do I go about this? Example: have Id number 123456: record has "Information A" in field "Type" Also have ID number 123456: record has 'Information B" in field "type" I want field "Type" to say: "Information A, Information B" for my ID number 123456.

I have a calculation field called 'zpastableMESSAGE' the entire content of whose calculation is as follows: TextSize ( TextColor (TextFont (zMESSAGE ; zMessageTextFont ) ; zMessageTextColor ) ;zMessageTextSize) (1) the field 'zMessageTextSize' is a text field, and whatever numeric value I enter into it is being used as TextSize, and correctly changes the size of the text displayed in the field 'zMESSAGE' (2) the Calculation fields 'zMessageTextColor' and 'zTestMessageTextFont' at present contain values RGB(0,128,0) and "Krazy Kracks NF" respectively, BUT ARE BEING IGNORED (that is, the text in field 'zMESSAGE' is NOT HAVING those values applied). But, if …

Hello, I have accidentally deleted all records (over 6000) while working on a file. Is there a way to recover the deleted records? The file size is the same as before deleting the records, which makes me think that the data might still be there. The last time I back up the file was over six weeks ago; so if I cannot recover the file, all the works and modification I had made to the file are forever lost. I saw a similar post from 2004, asking the same question. I have also tried the file "recover" option, but it didn't work. I tried to open the file in a text editor, but noting is decipherable. If there is a way for me to read the deleted records, I can work from t…

I am a beginner in Filemaker using Filemaker 18 and I need a script to increment a number by 1. I have 3 fields. Start Number End Number New Number I want to enter a number in the start field "50" I want to enter a number in the end field "100" I need the script to create a new record each time filling in the "New Number" field starting at 50 and ending with 100 Please dumb down the answer as much as possible because I am not at all familiar with scripting. Thanks in advance for the help

so on this record... I want to show another value from another record if that other record meet the requirement. please check picture and I'll explain more... So the picture shows the record of John Wick... Under Admin Officer I want to put the name of the Admin Officer which is another record on the same table, but the record/user needs to be assigned a job title as Admin Officer (like John Wick job title: Assistant IT Specialist) in order to automatically put his/her name in the provided "Full Name:" spot. I tried creating a new field calculation field to put there but I'm lost what to calculate... I'm thinking, if user = Admin officer, get his name.. th…

I have a library file of books (way too many books!). The titles were entered with "The" and "A" at the beginning of the Title field, if that was part of the book's title. I would like to move these words to the end of the field after a comma and space. "The Name of the Rose" ==> "Name of the Rose, The" More specifically I would like to create a calculated field, e.g. "TitleSansThe" and use a calculation to populate this field from the Title field. Can someone please point me in the right direction. I am new to calculations (and most of FMPro for that matter). Thanks in advance. John PS. I searched for previous posts on this topic, but was una…

I have been using Filemaker Pro 12 Advanced since 2013 years & it does absolutely everything I need it to do. I'm very happy and don't want to change. My daughter has very similar needs to me and would like to use FM Pro 12 Advanced but I suppose you can no longer purchase 12. If I were to purchase the latest, which I understand is Filemaker 19, is everything completely transferrable from 12 to 19 including Scripts, Databases etc? What I'd really like to do is buy my daughter a copy of 12 it would all be so much easier then. Is there any way this can still be done? Thanks in advance.

So I want to create a button that will open up the sorting window... but instead of showing all the options for sorting to the user, I want to limit what is shown in the sorting window to a specific table with only a few selected fields to be available for sorting... instead of showing everything. Is this possible? what is the best way to do this? Thank you.

I need to process a tab-delimited file. The source is "Adams, John Q". I need to parse it into two fields: nameLast and nameFirst. (I don't need the middle initial.) nameLast is a straightforward LeftWords calculation. But I'm stuck on how to ignore the comma and and the middle initial to extract the first name. Can anyone help? FMPA 18 macOS 10.15.6

So I have a Table "Daily Leave" related to "User Profile" table. I created a "Daily Leave" portal on the User Profile layout and started adding other fields to this portal; Year, Type of Leave, Leave Entitlement, start leave, resume work etc... please check attached picture. So the Leave Type field is a popup menu which let you choose the type of Leave you want to take e.g. Annual Leave, Sick Leave, Compassionate Leave, Leave without pay or Maternity Leave. So based on the this field "Leave Type" I want to show their last "Balance" like shown in the picture. I am able to show the Annual last balance which is 16 but its only because it is the last in the list so…

I'm using FM Pro 14 Advanced with a stand-alone desktop database Trying to get a count of "|" characters in field VARIATION RELATIONSHIP DETAILS so using PatternCount ( "|" ; VARIATION RELATIONSHIP DETAILS ) where the field for a given record is: "Length=1 ft | Color=Black" The function is returning "0" when to my understanding it should return "1" Calculation result is set to Number I have tried it with other strings and fields and am always getting "0" Anyone know why? Does this function only work with multiple characters ( a pattern)? Is there a better way to get a count of specific characters in a string? Sorry to be a dummass …

Greetings, So I have a date field which contain lots of date records. I want a button to perform a script to find only dates for this year only on the mentioned field. I am having difficulties trying to Specify a Find Request on what criteria to put.. I can easily put 2020 as a find criteria but I don't want to do this every year, so I need something like "Year(Get(Currentdate)" as a find criteria but it won't allow me giving me the error "The Value of this field must be a valid date in the range of years 1 to 4000 and should look like 25/12/2013". So I guess my question is how to put the find criteria as today's year? How can I do this?

Hi there, I have a field set to display a value list which shows unique dates based on a team members meetings. I would like to add a 'next' and a 'previous' button to my layout to set the field with the next falue contained in the value list - is this possible? Hope someone can give me some guidance thans in advanc.

I'm trying to build a database "Staff Leave", which will records staff leave daily etc. This is currently being done manually on excel (check example below). Excel: So based on that excel, I'm trying to come up with a similar design on a filemaker database. I've tried a few but with no luck.. So i've come up with something like below which uses a portal to imitate what looks like the one on the excel. Filemaker: This seems to be working fine. But the problem that I'm facing is when it comes to printing this leave records on this portal. I really have no clue on how to accomplish this, I really need someone to point me in the ri…

I have the following Calculation field called "extract_card_type" in a FMPro16 database: Let ( [ lineStart = Position ( PASTED_INPUT_to_use ; "¶Card Type: " ; 1 ; 1 ) ; lineEnd = Position ( PASTED_INPUT_to_use ; ¶ ; lineStart + 1 ; 1 ) ; start = lineEnd + 1 ; end = Position ( PASTED_INPUT_to_use ; "¶Organisation name: " ; start ; 1 ) ] ; Middle ( PASTED_INPUT_to_use ; start ; end - start ) ) and the text field 'PASTED_INPUT_to_use' is the following: incoming order Card Type: ORG WITH BORDER Organisation name: org-name Organisation type: org-type I expected the field "extract_card_type" to contain the value "ORG WITH BORDER" but it i…

I have a database setup wherein I take post-production notes on an animated series. I have a slew of retake scenes in any given database (a new one is used for each episode, a protocol issue with the studio) which some of them have categories for the type of note. Not all scenes have this category (they indicate reasons for these particular notes). When we have completed the review of any given episode, we need to reconcile those categories. Generally this comes down to matching that categories frame count (say 12) versus the scene's entire frame count (say that is 20). If the counts don't match we generally need to add another iteration of the scene with a category that …

Is this doable or closest way to work on this? Example: We have multiple user using difference language ex English, French, Spanish etc. So if i am the user using language english then enters a record with english data. Let say i enter a data on a field "Inches". Can that stored data be translated once a user with different language entered the record? So if the user using spanish language. The stored english data "Inches" will be translated to "pulgada".

Hello, I'm working on a solution that will involve storing a lot of PDF files. I can't get the PDFs to display interactively in the container field when working on the desktop file in FM Pro 19 (it just shows the MacOs preview icon and filename). I have to manually download and open each file to view. FM Go opens the file fine in the native viewer. I am on MacOs Catalina and FM 19. I deleted the Adobe Internet plugins to no avail. Any other suggestions? I have had this problem on again / off again for many years with FM on Mac. Thanks

Hello all Is there any way to have a global field which does NOT share its contents across different windows. I.e. populating in one window will not populate it when it is displayed in another. I'm guessing that it's not as it would sort of defeat the point of a global field. So here is the situation: We wish to have multiple Case windows open simultaneously. Within each of these windows (which show the same Case layout but different records) we have a global field which is used to define the body of an email that is about to be sent and attached to that Case. We prefer this to be global so as to not lock the record. Therefore, the problem is that if this i…

I want to create a formula that takes information from 2 fields and displays the answer in a 3rd for example if field A is 10 and field B is 10 display A in the 3rd field thanks Adam

Hi, I need to create labels in DIN A5 size with the intention of printing two labels in each DIN A4 paper size. One of our customers wants to mark the number of each package delivered. For example.... If I send 20 packages, the intention is to create 20 labels in 10 "DIN A4" papers. All are similar with the exception of a counter. The counter should change, for example for 36 to 56; the next time from 57 to 77, 78 to 98 ... I'd love to be capable of putting the initial number of the first package in one field and the last number on another field and automatically to print the required number of DIN A4 papers with the incremental counter in each label. …

Is this doable through simple script or field calculation? I don't know where to start but I'm hoping to exact solution about this. Here my example. I have a stock of 70", 100", 2pcs of 150" and 12pcs of 240". Also i have orders that needs 39", 88", 91", 106", 55", 55", 72", 72", 72", 144", 144", 144", 32", 66", 66", 66", 66", 144", 144", 144", 50", 50", 81", 81", 132", 132", 132", 168", 196". In the real world my goal is to minimize the wastage. So it will end up similar to this results. #1 - 70" = 66", with 4" wastage #2 - 100" = 66", 32" with 2" waste #3 - 150" = 81", 66" with 3" waste #4 - 150" = 81", 66" with 3" wa…

Hello, I am pretty new to FM and dealing with Calculation fields so bear with me for the flat-out ignorance that follows. I would like to have a Calculation field in Table1 that conculcates data from a field in related Table2 but only if a condition in another field in Table2 is matched. Here is a vastly simplified example: Table2 (where the data is stored) has COUNTRY and STATE fields, with each STATE being a new Record. In Table1 I want to have a calculation field that conculcates all of the STATE fields but only if COUNTRY = USA (thus excluding UK or France). Currently, my calculation is If ( TABLE2::COUNTRY ; "USA" ; Substitute ( List ( TABL…

Can someone suggest a calculation or script to concatenate text of multiple records into one string in a single field. I need to concatenate the description field of all records with the same Year_OrderNumber in a single field. I attach a sample Thanks for your support Test.fmp12

Hello, Sorry if this has been asked and answered, but I could not find it. Probably using wrong verbiage in search or something. I would like to use the Hide Object (and its associated Calculation) to either show/hide a Field that effectively covers the entire Layout. This is to Lock/Unlock the ability to modify data in a current layout. I am able to accomplish this with a button bar that populates a separate field based on the button that is pressed and then reference that field in the Hide Object Calculation. However, this seems a little convoluted. Is there a calculation that can get the status of a button bar (i.e., which button was last pre…

I'm trying to get a field to show me: (Image Width) x (Image Height) Using a calculation for the field content I can get one or the other. Let ( a = GetContainerAttribute ( ISBN_Cover ; "width" ) ; a ) So I'm thinking something along the lines of (below) -which obviously does not work- But how do I.. Let ( a = GetContainerAttribute ( ISBN_Cover ; "width" ) ; b = GetContainerAttribute ( ISBN_Cover ; "height" ) ; a & ¶ & "x" & ¶ & a ) In the process of posting this question, it occurred to me, "the other side of the semi-colon is the output, but what about embedding another calculation in there too?" ...and that work…

I am using the script step "send mail" in FilemakerPro 16 to send emails, but what I would like is to include HTML code in the body of the email, such as: <table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td><table border="0" cellspacing="0" cellpadding="0"><tr> <td align="center" style="border-radius: 3px;" bgcolor="#ff0000"><a href=" https://litmus.com " target="_blank" style="font-size: 16px; font-family:sans-serif;font-weight:bold;color: #ffffff; text-decoration: none; text-decoration: none;border-radius: 3px; padding: 12px 18px; border: 1px solid #e9703e; display: inline-block;"> I am a red button →</…

I need help please I have three fields Due Date, Days Till Due and Issued date. I am having a problem on working out a script that when the Issued date as been populated I need the Due Date and Days Till Due for the fields to be cleared but I cant figure out how to do this. Thank You

I went searching and found this post from a few years ago, but with no real resolution to a simple question. On Mac OS 13.1 & FM16 Trying to do a very simple calculation to hide or show one of two objects. Using the first rule works fine. Introduce a second rule, it ignores it, both objects show. Super simple, figure I'm missing something. Thoughts? Field A is a value list entry and Field B is a text field entry. It doesn't seem to be a character encoding thing.... Object 1 Hide object when: Table::Field A ≠ "Some" and Table::Field B ≠ "One" Object 2 Hide object when: Table::Field A ≠ "Some" and Table::Field B = "One" …

Hi there I have: 3 fields which relate to team performance criteria: ServiceTakings, RequestRate, and RebookPercent. We have 4 performance levels, team have to hit each criteria for said level to be awarded. i.e. tier 1 request rate = 0-39%, tier 2 request rate = 40-60%, tier 3 61-75% and tier 4 = 75%+ tier 1 ServiceTakings = 0-£4400, tier 2 = 4400-£58000, tier 3 = 5801-7000, tier 4 = 7001+ Team could be working at level 4 for serviceTakings, and level 3 for Request and 2 for rebook, in this instance the would only be awarded level 2 overall How can I set up a simple calculation to tell me which tier the team member is in? Hope someone can g…

I have created a related database for my Strat-o-matic baseball league. I want to track a player's movement over his entire career as he moves from team to team by way of the player being originally drafted into the league, traded, cut, drafted again, etc. I have created one table that is the master Player Tracker, which has portals to the following tables: Rookie Draft, Trades, April Supplemental Draft, Mid-season Supplemental Draft, Players Cut. All of these tables have a "date" field that records the occurrence, and a "team" field that records the team. There are multiple times a player changes teams. I'm trying to write a formula for the Master Player tabl…

I'm in the process of designing a report and am running into issues thinking through how to narrow down a record set for the math of the report. My parent report table has records for each week, as well as output per week. These values are entered manually since there's no viable option for a data connection. The child table has detailed records of hours spent on task per department per day. I've already managed to build the report to only show totals for one department by using the relationship as linked to a global value in the parent table. My question is: how do I further narrow the calculations to only count hours for specific records in the child table? In…

How can the following calculation ever result in a null field? The results I get are 1, 2, or empty. Why not 1, 2, or 53? Case (Week[1] = 1; 1; Week[2] = 1; 2; 53) Thanks, Paul

In table Planting, I am evaluating a Calculation field, Quantity_Display, which has 15 repetitions from a Number field, Quantity, which has 100 repetitions, to . Which repetitions are evaluated from which depends on two values in a related table. I use the following calculation: Let ( [ $i = Get ( CalculationRepetitionNumber ); $j = Property::Year_Curr - Property::Year_Start - 14; $k = $j + $i ]; Quantity[$k] ) (I know I don't need both $j and $k but they helped me debug) The first repetition works fine evaluating Quantity_Display[1] to the value of Quantity[5]. This is what I want, because Property::Year_Curr - …

Hello, What's the proper format to when looking for empty and not empty information?

I have an "Award Deadline" field that is calculated based on what is entered into another date field, "Allocation Date". The "Award Deadline" is typically 6 months from the "Allocation Date". Recently, due to COVID-19, "Allocation Date" entries in between October 2019 to June 2020 are allowed 12 months instead of the typical 6. I am trying to fix/revise the Case in my "Award Deadline" field to allow entries of date between October 2019 to June 2020 in the "Allocated Date" field to calculate 12 months. The case I have written for the "Award Deadline" is: Case ( ${Alloc. Date (CON) 1} ≤ GetAsDate("9/30/2019"); Date(Month(${Alloc. Date (CON) 1}) + 7;1; Ye…

I know how to find the median of numbers in Excel, but I don't know how to do it in FMP. I use FMP 16 and I can't find a "median" command. I have three simple numbers that require a median, and I just need to know which command to use. I know this is probably very simple, but my initial run through the forums for an answer didn't produce anything simple. I can't do the calculation in Excel before importing into my database, because the source documents are different. I can bring them together in FM as a calculated field, which would be the median. Your help is appreciated! 😊

Hi I have a repeating calculation field. The first repetition is able to refer to related records, the join fields of which are not repeating. However subsequent repetitions can't see any related records. I've only got seven repetitions, but the calculation is quite complex so I'd rather not switch to just using seven separate fields. Is this behaviour by design? Thanks

Long story short is I'm having a printing layout issue that I'm not sure how to solve. I have a fire dept file that has an EventReport table and each event gets one record. Related to the EventReport records is a Narrative table. Officers from an incident can enter multiple timestamped narrative dialogs for a given event. I have a layout on the EventReport table for printing an event report and I had listed the Narrative dialog records within a portal. However i'm just having layout issues between windows and mac when printing and the portal records wouldn't line up with the page breaks and if I put a second portal on page 2 and it was empty I couldn't get that page …

I changed a field from a plain number field to a calculation field so I could automatically enter some data. Disaster immediately struck upon hitting the OK button and all of my data was wiped out. I have an excel file with the raw numbers in it, but when i try to import the data, it won't let me enter it into the new calculation field. If I change it back to a number field, I can then reimport the data, but then can't change the field back to a calc field with losing the data all over again. How do I get around this? Here's some details: I'm entering emissions data for engines. When I start a new engine record, it automatically generates six child records, one…

I need a formula to determine the end date of a 6 month program from its start date

Hello I need help please I have 3 buttons the following set fields button 1) set field [T50a_Samples::Morphology ; "Curly Silky"] button 2) set field [T50a_Samples::Morphology ; "Straight Sharp Glassy"] button 3) set field [T50a_Samples::Morphology ; "Straight Wiry"] What ever the field is set too I am trying auto set another field 1) T50a_Samples::Suggested_Material_Type ; "Cloth" 2) T50a_Samples::Suggested_Material_Type ; "Fabric" 3) T50a_Samples::Suggested_Material_Type ; "Leather" I have been on this for 3 days now so any help would be grateful Thank You

How to find out what causes this dialog to pop up and its delaying some of the works? Is it something related on unstored calculations?

Hello, I'm trying to do an auto enter calculation with an IF statement when importing data. I have one field that will either have an IP address or a computer name. If the field is a computer name, i want to strip out some of the text before the actual computer name. The format of the field with a name is (DOMAIN\computer-name), I'm attempting to strip the DOMAIN\ out and just return the computer name. If I do the calculation without the if statement then it works, for the computer name, but removes the IP address. This is the formula that I have If ( Position ( Self ; "\" ; 1 ; 1 ); GetValue(Substitute(TextFormatRemove(Self); "\"; ¶); 2); Trim(TextFor…

This should be easy, but my brain just won't cooperate! I'm moving a bunch of stuff and want to inventory what is in each moving container. I have a very simple parent-child relationship with the parent record having the container number and category of parts/equipment/supplies and the child records describe each piece of equipment in the container. I have a portal with the child records showing for each container, but want to put anatto-entered item number for each piece that has the container number followed by a dash and then followed by a serialized number for each item in that particular container. For example, I would have container 1 labeled "Glassware" and…

Hi, maybe not totally the right sub forum, but I couldn't find a better place... I have a client with an already complex system (60 tables, around 6000 fields total, 477 relationships, 1315 scripts, 400 layouts, file is 900Mb) and he is now asking to add a quotes module, which, due to the technical nature of their business, will also contain a lot of tables, calculations and relationships and so on. Before I start building this quotes module I would like to know if there is a limit to what you should or can put into one file. I'd prefer to keep everything in one file because some tables in the main system will be used in the quotes system as well, …

I feel a little sheepish asking this because I've done it many times before but just can't remember how to do it today; too stressed out from not being able to buy eggs, milk, bread, toilet paper... What I want to do (in Layout 2 after sub-summarizing/sorting by Course Name then Risk Level) is show the percentage of the Student Count ( = 150) to the right of the Risk Level, e.g., Achieving 96 (64%) At RIsk 6 ( 4%) Early Alert 48 ( 32%) I tried the calculation, ( RiskLevel__lxs / StudentCount__lxs ) * 100 ...but that doesn't work. *heavy sigh* I feel sooooo dense. Thanks in advance for your help. Summary E…

I just ran into an issue where I imported data that was in all caps into a Filemaker table from an Excel spreadsheet. I then used TextStyleAdd( field1; Titlecase) to change it to title case. In FileMaker, it looks properly transformed. If I copy that transformed text and paste it into another FileMaker text field it still looks transformed. If I copy that transformed text and paste it into a text document OR I export that field to anything other than a FileMaker file, the text is in its untransformed state. Steps to reproduce: 1) Transformed "HELLO" to "Hello". 2) Copied "Hello" and pasted into another FileMaker text field. Result "Hello" …