Calculation Engine (Define Fields)

I've had an interesting challenge thrown in my direction by one of our administrators. Part of their job is to reconcile credit card payments that we have received, against orders that are in Filemaker. 98% of the transactions are simple to reconcile, but the remainder can be problematic. This is often caused by other staff not marking FM orders as having been paid, but nonetheless it still needs picked through. The problem is compounded by the way we receive card payments into our bank account, there are usually a number of payments lumped together for the day(s) in question. This makes it hard to spot unreconciled payments. If there is one individual payment…

I literally input the data, or auto input, see that everything is working well ( i use global fields on an unrelated table to store menu titles, button titles that is is, ui elements etc. ) then I log out of the db (hosted on fms13) and when I log back in everythings blank again...this defies logic...

I'm trying to put together a calculation/graphing tool for a friend of mine, he has several fields that he needs a running total generated for. Easy, right? Just use a Summary field and click Running Total. Except... on some of those fields from which we are calculating the running total, they have no data in them. But the running total keeps going. So what it looks like is something like this Where the field on the left is the number, the field on the right is the running total. What he wants is that running total (summary) field to be empty for the same records that have no data in them for the other field. But there's additional records in his data …

Greets, all: I'm trying to use the Position command in a script to parse text (after an Excel file import) from a cell (named F1) that's typically formatted like this: Sequence: 1; Item ID: 8536/1, Item Desc: Brandecker, CARD, Clinical presentation and evaluation of ischemic heart disease ...where I want to: Extract the Item ID number (which can be up to five characters long) and push it into a field named IDNumber. Extract The Item ID's question number which is the number immediately to the right of the forward slash ("/") symbol. (The question number can be up to four digits long.) That'll be shoved into the field, QuestionNumber. All…

how the IsValid () function works? Can this be use if the data I entered on a fields belongs to the value list.

I have a set of static data that's part of a web string. The users open URL's using buttons and I use scripts to do that. I'm trying to figure out the best way to store that data without storing it in the open url script, because it can change from time to time (not too often) and it can be a pain to change it from the script. I was thinking of creating an ADMIN panel (ADMIN table actually) where the various data could be stored in text fields, but this isn't effective because the table isn't related to any other table. Anyone with any ideas on this one? Thanks in advance.

