Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculations and Define Fields

Calculations, enough said! ...

Followers 1

1,804 topics in this forum

  1. smb avi

    Simple "If" calculation

    by smb avi

    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 …

    smb avi
    Last reply by smb avi,
  2. Randy Bricker

    Search for latest occurrence

    by Randy Bricker

    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…

    Randy Bricker
    Last reply by Randy Bricker,
  3. naplesweb

    Long text calc for JSON randomly drops text elements

    by naplesweb

    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…

    naplesweb
    Last reply by naplesweb,
  4. JammerHammer

    Calculation for a conditional based on groups in a portal

    by JammerHammer

    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.

    JammerHammer
    Last reply by JammerHammer,
  5. agian510

    Calculation Based on Multiple Portal Line Entries

    by agian510

    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 …

    agian510
    Last reply by agian510,
  6. agian510

    Can I copy a field within layouts/individual records display different values

    by agian510

    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…

    agian510
    Last reply by agian510,
  7. Rui Gonçalves

    Average of

    by Rui Gonçalves

    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

    Techphan
    Last reply by Techphan,
  8. DeLuca

    Using Field from NextRecord in CurrentRecord Calculation

    by DeLuca

    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…

    DeLuca
    Last reply by DeLuca,
  9. Tapper

    How to add parentheses to part of a Calc field

    by Tapper

    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.

    AHunter3
    Last reply by AHunter3,
    • Like
  10. steve5504

    Change date format

    by steve5504

    Is there a way to simply change the date format from mm-dd-yyyy to yyyy-mm-dd? Thanks

    Techphan
    Last reply by Techphan,
  11. page19

    Future Date

    by page19

    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.

    page19
    Last reply by page19,
  12. naplesweb

    Millisecond Unix timestamp human conversion craziness

    by naplesweb

    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 - 🙂 …

    naplesweb
    Last reply by naplesweb,
    • Like
  13. W4hyu213

    How to make calculation for file on my attachment?

    by W4hyu213

    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

    Josh Ormond
    Last reply by Josh Ormond,
  14. ClinicArt

    Average Age over several records

    by ClinicArt

    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!

    AHunter3
    Last reply by AHunter3,
    • Like
  15. W4hyu213

    Make total with summary or calculation, file in attachment

    by W4hyu213

    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

    W4hyu213
    Last reply by W4hyu213,
  16. hopey8574

    Auto population of a field based on 2 other dynamic value lists

    by hopey8574

    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…

    hopey8574
    Last reply by hopey8574,
  17. derlandson

    Financial calculation based on date of transaction

    by derlandson

    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…

    Techphan
    Last reply by Techphan,
    • Like
  18. dztrucktion

    Repeating Alphanumeric Strings

    by dztrucktion
    • 5 replies
    • 184 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 …

    dztrucktion
    Last reply by dztrucktion,
  19. TanyaI

    Time calculation

    by TanyaI
    • 3 replies
    • 251 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

    TanyaI
    Last reply by TanyaI,
  20. David Gadd

    Summary (total) of number field for all records (not just found records)?

    by David Gadd
    • 3 replies
    • 266 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.

    Steve Martino
    Last reply by Steve Martino,
  21. Kishan

    Lock a Radio Button

    by Kishan
    • 2 replies
    • 342 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

    Kishan
    Last reply by Kishan,
    • Thanks
    • Like
  22. mbk28

    age with competition

    by mbk28

    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…

    Techphan
    Last reply by Techphan,
    • Thanks
    • Like
  23. Carlos F. Martinez

    Need help with calculating implementation date

    by Carlos F. Martinez
    • 7 replies
    • 338 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.

    Carlos F. Martinez
    Last reply by Carlos F. Martinez,
    • Thanks
    • Like
  24. Carlos F. Martinez

    Need help with count calculations

    by Carlos F. Martinez
    • 3 replies
    • 373 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.

    Carlos F. Martinez
    Last reply by Carlos F. Martinez,
    • Thanks
    • Like
  25. torch_55

    External storage for Container Fields

    by torch_55

    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?

    Josh Ormond
    Last reply by Josh Ormond,
  26. Jabert

    Finding number of ID's in a large group of records

    by Jabert

    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?

    iking42
    Last reply by iking42,
  27. Parken26

    Case - calculate date based on date, age and type criteria

    by Parken26
    • 11 replies
    • 1.2k 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 …

    Parken26
    Last reply by Parken26,
  28. CBarnes

    Filled-In Fields From Related Records

    by CBarnes

    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…

    AHunter3
    Last reply by AHunter3,
  29. JamieChriso

    Summary field not working as it should

    by JamieChriso

    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…

    David Head
    Last reply by David Head,
  30. JohnGG

    Do a sequential lookup/calc from a csv list

    by JohnGG
    • 3 replies
    • 984 views

    This has been driving me nuts for 10 days. I tried. Here is what I want to achieve. This shows the list of Zip codes and the Cities serviced filled out manually. A pain in the butt This shows the list of Zip codes and the Cities serviced not filled out, I want to do it programmatically. Any ideas or direction would be welcome. I can do a lookup for one zip at a time but from a list..? Cheers JohnGG

    JohnGG
    Last reply by JohnGG,


  • YPR DONATION-TIP KEEPS US ONLINE

    WE have helped 100,000's of people for many years, now we need your help!!!! Your Donation is Needed to Keep Us Online! Covid 19 and isolation!!!! Without Your Help We Are Gone After 23  Years!



    3% of donation goal reached.
    Donate Sidebar by DevFuse

  • Upcoming Events

    No upcoming events found
×
×
  • Create New...

Important Information

Terms of Use

  I accept

The Original FileMaker Forum & Community

Welcome to our the original online FileMaker forum & community. Find answers, ask questions, and connect with our community of FileMaker users, developers, and partners from around the world. To take part all you need to do is register!