Calculations and Define Fields
Calculations, enough said! ...
1,804 topics in this forum
-
Case(0;0)?
by neufere- 1 follower
- 1 reply
- 897 views
In a Filemaker app we purchased that was originally written quite a while ago, there are a number of fields and variables set to the value Case(0;0). Does anyone know what this accomplishes and why it would be done? Thx.
-
Simple "If" calculation
by smb avi- 0 replies
- 237 views
I have very simple FMP files for a few clients. For one, I manage a credentialing program. When someone passes the credentialing test, I enter a "1" in the "C Passed" field (or if they have tried multiple times, it might be a number greater than 1). What I want FMP to do is automatically enter a check box in the "C Installer Web" field. Currently, I enter the check mark manually (the field has a value of "Y" so for reports it shows the "Y"). But it just seems that once I enter the number in the C Passed field it could automatically enter the result in the C Installer Web field. This is what I came up with but it doesn't work. I also tried a Y instead of the x …
-
Search for latest occurrence
by Randy Bricker- 5 replies
- 279 views
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…
-
Long text calc for JSON randomly drops text elements
by naplesweb- 1 reply
- 180 views
I have a fairly long text calculation I use to produce JSON from a database with lots of little text elements and a few lookups etc. Not very complex, but there are lots of lines like the sample below. I use Filemaker to dynamically create JSON post info that I feed into "JMeter" - an automation and testing tool. The issue is that the result will sometimes - seemingly randomly - omit a data element in the calculation's resulting text. Curious if anyone experienced anything like this... Randomly dropping elements often results in invalid JSON and always in some part of the data not posting - in sometimes very subtle ways. Additionally strange: I put lot…
-
Calculation for a conditional based on groups in a portal
by JammerHammer- 0 replies
- 142 views
I'm trying to figure out how to calculate the last (or first) record in a portal sorted by group for a conditional. So I've got a portal which sorts all my records by group. I'd like to create a visual separator line between each group in the portal. I'm thinking that I create a red line that sits in each portal that's hidden in all records except the first or last in a group. It seems like it should pretty easy but I can quite figure it out. Thanks in advance for your help in pointing me in the right direction.
-
- 0 replies
- 117 views
Hey Guys, So I am working on my brewing recipe guide and I am trying to create an "IBU" calculation. So here is the formula i am trying to use, working with a 5 gallon batch: Ounces x Alpha Acid x Percentage Utilization(boil time) divided by 7.25. Percentage Hop Utilization for a 5 gallon batch of homebrew31% over 60 Minutes30% 51 to 60 Minutes28.1% 46 to 50 Minutes26.9% 45 to 41 Minutes22.8% 40 to 36 Minutes18.8% 31 to 35 Minutes15.3% 26 to 30 Minutes12.1% 21 to 25 Minutes 10.1% 16 to 20 Minutes8% 11 to 15 Minutes6% 6 to 10 Minutes5% Up to 5 Minutes So a few questions: 1. Would I have to create a …
-
- 2 replies
- 279 views
Hey guys, I am very new to filemaker pro and I currently am working on a few projects. The example I will use to ask my question will be from a hobby of mine. I have created a brewing worksheet and I have an issue that I would like to get solved without having to create dozens of fields. I am trying to create a database for the different beers that I am brewing and am trying to have it display recipies for each record. Example, I have created a "Hop Ingredients" Field, but I need multiples of that field since there are different kinds of hops I am using. My problem is when I make copies of the field, when I select one in browse mode, all of the field of t…
-
Average of
by Rui Gonçalves- 1 follower
- 1 reply
- 214 views
Hello People! Hope you are all safe I’m using this formula to get years and months, and it works fine Year ( Get ( CurrentDate ) ) - Year ( Data Nasc ) - If ( Get ( CurrentDate ) < Date ( Month ( Data Nasc ) ; Day ( Data Nasc ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) & "." & Mod ( Month ( Get ( CurrentDate ) ) - Month ( Data Nasc) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( Data Nasc ) ; 1 ; 0 ) ; 12 ) Now, I tried using the Summary Field – Average of, to get the average age of agroup and the result comes out 25.70, 30.25 and so on How can I get the months under 12
-
- 2 replies
- 250 views
Database has over 1000 records; one table and a dozen or so fields. Each record is one of a continuous sequence of timed events. Each record has an event Start_time field but not an end time. The Start_time field is of type Time resulting in H:MM:SS format Events are minutes long and always within one day. I would like each record to have a Duration field of type Time that calculates event duration (MM:SS) by subtracting the event Start_time of the current record from the event Start_time of the next sequential record. Only need to do this once to populate all the Duration fields saving me the time, date entry effort and errors of a thousand manual calcul…
-
- 5 replies
- 495 views
Hi. I need to incorporate parentheses into a text calculation field that loads into a web browser. Does anyone know how to do this? Essentially: "URL" & Address, but I need the address to field to have parentheses around it.
-
Change date format
by steve5504- 1 follower
- 3 replies
- 2.2k views
Is there a way to simply change the date format from mm-dd-yyyy to yyyy-mm-dd? Thanks
-
Future Date
by page19- 6 replies
- 459 views
Hi, I'm working on a FM database for a friend and I've reached a point where I'm stumped as how to do it so was just wondering if some kind person can help me please. What I would like to do is have the database, upon opening, automatically find records based on a date field that is 30 days in the future. For example, if the current date is 01/01/2020 then I would like to create a script that automatically finds all records that have the date 31/01/2020 in a certain field and have a simplified report card (with just 2 or 3 details from the main database) show up displaying which records they are. Any help will be much appreciated. Thanks.
-
- 7 replies
- 1.6k views
I'm posting here to attempt to preserve my hairline - as I've been tugging at my hair since Friday since I'm hoping someone in the community has run into this already. I have log data from a cloud based load-test with a millisecond epoch timestamp like 1582668354014 In Excel, (sorry - I dislike it too) I have found I can get a "human" readable timestamp for this by =((A20/86400000)+DATE(1970,1,1))-TIME(5,0,0) (Where A20 is my timestamp and the -Time(5,0,0) adjusts for GMT-5 (US East)) Excel resolves the above calculation to 43886.7124307176 and if I apply a format as "mm/dd/yyyy h:mm:ss.000 AM/PM" , it displays: 02/25/2020 5:05:54.014 PM - BINGO - 🙂 …
-
- 1 reply
- 257 views
Calculation for field percentage with this condition: If Dept = A; Qty = 20; Percentage (can edit) If Dept = B; Qty on A & B = 20; Percentage on A = 100% then Percentage on B ( can edit) If Dept = C; Qty on A & B & C = 20; Percentage on A = 100% & Percentage on B = 100% then Percentage on C (can edit) Last Dept Percentage can be edit and previous Dept must be 100% Or there is other way for this? Test14.fmp12
-
Average Age over several records
by ClinicArt- 4 replies
- 369 views
Greetings, FM Community, A question from a new beginner: In my Main database, I have defined an "Age" calculation field based on a person's date of birth. It changes or updates automatically. The Age value is "looked up" automatically into a second database for the many people reported in many records. I would like to find the "Average Age" of the people in the records and render this in a Summary part. Any ideas? Simple solutions are best for me...I am a beginner and a little "long in the tooth." Thank you!
-
- 2 replies
- 293 views
Test15::Qty for same ItemNumber example: ItemNumber : 536-880 has Qty: 60 ( has 3 record) ItemNumber : 556-884-01 has Qty 26 (has 3 record) How to get Total Qty for both ItemNumber is 86 (60+26 this is in RED when short by POFes), not (60*3)+(26*3) = 180+78 = 258 The total Qty for all ItemNumber should be 378 not 1103 Test15.fmp12
-
- 0 replies
- 233 views
Hi All, I am building a personal diary. It has 2 tables, Tasktimer (which category, task and times the activity) and Categories (which is a 3 field list of Categories, Tasks and Remarks). I set up a relationship for the 2 tables via a Category field in both tables. This then allowed me to set up dynamic value lists, which I want to filter choices based on what is set in the Tasktimer table. In the Tasktimer table now when choosing a Category, it shows me the list of categories from the Categories table. After choosing a category from the list, the task list is filtered based on the category. I would like the remarks field to auto populate with the filt…
-
Financial calculation based on date of transaction
by derlandson- 1 follower
- 1 reply
- 690 views
I have a table of financial transactions that include dates and ID's. This table has a relationship to a student table via the ID. What I am looking to do is calculate the transaction fee (2% plus $.11) for the student based on the dates of the transactions, When the transactions get created they are broken down into registration fee and tuition payments, but both of those will be on the same date and thus it is the total of those 2 transactions that are assessed the transaction fee (2% plus $.11). There will slo be transactions on future dates that are stand alone transactions (no registration fee) and the transaction fee will need to be calculated. I can sum the tr…
-
Repeating Alphanumeric Strings
by dztrucktion- 5 replies
- 284 views
Hey everyone! I am trying to pull out multiple alphanumeric serial numbers out of a giant text field. The serial number could show up in any number of places, but has a fixed format. I need the calculation to pull all occurrences of the serial number and basically put them in a list, to which I will then be able to create a join field from. Honestly, I have no idea where to even start. The serial number is always going to be 2 Letters and 3 numbers. Source (Text Field): Big and boisertious rock track, but time selling point, Track is a version of AC101, KD205, GR771. Also check FE454/GG123. Result: AC101 KD205 GR771 FE454 …
-
Time calculation
by TanyaI- 3 replies
- 347 views
Hi probably a simple one for you guys. I have a date and time of sample collection, 2 fields one is a date field the other a number field. (Thinking about it it maybe should be a time field.) Then I have a date field and number field for time processed. I want a calculation that will tell me the number of hours between these two points in time. Thanks Tanya
-
- 3 replies
- 344 views
This should be very easy, but Ii can't find how to make a field that shows the TOTAL $ for a number (currency) field for all records (not just for a found set). A SUMMARY field only produces results for a found set, so I assume I need a CALCULATION field, but which calculation? Thanks for any help.
-
Lock a Radio Button
by Kishan- 2 replies
- 511 views
I would like to lock a radio button field after selected action as per attached . After the radio button action is selected and the field is locked, but i still can modified it. Any suggestions? Radio Button.fmp12
-
age with competition
by mbk28- 1 follower
- 6 replies
- 413 views
Hi, I have many tables but 2 in use for age calculation: people and event in the people I have the birthday and in the event I have the date of the event (olympics) and I try to find old is someone for an event. In the file join, you can see the date of birthday, I use the 18/04/1994 not the 18 APR 1994 and the jourmed (day of event) but sometime, I have problem with the February 29 if people born a year with feb 29, don't know how to solve the problem I use this calculation: // This is a Custom Function version of calculations provided in "Advanced FileMaker Pro 5.5" by Chris Moyer and Bob Bowers. This formula differs slightly from th…
-
Need help with calculating implementation date
by Carlos F. Martinez- 7 replies
- 409 views
I need some help on a report. I am trying to find out how to calculate an implementation date which is calculated as start date plus 20 working days. In my company, working days are considered as the standard work week of Monday thru Friday. See attached screen shot. I would greatly appreciate any help on this. Thank you.
-
Need help with count calculations
by Carlos F. Martinez- 3 replies
- 719 views
I need some help in figuring out something similar to the countif function in Excel for a Filemaker database report layout. I have tried using If, Case and even ExecuteSQL but none seem to work for me. I am attaching a screen shot of the report layout with notes on what I am trying to accomplish. The counts and totals would be placed in the footer (the ones in the attachment are images from an Excel file with a similar format). I would greatly appreciate any help on this.
-
External storage for Container Fields
by torch_55- 14 replies
- 1.9k views
I am using FMP 16 and have set up a container field to hold interactive PDF content and to use the external storage option. When I add a PDF to this field, it is stored in three formats (pdf, png & jpg). Is there a way to have it just stored as PDF?
-
- 1 reply
- 768 views
I have a layout with multiple entries of similar data. For example a line of data contains an ID#, a date, and three quantity fields (number of occurances of case A, B and C). I need a count of the number of ID #s in the total population. and a total of the quantities of Fields A, B and C for each ID#. What is the best way to approach this?
-
- 11 replies
- 1.4k views
I am developing a vehicle management solution in whihc I need to display the date of the next coming MOT surveys for a large number of vehicles - in a portal. For this I need to have FM return a date to a field based on the following If a vehicle is a truck it must be surveyd once a year If the vehicle is a car it must be surveyed every second year BUT only if the vehicle is older than four years. I am trying to work this out with a Let function as per below - however I just keep getting stuck in understanding the correct way of construction the Case NB! Personbil and Varebil" is local for car and "Lastbil" is for truck and it would probably be just as easy to …
-
Filled-In Fields From Related Records
by CBarnes- 1 reply
- 1.2k views
I am trying to fill in two fields from related records but cannot figure the correct working way to do it. In my main table (DwellTimes) I have a lot of imported data from an AVL system. One of the fields with data is Stop, which has all of the stop names. I created two fields, StopNumber and Timepoint (the imported data does not have these two fields). I created a table (StopNumbers_Names) that has all of the stop names and the associated stop numbers. I also created another table (Timepoints) that has a list of the stops that are timepoints. Not every stop is a timepoint, that is why I created two tables. 1. I would like the StopNumber field to be filled in from…
-
Summary field not working as it should
by JamieChriso- 3 replies
- 919 views
Hi - please be gentle, I have never posted anything on a forum before and am a newbie! I am having problems with a report and think it is a simple solution. I have a report for a number of different tours and summarise the amount of people on each tour: CountILPax = If (TourNameRefined = "Island Life" ; PaxNumbers) I then have a summary field: SummaryILPax = Total of CountILPax I have a calculation to determine the number of Island Life tours on a particular day: CountILTours = Case ( GetSummary ( SummaryILPax ; TourNameRefined ) ≤ 6 ; 1 ; GetSummary ( SummaryILPax ; TourNameRefined ) ≥ 18 ; 2) The CountILTours calculation works in the Sub Summary P…