I am hoping that someone can help me with the below problem. I am trying to create a field in a database to indicate the date that a trainee will move to thier next year of training. They go to next year of training after 52 weeks training. They may have multiple placements during a particular training year. I have their training start date (trainee::startdate... their start and end date (traineeplacement::startdate and traineeplacement::enddate) of each job (in a linked table), their percentage of full time (traineeplacement::PercentageFullTime) and the calculated number of weeks whole time equilavent in the job (traineeplacement::durationweeks). Through…

Greetings Filemaker experts, I'm designing some sort of athletic sport management system. As you can see it contains 3 sections; User Profiles, Development and Events. So it goes like this: A user registered as an Athlete or an Official (or both) and Sport(s) the he or she plays. These athletes/officials will have/had trainings, academic qualifications, and medical check ups (especially before a sport event) etc... from time to time. The Events are divided into three categories; Local, Regional and International Sport Events. Management team will be able to identify who's eligible to attend such events based on their qualifications, trainings and athletic c…

Hi, I have a 3 field, Color, Size, and List3, some words are in English so I made a list of all unique values of all three table, translate it, then past in to a table with field English (original word) and French (translated) what I would like is match at those three field with field in table where all words are already translated. for exemple Product table Record 1 /> Color ; red - Size ; eleven - List ; proud Translated table Record 1 /> English ; red - French ; rouge Record 2 /> English ; eleven - French ; onze Record 3 /> English ; proud - French ; fier Right now I put a relationship field Color…

I've tried to combine Pubmed Fields into one Calculation field: -------------------------- Let ( [ B = If ( PubMedArticles::Category = "Background" ; TextStyleAdd ( "Background: " ; Bold ) & PubMedArticles::Text & "¶" ; "" ) ; O = If ( PubMedArticles::Category = "Objective" ; TextStyleAdd ( "Objective: " ; Bold ) & PubMedArticles::Text & "¶" ; "" ) ; M = If ( PubMedArticles::Category = "Methods" ; TextStyleAdd ( "Methods: " ; Bold ) & PubMedArticles::Text & "¶" ; "" ) ; R = If ( PubMedArticles::Category = "Results" ; TextStyleAdd ( "Results: " ; Bold ) & PubMedArticles::Text & "¶" ; "" ) ; C = If ( PubMedA…

Hello. I need to find a way to extract a value from a valuelist based on a partial match only. For example, the valuelist contains the following data: r123-A2 s123-12x16 t123-50x70 If my search value on the list was "12x16", I would want "s123-12x16" to be returned from the calculation. There would not be any circumstance where more than one match was possible. There would also be the possibility of no matches. Hopefully someone can help as I've tried everything I can think of to no avail. I'm able to do it if I know the exact match, but I can't find a way to get it to work when I only have a partial match. Many thanks in advance..…

How to get the last character on a field? example: job; .the result should be ;

I have a table "Schedules" which shows related records from "Line Items". When viewing a Schedule there is an "Item Count" field which shows how many Line Items are in that Schedule. Each Line Item is marked with simple Y/N Radio button when Invoiced. I would like to have a field on the Schedule that shows how many of the related Line Items have been invoiced. I can do it with a script but would prefer a calculation if possible...but I just can't work out how.

hi! I couldn't find a better area for this topic. But I have a variable $RowList which is defined to contain numerical values in List(FieldA; FieldB; FieldC; FieldD,..., FieldK). That is inside a Loop ($i as a counter). I would like to do some testing for each of them in turn (like: If (GetValue($RowList; $i) > x) then set $RowList value $i to something else). And then return the modified whole list back to a variable. Is that possible?

Is there a way to accomplish this? I.E. Total = 120 Value = 50, 60, 60 result should be = 60, 60 or Total = 100 value = 30; 50, 60, 20, 20 result = 60, 20, 20

My Filemaker Pro 11 solution uses a layout that features 31 calculated container fields representing days of the month. A "Case" calculation has 12 different scenarios to display each month-day container records 29px x 15px field as either "Red" or "Green". My problem is long calculation time for the layout to refresh. I did not realize that a container field cannot be indexed. Also, the red and green object container fields used in the month-day calculation are global containers which cannot be indexed. I struggle to find a solution to this problem. Ideas would be greatly appreciated.

I have a parent table which has 3 child tables all of which have a date field. I would like to have a field in the parent table which tells me what is the latest date in the 3 child tables so I know the last time there was activity relating to the parent record. I can figure it out.

I need to count the 2 identical record as one.

Good evening to the team, I would like your help I have a group of puppies starting their first 42-day vaccination, the second at 57 days, the third at 72 days, the fourth at 93 days, and the fifth at 120 days on specific physical dates. This table is on a table. At the other table are the puppies listed and the same vaccines appear next to the name and photo of each puppy. What I am trying to do is when a puppy is adopted before a vaccine is adopted or after which he will vaccinate the vaccines that have left the rest of the vaccine unchecked from the date of adoption. I probably don't use IsEmpty correctly. The information I use to achieve this is the date of vacc…

Hi, I need to calculate Interest Rate based on the following variables: Payment Present value Future value Term Payments in advance or arrears This is easy to do in Excel but I hav not been able to find an answer on how to do this. I looked for Custom Function and API options. Does anyone have a sample file or could point me to a solution? Thanks in advance.

I feel I am nearly there with the Case Function to define date periods, (UK date system), with: Case ( (Date of Referral to Regulatory Body) < "1/4/2008"; -3; (Date of Referral to Regulatory Body) < "1/4/2009"; -2; (Date of Referral to Regulatory Body) < "1/4/2010"; -1; (Date of Referral to Regulatory Body) < "1/4/2011"; 0; (Date of Referral to Regulatory Body) < "1/4/2012"; 1; (Date of Referral to Regulatory Body) < "1/4/2013"; 2; (Date of Referral to Regulatory Body) < "1/4/2014"; 3; (Date of Referral to Regulatory Body) < "1/4/2015"; 4; (Date of Referral t…

Good day maam and sir, I would like to ask for help about this calculation?if how can I use and apply this one in to one field? This is for water billing app, Calculation is : Previous Reading-Present Reading=Consumption Consumption =Amount Due The Consumption is calculated result from below calculations. But it is depend if the Connection Type is Residential,Commercial or Bulk. If type is Commercial “C” Case(Consumption>=36;(((((Consumption-15)-10)-10)*21.56)+43.13+945);If(Consumption>=26;((((Consumption-15)-10)*34.5)+600);If(Consumption>=16;(((Consumption-15)*30)+300);If(Consumption>=15;(300);If(Consumption…

Wow. I was unable to make this work - I need someone to help guide me in the right direction, I figure it should be easy enough. Here is an example of what I need to achieve: - Imagine a simple online ordering system. - I have a table called Assignment where I have records with two fields: Assignment::Client and Assignment::Agent. The idea is every agent can work with only certain clients. I use the agent's account name to create this table. If a matching Client-Agent record exists, the Agent shall be able to see orders from that client; if no such record exists, then the agent should not see that client's orders. Throughout my solution, I use $$accountn…

Hi, I've been struggling with the best way to explain this question, but here goes: When defining a calculation in a field, is there a way to "get" the name of that field? For example: If I have a calculation field named "Tango10" and another field named "Tango20" is there a way to have get the name of the field calculation in those fields such that I could do the following: Calculation field = 10*right(getfieldname,2) Where the getfieldname would be the function that I'm looking for and the result for fieldname Tango10 would be 100 and fieldname Tango20 would be 200? So basically, I'd like to be able to use the name of the field in a calcula…

Hopefully this is the right place for this question. I have a calculation field that results in either a number or a number (e.g. "1.2" with a less than sign in front of it (e.g., "<1.1"). I have the calculation round the number to 1 decimal place. The calculation works but the if the result is "0.9", the field only displays ".9". How can I force the field to show the leading zero on the decimal (e.g., "0.9" instead of ".9")? I can't use the inspector to format the result as a decimal, because that removes all of the values with a less than sign entirely (i.e., a field that should appear as "<1.1" will be displayed as empty. So my questions: 1) wha…

What's the process of getting lat and lon base on the address entered to filemaker.

I have a half dozen possible plan-view conditions for cabinet boxes. The cabinets can have an exposed end, abut a wall or appliance or simply be adjacent to another cabinet. I have some standard drawings that depict these conditions. Should I store these drawings in repeating container fields on another table and call them with a calculation or I should I simply stack them on top of each other and control their visibility with a calculation? Which approach would require less data to move down from a server?

I'm trying to find out how to apply a time stamp to all the open records being browsed when I run a script. Currently I search all my invoices for items that have not been payed. I then hit a button to run a script to email all open invoices. A time stamp is added to show that it has been emailed. Right now my script only applies the time stamp to the first record. All are being emailed however. Any ideas or help how I can get the time stamp for all the records being emailed. Script calculation is below: Insert Calculated Result [Select; Invoicing:: Emailed Timestamp; Get (currentHostTimeStamp)] Go to Layout ["Invprint" (invoicing)] S…

Is there a way to improve process? It takes time to get the lat and long.

My cabinet shop builds window bench seating for kitchens. I am ultimately trying to determine overall projection into the room. The basic parameters are angle of the back (A), projection from the wall (B) and height to the window sill (C). What I am trying to calculate is how deep the base portion (D) would be. I am thinking this has something to do with the pythagorean theorem but my math skills are not big enough to be sure. Angle (A) is typically 10º though can vary. Is there a way to derive Dimension D from a filemaker calculation?

On the panel order form, where it shows the drill spacing, can we have the sizes rounded to the nearest 1/8". i.e. For an 99" opening where we've determined the drill spacing to be 11.375" or 11 3/8", it should be shown as 4" - 15 3/8" - 26 3/4" - 38 1/8" - 49 1/2" - 60 7/8" - 72 1/4" - 83 5/8" - 95" - 99"

I made a field called FINAL REPORT adjusted on final print layout , my question is "can we adjust the field with changing of global parameters adjusting its size up down left right width and height positions " hope u got my point

We are building a database of all South African wineries and their wines. In the wine's text description, you would typically enter "Zevenwacht Sauvignon Blanc" and in the next field "Zevenwacht Chenin Blanc", etc. This requires typing names like "Sauvignon Blanc" thousands of times. In the Mac OS System Preferences, Keyboard can be assigned to replace any typed "SB" with "Sauvignon Blanc" (for example). Filemaker 16 however does not recognise this solution. Is there an easy way to achieve this within Filemaker – or get it to recognise the OS replacements?

Hello FmForums! Newbie here. It's been about 6 months of learning filemaker - I've been using forums, Youtube and good ol' Google for my learning. However, I am now scratching my head and can't find a solution. So, I am making my first post 😅. My issue: I have many records in the database and now I need to create another field. This field will calculate a product's selling price using the case formula as a pricing table. However I want the field to be an auto enter calc so I can change the field manually after if needed. The calculation uses a couple of existing fields, which have already been entered for every record in the database. How do I force an auto…

UK Stamp Duty Calculator 2019 Hi, does anyone know how to convert an excel formula to a filmmaker calculation? To be honest I'm looking for a calculation for UK Stamp Duty and although there are plenty of excel files to use, I don't know how to do this in Filemaker Pro. There are two types of calculations depending on if it's your 1st home or 2nd Home. See below. 1st home: =SUMPRODUCT(--(B3>{125000;250000;925000;1500000}), (B3-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02}) 2nd home =SUMPRODUCT(--(B3>{125000;250000;925000;1500000}), (B3-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(B3*0.03) Thank you

I am looking for some help in trying to help calculate the difference. I have 3 time summary fields that summarize hours worked in a sub summary. IE Regular hours worked 36:08:00 and overtime worked 4:00:00 I tried creating a calculation field to add these together but I just get a huge number and no actually an hours breakout. Any thoughts would be greatly appreciated. Thank you!

Here's my scenario, I need to divide a number that could result as nearest as 12 but nut exceed to 12 Example 50 / 5 = 10 70 / 7 = 11.66 What i need is the divisor to put so the result would be as nearest as 12 not equal or greater than 12.

Greets, all: I'm > this < close but just can't seem to nail it. I'm using a value list that concatenates from another table the values of city, state, and country--that's being used as a key--so it looks like this: Lewiston|ID|US Coeur d'Alene|ID|US Boise|ID|US As part of another calculation, I want it to extract whatever the two-letter state code is ("ID", in these examples) irrespective of the length of the city name. I got this far: Middle ( JOB::Company_City__lxt ; Position ( JOB::Company_City__lxt; "|" ; 1 ; 1 ) ; 3 ) ...but I can't figure out a way to dump the pipe ("|") character so I don't end up with "|ID". Also,…

Hi, my name is Josh and I'm new to Filemaker. Could someone help me with this? As a starter project I made an inventory list for tools. I have two related tables: tools and variants. I want all my tools and their related variants in a report grouped by categories and subcategories. The categories are grouped by subsummaries. The subcategories however are displayed next to the tools. Therefore I always want to hide all subsummaries but the first. I was able to hide the subcategories with this: let ( rn = get(recordnumber) ; case ( rn > 1 ; variants::id = GetNthRecord ( variants::id ; rn - 1 ) ) ) However this hides only the subcategories on a product by…

Hi I need some help on the calculation of record count of related record in the Main page where related records from a single input form. Thanks in advance Ken Chan TEST.fmp12

I have a list such as: (B) KARR SECURITY [PS] $100 (B) PPM Gross [$1270 * 5%] = $63.5 (B) Warranty $50 Looking for suggestion and help to grab the last word in each line and calculate the total of the list. IE: $100 + $63.5 + $50 return result $213.50 Thanks

Hello, I have a number field where I want the user to be able to enter things like: 42 (only item 42) 12+82 (only items 12 and 82) 1-5 (items 1 through 5) 31,33,35,37 (only items 31,33,35,37) So I am need of a way for filemaker to deal with the operators '+' '-' and ',' Do you have any advice on how I can separate out the item numbers? Thanks!

Hi, I am trying to sort a calculation which is solved by the following. "field a" has a range between 0001 and 9999 so if "field a" is between 0001 and 0999 I need to give field b a result of "yes" i tried this. If ( fieldA ≥ 1 & fieldA ≤ 999; "yes"; "" ) but it did'nt work.

I am having a problem where $$Variable is evaluating as less than Table:Field where it shouldn't. For example Table:Field is 65 and $$Variable is 100. Anything up to $$Variable=99.9999999 will evaluate as greater than 65, but any 3 digit entry (100 or above) evaluates as less than 65. Table:Field is a number, the only way I could see this happening is if $$Variable is typed as text, but I cannot control it. $$Variable is entered in a Custom Dialog Box.

I have a problem trying to use a calculation to pull information from a related table that hopefully someone here can point me in the right direction to solving. I have two tables, related by a barcode field. On the first table (PendingOrders) I have a list of items that I have ordered, how many were purchased, and a projected arrival date. Any given item (same barcode #) may appear on the table multiple times with different projected arrival dates On a second table, I'm trying to add calculation fields that will tell me how much of each item I have projected to arrive this week, how much next week, and how much is expected to arrive in 3 weeks (as well as many …

I'm looking for a field calculation that will break a number into groups of 4 digits, each group separated by a space. The number can have 5 or more digits (say up to 16). For example: XXXXXXXXXXXXXXXX to XXXX XXXX XXXX XXXX

In a hobby solution I work on, one part is a reoccurring medicine-event where one or more medications should be checked as "Done" in conjunction with the medicine being taken. Finally, you should check the medicine event "Done" as a whole. I would like that this last part was done automatically when you have checked/taken all medications in that event. The basic structure is an Event-table and an Object (medicine) table with a join table in between. A row in the event table is a medication event and one or more medications are added to the event via the join table. A field in the joint table is called “Done”, it is a boolean field where 0 = not done and 1 = do…

Hi Guys I need some advice on how to calculate this. I tried using CASE and it doesn't seems like it work. The Period In Days will increase by day. Depending on the figure in the PERIOD IN DAYS, ie. 16 the <15 will show ZERO. And the <30 will display the stk balance. Case ( PERIOD IN DAYS ≤ 15; STOCK BALANCE;0; PERIOD IN DAYS ≤ 30; STOCK BALANCE;0; ) I did and excel of how should it look like in XLS. Thanks in advance Ken

Does anyone have an idea how to strip everything invisible and just get the visible text in a field back? I tried various functions and still don't get why it's not possible to zap those gremlins natively or at all. And, NO! A CF containing every char to be removed is no solution. At least until EVERY SINGLE invisible char is included. Is there a list of those somewhere? @comment has posted a CF and although this solves the problem as such, it also strips out other chars.

Hi, I am extracting data (script loop) in to a portal, then make some calculation and summary fields to make calculation. So first I loop, extract data, calcul data, then I summary it, then I make another calculation based on previous steps, next loop, and so on. Problem = When I loop the extraction I can't see the result of some calculation until i cancel the calculation scrip and click on a random grey space. How can I do to get the results during the loop and not when I cancel and click somewhere. Also if I leave the layout and go back I ll have all result as needed, but I need it during the loop in a script. for exemple table "forex1" have "1,3,2,2,3,1,4,5,3…

I realise this topic has been touched on before, however... I need to hide an object on my layout for a membership database. To do it, I need to meet the true and/or false values from two fields, 'Category' and 'Status', both of which are pop up menus. As I'm very new to the whole scripting and calculations thing in FM, I have literally spent hours today Googling solutions. These two that I've put together work perfectly when they're run separately: FilterValues ( MEMBER::Category ; "All¶Donor¶Friend¶Navara¶Contact¶Potential Navara Funder¶Mailing List" ) ≠ "" FilterValues ( MEMBER::Status ; "Inactive" ) ≠ "" But try and put them together to run one after th…

Having a major crisis lol... I have a script that downloads XML, and then parses it out. Worked fine forever, now it says it can't calculate it because it's over 30,000 characters. Anyone know where to start? Thanks!

I would like to create a heat map with data from a related table. In table 1 different records with a field with a value. A field with hour of the day. A field with day of the week. In another table I would like (in 1 global record) an overview with times per day with a field containing the average value of that hour of the day of the week. What is the best way to do this? Thanks

I'm looking for some help with a script for final keywords that needs to substitute keywords from a related table. This was working fine until I exceeded 1000 records in the related table that the replacement keywords come from. Apparently Filemaker has a 1000 row or record limit for this kind of script. Does anyone know a work-around for this? Here's the script I'm using:

Hello, Can this function able to be replicated through filemaker? or do we need webviewer to use this function?

Hello, im a little stuck. in a DB, I have a field that is a calculation of three other fields, [length], [connector.type], [pairs]. An example in that field might be “100’ G3 15pr” Im trying to do a report that gives me the found count of every value that the calculation field has. Basically, how many “100’ G3 15pr” do I have in my DB Im sure it’s an easy find, but I’m drawing a blank. Is this a simple function I can do in a report, or is it a scripted thing? Can anyone point me in the right direction please? Thank you, Tony

Apologies for this newbie question, it seems like it should be obvious, but.... Database, two fields Customers and Jobs, relationship by Customer ID. When a new Job is created you need to choose a customer, I want to choose the Customer by Customer Name but have the relationship by Customer ID - ie choosing the Customer only depends on knowing the Customer Name, not the Customer ID Or do I need to make the relationship on Customer Name to be able to choose Customer Name in the Jobs layout?

I have a layout summarising advertising activity and ticket sales for multiple events. I have tried a number of things. First, in order to show dates (over a defined date range) that activity and sales occur I use a calculation field called "zs_GanttChart_rt": Let ([Current_Date = Extend ( zv_StartDate_gd ) + Get (CalculationRepetitionNumber)]; Case ( IsEmpty ( Refiner_1A ) and Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_Start) +2; Extend (Z_colour); Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_End) +2; Extend (Z_colour) )) That works fine. Then I try to put sales figures in. First I …

Hi, I want to have an opening balance of my product stock or any customer account but i cannot get the idea of what calculation can be used. I am using a list view of the products where the balances are coming from the transaction table. Required E.g For Products Stock (The opening calculation idea is required. Product Opening Stock (Required) Purchased Sold Balance APPLES 10 08 02 16 ORANGES 50 90 30 110

I have an iPad app that uses 2 databases. One is mostly for the UI but has some tables with data, and the other is predominantly an image repository that is used by the first database. The reason for this separation is that I don't have to redownload all the images (400 or so at the moment) every time I do an update to the app. The Image repository only has a couple of tables and barely any scripts or UI components. The UI part is only 1.5MB or so, as opposed to 150MB for the images. When loading images (which is done from the UI app which has the image repository set up as a data source) I use auto enter calcs to extract some data from the images (resolution, image …

Hi, I have a creation date field and I would like to add a year to it so if it was created on 01/01/2019 it actually says 01/01/2020. I'm relatively new to FM so I am a bit puzzled as to what to do Thanks in advance for any help

I am trying to write a script to permit me to respond to emails received into fmp. I have successfully written the calculation to extract the email address from the "from" field in which the data is presented "Joes Bloggs <[email protected]>. I cannot work out how to do the same for the CC field. The data is in the same format but there are typically multiple CC recipients and so I need to extract the text between <> sequentially. Any clues?

I have a database of daily records that each contain a start and stop time. I would like to calculate the time elapsed from each day's first record start time to the last record end time. I've played around with calculations, but am having trouble referencing the first and last daily records. Any help appreciated!

I need help pulling data on field check;L10; result should be L10 or check;check;L1 result should be L1 .

I am struggling to do something simple, which is to define a field that will display the filename of files stored ion a container field called 'Document'. I am using the GetContainerAttribute(sourceField;attributeName) function, but I can't seem to get the engine to recognize the field, which I'm entering by double-clicking it in the field list! I've tried using different attribute names, and tried doing it on other container fields in different files, but there's something I'm just not getting here . . . can anyone spot my problem in this image of my attempt?

I tried few calculation in Filemaker like substitute trim etc but did not get same result. Google sheets has nice function JOINTEXT any help to replicate/convert the function in Filemaker calculation.

Hi all Is there a calculation to remove all long text strings (say anything over 20 characters) from text fields? Thanks

Hello - I'm new to SQL and Filemaker calculations, so forgive me if this is completely remedial. I'd be appreciative of any resources you think might be helpful. My team is responsible for course scheduling in higher education, and we have an outdated student system that, while great for data entry, is limited in its view options. We use a filemaker as a tool for viewing course details from different tables in aggregate and in relation to each other. For this calculation purpose, we have two tables, "data" and "CRN". Data contains the only information in our filemaker database that doesn't exist anywhere else (notes, calculated fields, etc.). The table CRN…

Why does Help say this when i try Get ( SystemPlatform ) it returns 1 also so why the Abs()?

Hi all How can I get my calculated Base64Decode container field to display some centralised (vertically and horiztonally) text, and have the entire background a certain colour? Thanks

Greets, all: I'm using this calculation--in a parent table in a field temporarily named 'Temp'--to grab course numbers from the course_number field in a related/child table: UniqueValues ( List ( ASSIGNMENT_COURSE::Course_Number__lxt ) ) (I'm using this calc because the child table contains multiple records with the same course number; it's not a key field.) My intent is to use six buttons--in the parent table--where each button would grab an assigned row's value using the calculation above. For example, say that the above calc has this result: AST-100 EDU-100 EDU-300 PHY-100 SOC-100 TMA-001 I want Button 1 to grab AS…

Hi guys I'm sending the BE_ExecuteSystemCommand provided by the BaseElements plugin. I get a result back, however there are no carriage returns (which would be displayed if I ran the commadn in cmd.exe). If I copy the result to the clipboard using BE_ClipboardSetText (format CF_Text) then paste the results in to notepad then the carriage returns are there. What character is it using as the carriage return in the first place? Thanks Turns out it is unicode character 10 (line feed / LF). I used the following calc to sort: Substitute ( $cmd; Char ( 10 ) ; ¶ )

I need all users in a solution to be able to read data from a table in an external Filemaker data source. I cannot create every user again in the external file. I have created a user in the external file that has the needed access. As I understand it my users would get a password dialog when trying to access the external data but the users, of course, doesn't have the password. What is the recommended way to accomplish this?

I have two files called "signup.fmp12" and "2000-words.fmp12". I am using FMPro16 on MacOSX 10.13.3. "2000-words.fmp12" has ABOUT 1950 records with only 2 text fields: one called "serial_number" and the other called "word". Each contain unique values. I want to add a calculation field called "pword" to "signup.fmp12" which fetches one random word from "2000-words.fmp12" So each record in "signup.fmp12" will have one randomly-different word in the field "pword". (Strict randomness is not essential) I am unsure how to "connect" these 2 files so that this can be done. Could some kind person please give me some nice clear "idiot-proof" instructions o…

I am not sure if this question belongs in the Calculation Engine forum or the Relationship forum. This extends a question I posted earlier this week. It involves multiplying summary fields X summary fields.scope of work.fmp12scope of work.fmp12 I am developing a database for producing bid-cost estimates for my cabinet shop. I have a table that produces a graphical synopsis of the cabinets that we need to bid on. This SYNOPSIS table tracks how many doors, drawer boxes, faceframes etc are contained in each cabinet and keeps a running total of how many items are required for all the cabinets. A second table tracks labor costs for building various components. …

Hello all, I'm new to FileMaker and I am using it to record collection information associated with insect specimens I am studying (where was the specimen collected, when was it collected and by whom, etc.). I'm trying to generate a “specimens examined” report for each insect species that includes all of the collection information in a single block of text. For example, I'd like to take the following 6 records: and in another layout generate a text report such as this: Apis mellifera MEXICO: Chihuahua: Chihuahua: Chihuahua, 26 Aug 1978, A. Cortez (1 female; BMNH). USA: Arizona: Cochise: Tombstone, 30 Jun 1881, W. Earp (1 …

Hello Guys. I want to create a Calculation that will do the following Each worker is entitled to 45 days holiday per year The standard annual leave entitlement for a full-time member of staff is 45 days including public holidays and customary days. I would just like to be able to put his start date in a filed and then automatically generate how many days holiday he would be entitled and how many days remaining and the end date I want to keep a history table for each worker when he took his leave and how many days he have. Like this 1- Emp_Id emp_name , start_date , end_date , total_days, rem_dates Hope this is enough info. Thanks in advance.

I have a field called 'input' an example of whose content is shown below. I need to extract 8 pieces of information out of this, each into 8 separate fields named as follows: extract_entry_number extract_date_submitted extract_region_chosen extract_headline extract_wording extract_filename extract_email Can you please provide the best/easiest way (calculation?function?) for each of these 8 fields to be populated from the content of 'input' (Note: any of the 8 pieces of data 'input' can be of arbitrary lengths in different records) (Note: lines of text starting "Entry #:" and "Date Submitted:" and "Which" and "and which" and "Create a" and "You…

Hi, I have 3 calculations that I would ideally like to concatenate into one, but can't work out how to do it. How can it be done? TextFormatRemove ( MiddleWords ( Self ; 1 ; Length ( Self ) ) ) Substitute(Home_Address_1 ; ["ch. "; "chemin "] ; [" "; " "] ; ["-Au-"; "-au-"] ; ["Ch. "; "chemin "] ; ["ch. "; "chemin "] ; ["Chateau"; "Château"] ; ["chateau"; "Château"] ; ["Ch "; "chemin "] ; ["av "; "avenue "] ; ["av. "; "avenue "] ; ["Av. "; "avenue "] ; ["Av "; "avenue "] ; ["ave. "; "avenue "] ; ["ave. "; "avenue "] ; ["Ave. "; "avenue "] ; ["Ave "; "avenue "] ; ["pl. ";"place "] ; ["Pl. ";"place "] ; ["BIS ";"Bis"] ; ["bd ";"boulevard "] ; ["bd. ";"b…

Hi, the goal is to monitor and compare results when a horse changes racecourses. I want to grab a few of the field values from the previous record and insert them in the following record when there is a track change. I’ve seen similar requests for help on getting info from previous record, but was unable to adapt the solutions offered up. In the image I’ve added text fields (last Race_ID, last track, last class, last fin) for demo purposes. The ‘change’ field should be temporary, but I suppose I could live with it. The ‘mark’ field was part of my effort to have fmp do it. There are 3 tables working here in the image ‘Results’ ‘Races’ and ‘Horses’. But, t…

I have always developed with FileMaker for windows. I have bought myself a MacBook Air and love it. So now I am using FileMaker 16 Pro advance for MAC. I know that MAC does not label their directories like windows does. So I am having problems figuring out to get the right coding to access my photos. I have a folder called "Photos" that reside on the C: drive in windows. Then I reference that folder so pictures will populate automatically when I put in a record. Here is what I have on the windows fileMaker: ImagePath: Let ( [ // Transform the contents of a container field named Images to text ImageRef = GetAsText ( ImagePath); Photos = Position ( ImageRef; "/";…

Greets, everybody! Please refer to the attached. What I want to do is set a conditional formatting calc so that if there's an overlap of time for a reserved court (as shown in the attachment), the records within that overlap will turn red to signal the user that there's a time conflict. How do I pull this off? (I have yet to figure how to prevent that from happening in the first place, but I'm guessing I could re-engineer the needed calc so that when a new record is created, it will search for a time conflict before being recorded.) I'm guessing that the answer involves converting the date and time into a concatenated string of numbers then using the MIN and MAX…

I'm using the following function to pull text from a text field. The text is about 30-40 lines long. How do I text parse until the end of the text? I tried using "" as the end suffix but I am now returning nothing. Thank you Let ( [ Textfield = BWIA2 FMP11 2::Web Quote Entry Hangar ; prefix = "comments:" ; suffix = "" ; start = Position ( Textfield ; prefix ; 1 ; 1 ) + Length ( prefix ) ; end = Position ( Textfield ; suffix ; start ; 1 ) ] ; Middle ( Textfield ; start ; end - start ) )

Hi All, I have a main table and a secondary table called users. They share a field called Producer number. Within the main table, which contains thousands of customer files, each customer has an assigned producer number. Within the users table, all of our producers (9) have individual records and are numbered 1-9. I'm looking to create a script for our new business website leads that when a new quote request email comes in, filemaker can sequentially assign web quotes based on the day. So, for example, the first quote request would go to producer 1, the script would then check how many quote requests each producer has received for the day and distribute eve…

I'm trying to create a dashboard that shows how many open items per department. I have a 3 tables, Orders (Parent Table), Assigned_to (a child table to orders), and a personnel table which lists the person's name and department My Tables and Fields: Orders Assigned_to Personnel OrderNum_key OrdersNum_fkey Personnel_Key Open_Date Personnel_fkey Name Close_Date Department# Static 1 Relationshi…

Hi, I m trying to get data from a range of 20 previous record in a calculation field, I used the fonction : GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -1)& ¶ & GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -2)& ¶ & .....& ¶ & GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -20) but in purpose to find the best result in my research I need to add or reduce the number of previous record, and there is 22 field with 20 previous record. So imagine the work that I had to do to edit every field. I thought maybe it s possible to make a loop ? or something else that't I don't know ? I know this screen is wrong but m…

Hi All, Please could you assist? I want to select a date from the drop down calendar but only want to display Month and year for example: April 2019 How do I accomplish this?

I'm a newbie. I am trying to create a checkbox that, if checked, the text in an edit box next to it will go from black to grey. I have spent hours trying to figure this out. Here is what I have: Filemaker won't accept this for me to see if it is correct or not. I keep getting the following message. What am I doing wrong? No matter what I do I get this message. I have created a file just to practice this. It only has 1 table - the one in the photo and I've clicked on those fields on the left to populate the formula. Thanks for any help!

Have a form for creating classes. In it have four fields. name of class (formatted as text) date of class (field formatted as Date) start time of class (formatted as Time) end time of class (formatted as Time) How do I concatenate so that they can show up on a form as "1/1/2019 1:00pm-2:00pm". I was able to use GetAsText function on the date and time fields so I could get concatenation but now they read "1/1/2019 13:00pm-14:00pm "

i could decrypt a filed plz help me where am i going wrong made text field with text and enc and dec fields but using function both fileds are not same ENC-DEC.fmp12

Hey... I know the answer to this is super easy, feeling brain dead, but I think I have done this before ages ago and just forgot how. How do I find and replace a text value in a record in all found sets? Obviously, I am look to do this in all records instantly, not one at a time. : ) Thanks, Mike

Hi, Using TextStyleAdd, I want to change the style of a single letter at the beginning of a sentence. For example: // SOAP [ "S:" ; TextStyleAdd ( "S:" ; Bold ) ] ; [ "O:" ; TextStyleAdd ( "O:" ; Bold ) ] ; [ "A:" ; TextStyleAdd ( "A:" ; Bold ) ] ; [ "P:" ; TextStyleAdd ( "P:" ; Bold ) ] ; But I get this: P: - P: (correct) SOAP: - SOAP: (incorrect) SOFA: - SOFA: (incorrect) Is there a way of adding search constraints to TextStyleAdd? Or, is there a hidden character at the beginning of each line I can capture? All the very best, Daniel

I have a tool with the following fields. studentDOB - a date field tracking birthdate studentDOBMonthName - a calculation field returning text result - MonthName (studentDOB) studentDOBMonthNumber - a calculation field returning number result - Month (studentDOB) The DOB field works fine. But the other two fields return question marks. This is an FMPA 17 file, hosted on FMS17, accessed on macOS 10.14.2. Any help would be appreciated.

I have imported a large number of records but all the names of individuals are in all caps. I would like to create a calculation field that changes the names to lower case but with the first letter capitlaized. I don't think I would want to run a script to do this unless necessary.

Hello to all, I am building a solution, where the user needs to see a crosstab absence sheet of their personell. The people there can be absent for a variety of reasons, (absence types) which are recorded. The users want to be able to select a date and see the absent personell for a certain date range. I tried very hard to find the a way to do this, so that the app will be responsive. The solution is accessed via WebDirect (so I tried to avoid the Window Refresh script step) So far I have come with two ways to do this. The first way is based on a repeating calc and the second one on a summary field (which summarizes a repeating calc) See the att…

Hi there, I want to set a date field that upon a click will jump one month like this example : 11-24-2018 to 12-24-2018 to 01-24-2019 etc... Thanks in advance!

Hi, I am trying to establish some simple code using a text calculation. The calc I have written goes like this : "[" & #_security_name & "]¶" & "security_name = " & #_security_name & "¶" & "security_type = " & #_security_type & "¶" & "account = " & #_account & "¶" & "sar_timeframe = " & #_sar_timeframe & "¶" & "sar_timeframe_switch = " & "#_sar_timeframe_switch" & ¶ & "sar_periods = {""1m"": ""1D"", ""2m"": ""1D"", ""3m"": ""1D"", ""5m"": ""1D"", ""10m"": ""1D"", ""15m"": ""1D"", ""20m"": ""1D"", ""30m"": ""3D"", ""60m"": ""1D"", ""120m"": ""1D"", ""240m"": ""2D"", ""1D"": ""5D""}" &am…

Greets, all: Is there a way to alphabetize values when using the List function? If you open the attached quick-'n'-dirty solution I'm building for my wife (Click a recipe [Multigrain...] then click VIEW button at top), you'll see the list of ingredients but I'd like them alphabetized. Note that the VIEW page will end up becoming printable, so no portals because they won't be able to self-expand to accommodate a long ingredients list. I'm sure the answer's simple...which is a good thing since I'm simple-minded, anyway. TIA for your help! Grace Recipes.fmp12

Looking to do a calculation of a transaction fee that is based on a fixed percent plus $0.11. This is determined by the date the transaction comes through one. So registrants are paying $325 initially which gets broken up in the database as $75 registration fee and $250 tuition, both on the same date. I can easily add those together and calculate one transaction fee, but there will be separate payments coming in the future that are on a different date and need to get assesses the transaction fee separately. Right now there is a transaction table and a student table that are joined by an ID. Any thoughts about how best to proceed? Thanks, Dan

Hi, Singles::PSBestOptA = "Same" "Singles::PSBestOptA produces the following; a "0" or "Same" or "Best" and I've test with "number" and "text" When "Singles::PSBestOptA" has a "0" in it, FileMaker allows ... meaning "0" = "Same" ?? what, impossible but I've tested it. Changing "Same" to "t" FileMaker does not allow or match or whatever is going on ... ???? I need understanding here. FileMaker should not allow; "0" = "Same" yet it does ??? Can someone help me with this ...