Calculation Engine (Define Fields)

We process songwriter royalties. It is not uncommon for us to be working with 100,000+ records. The summary field on our royalty report layout is extremely important. This tells the bottom line … (what the payout is). We frequently access this layout. The actual royalty checks are also printed through FileMaker (using the amount of the summary field). We only have one summary field on our royalty report layout. Today there are 138,150 records that I’m working with. We’ve been working with this database for about 13 years. The latency we experience with the one summary field on our royalty report layout is really aggravating. I am currently working on the machine that host…

I'm looking for a calculation to use as a flag for any letters/characters that aren't approved for use in a spefic field; the approved characters are: dMmy/.- ...and those characters can appear in any order and in any number...so that negates using PatternCount unless I make a multi-line case statement using PatternCount for each approved character, but that sounds klunky. I also tried using Filter as part of an equation but I couldn't get it to work. Your help would be appreciated. Thanks!

Hi. I have a single field, call it LETTER, with a massive calculation, but I want that calculation to be vary depending on another variable, ie a person. (Tom, George, Pete, ... ). So I"m thinking I need a table of a calculation for each person. Tom and his calculation, George and his calculation, etc , But how do I get those different calculations into the LETTER field. Is it a lookup command? Appreciate any help. George

I have a calculated field that makes up part of the body of an email. It looks like this: "Total new charges:" & $InvoiceTotal When this prints, if the invoicetotal is 79.90, it prints 79.9. If the invoice total is 79.00, it prints 79. How can I get it to always print with 2 decimal places?

I'm modifying an Expenses DB template that came with my FM12 app (the only app I have) and it does a monthly summary based on Year ( Due Date ) & " " & Month ( Due Date ) The output is Text - which is sorted in the Sub Summary field. The summary view based on sub-summary sort works for 80% of the year but for some reason it goes Jan - Oct - Nov - Dec - Feb then the rest is correct month order. I'm not sure what the glitch is. Is there a better workaround that sorts by date and year, so all of 2016 in correct month order then all of 2017 in correct month order etc?

As you can see in the image i create from filemaker the google map and the points of address. Using the polygon function I delimited the area that include the addresses. as i create the polygon i get into a div the coordinates of the polygon. I should pass them to filemaker and it has to find the addresses included into the polygon. To find them i should loop all the records to check if the address of each is in the polygon bound. I didn't find the formula to get this result, there are custom function for circle or rectangle area but not for a polygon. Does any can help me? Polydata_test1.html

Hello All, How can I capture all the materials that are going to expire and for retest this month. My problem is with my present graph relationship (Retest) it will not capture all the materials that are due for retest this month. it only capture today's date and future dates and also it captures the next month dates same with my expiry relationship. I have attached the file that Im working on for your reference Thank you so much.. Retest_Expiry.fmp12

I have two related queries relating to serial numbers, and to a summary field. First query: I have a number field which I want to auto-enter a serial number, but I want that serial number to depend on the value in another field in the same table. So for example the records should create like this: Field 1 'blue', Field 2 '001' Field 1 'blue', Field 2 '002' Field 1 'red', Field 2 '001' Field 1 'blue', Field 2 '003' Field 1 'red', Field 2 '002' etc... Field 2 needs to be a number field with an auto-enter calculation because I need to be able to enter a value that overrides this on occasion. Second query: I also want a summary field …

This is pretty a simple request that does not seem to work. I have two tables Parent and Child. I want the Parent record (of which there is only) to display the number of Child records in each parent set. In the Child table I have field 'k one' (a calc with a value of 1). In the Parent I have a calc field Count(Child::k one). No matter what I do the value is always 1 on all of the parent records, but it should read 25, 65, 44, etc depending on the number of child related records. What am I missing? Thanks in advance, David

I'm working on a script that sends an email to a customer with a custom link. Is there a way to create the name of the link (what the customer sees) as Name (for example) but have the address actually different IE the real address? The problem I',m having is my custom URL is very long and I would like to make it easy and simple for the customer and for them only to see a blue line with the title "Click here" Thoughts? Many thanks in advance

OK, I'm using FM16 and trying to figure out an API call. I'm not an expert when it comes to APIs. I have a shopify store, and I can use Insert from URL to get the JSON info on each order. I want to be able to "PUT" and upload parts of the order. Every order has a "Note" field that I can add notes. Per the shopify API documentation, to add an order note, you do the following: Add Note to order PUT /admin/orders/#{id}.json { "order": { "id": 450789469, "note": "Customer contacted us about a custom engraving on this iPod" } } Can someone help me with what I put in the cURL options area? Any help would be appreciated. I think…

Hi, does anyone know how to validate a container field as unique? I am placing a barcode graphic into a container field and want to ensure that barcode can't get assigned to another record. Thanks for any help..

Not sure if there is a location feature within filemaker or a list I can build into a table but I am trying to set up system where if I type in a zip code (92881) for example, the 10 closest zip codes in that area are shown. Any ideas? Thank you in advance!

I'm hoping someone can help me... I'm trying to convert the following Timestamp to Date. The timestamp format is: 2017-10-30T14:47:12-06:00 . I've tried to simply use the Left function to make it 2017-10-30, but I still can't convert that to a regular date. Any advice? Thank you!

I am trying to create a script that sets a global field with a certain value so all users connected to the server can see this value in the global field. Is there a way or script that tells the server to set a field rather than the connected user?

If i have field with number. How i can automatically make that number 1 smaller when i click Duplicate this record button? So number is now 7 and in next record it is 6.

Is there a script to detect when other users are currently in a particular layout? I have a layout I use to bind accounts but I only want one user at a time to be able to access that layout. When the first person leaves the layout then another person can enter. Can this be done? Thanks

I'm stumped. I need a calculation that will return the quartile rank for a record within a set. I was able to easily do this in Excel, but not so sure how to do this in FileMaker, or if it is even possible to do so. Any tips would be greatly appreciated! For example: I have 14 records. I need to know in which quartile they ranked in based on the amount of their contributions.

I have a Db setup that allows users to scan barcodes and save them in a barcode field. I'd like to set it up so that after they scan the barcode, it will move the cursor to another field automatically. I also have a button setup to navigate to the next record and automatically go to the Barcode field to wait for input. Below is what I've got for my button, but I can't seem to figure out how to set it up so that the cursor will automatically move to a specific field in that record after entering the barcode fom the scanner. So far I have: Go to Record/Request/Page [Next; Exit after last:Off] Go to Field [UPCS::UPC]

Hello I have 2 tables linked by pk and fk. They are jobs and quotes. A job can have a few versions of a quote, the last one being the most up to date. I can find the latest related record and return that field, but I want to return the cost of that single related record too. I have tried using If (latest related record=pk of record ; cost; 0) but it doesn't work. Any help appreciated - I am sure it is pretty simple. Thanks W

Hi All, I'm familiar with the middle words function but I'm looking to separate individual letters. I have a table with values the first 2 letters of which I do not need. I'm looking to create a field or function that skips the first 2 letters and displays everything after the first 2 letters. Is this possible or is the middle words the only way?

Trying to trim characters from both the left and right of a value in one field. There are no breaks or symbols within the value. For example, the following value is in my "full_ticket_number" field: 2900686073681910 In a separate calc field I need to have the "29" from the left and the "0" from the right removed to provide the value: 0068607368191 In this example, the "29" and the "0" seem to be some sort of control character placed there by the issuer, which have no use for me in my solution. Of course the value is unique for each record so I can't just have the calculation look for certain characters to apply the function to. I've tried to combin…

I wasn't sure where to post this so please move this topic if a better forum would be appropriate. I have an application that I'm working on that will have multiple Event Weekends, for a type of sport. I need to know what the Current Event Weekend is that the user will be working on. The Current Weekend can be changed depending on which weekend the user needs to add entries to. I'm not sure how to set this up... Option One: I thought maybe having a CurrentEvent table, where there is basically only one record that the whole application can access, that tells the application what the Current Weekend is. (it can just be a number field that is linked to the E…

In MS Word I can edit the text display of a hyperlink by right clicking on the link which gives me a menu of options including "edit," allowing me to simply & easily replace the text string with any word I want. I cannot, however, do this in a field in FMPro 14. Is there a simple way to do this in FMPro 14, as there is in Word?

We have users syncing using laptops all over different timezones. However, there is the possibility that the laptop's time/zone are not set to automatically update and either can be set incorrectly. Moreover, we cannot rely on any internet access. So, a user may travel from Rome to Moscow and never realize that the laptop's clock/zone is wrong. We're looking for ideas to at least alert the user that they might have incorrect clock settings. Ideas welcome.

We're using Macs here at the office (not sure if it matters) but I have a search field that you can type search terms into and currently the user has to exit the field then click on a separate search button. Is there a way to perform the search script when enter is pressed? Thanks

in a field called p1::tn1 i want first letter of the word with font1 and characters after 1st should be font2 i wrote script but no success where am i wrong kindly someone help me plz Upper(TextSize(TextFont(TextColor(Left ( P1::TN1 ; 1 ); RGB( 0 ; 0 ;0 )); "font1" ) ; 12)) & Upper(TextSize(TextFont(TextColor(Middle ( P1::TN1 ; 2 ; P1::TN1 ); RGB( 0 ; 0 ;0 )); "font2" ) ; 12))

I am converting field names to values in a text field. Field names are similar to: FacilitySizeApproxImpervious For the data, I want: Facility Size Approx Impervioius What is the best way to identify the capital letter and add a space before it (but obviously not the first capital letter)? Thank you for any assistance. By the way, I have this started: Let ( field = table::FieldName Substitute ( field ; [ "A" ; " A" ] ; [ "B" ; " B" ] ; [ "C" ; " C" ] But I wonder if there is a better way and that also replaces the first one.

Hello All, I want to create an auto enter Lot Number for new incoming material base on YYMMXXX YY - Two digits of the Current Year MM - Months Number XXX - Number of Material Incoming For Example: If I received 5 Materials for the Month of September 2017 the Lot Number would be the following 1709001 1709002 1709003 1709004 1709005 If I received 3 Materials for the Month of October 2017 the Lot Number would be the following 1710001 1710002 1710003 Any Help is Highly Appreciated.. Thank you

Hi there! I've been beating my head on the desk for about a week trying to figure out an issue with auto-incrementing values in a child table, dependent on a value in the parent table, and it is driving me absolutely insane. In a previous iteration of the DB, auto-increment was working fine, as the value to increment the field was hard coded (i.e. incrementing the value by 1 for each successive child table record created). However, through real-world use, it became apparent that this was not ideal, and that I needed to have the option of incrementing the value by 1 or by 2. So, I devised a field in the parent table for the user to select which increment should be use…

Hi, when using the LocationValues function I get the following values: +1.325186, +103.967162, +65.000000 Question, how can I have the above values separated and captured into Latitude, Longitude and Accuracy fields?

Hey Guys, I'm basically looking to extract information from a field. So lets say I have a field called "Tags" and for arguments sake, let's say the field contains color string inside, IE "Red Blue Black Green Purple Orange" Now if I wanted to check if the field contained the word "Green" within the string and if it DOES contain it, then to RETURN that result and set it into a field somewhere? So the result of the calculation would be "Green" I'm sorry if thats unclear, but I need to find a way to do this, to check for and extract a certain word from the "Tags" field which is a long string of text (different words) Thanks in advance

I need a calculation that will return the day number (1st, 2nd, etc) for the first Monday in a given month (e.g. an input of 9/2017 would return "4") Any help is gratefully appreciated.

I have difficulties to solve this in FileMaker 16 please see attached file I cant find rank function... step1 and 3 are easy but step 2 is a problem Please help me I'm desperate. d'hondt.xlsx

Hi All, I have a data set that includes about 300k records. Each record includes 3 fields: Year, make, Model. I'm looking to set up the 3 fields in my main database so I can start selecting the year, which will limit what makes are displayed. Select the make and based on the year and make show the available model options. What is the best way to set up tables and or relationships so this is sorted properly? Thank you!

I have 2 time fields defining the time for the beginning and the end of a call which I trigger at the beginning and end of the call: the result is another time field. Another numeric field gives me the actual cost per minutes in Euros as 4,7 However I don't know how to calculate the decimals in the cost field as FMP time function only gives hours, minutes and seconds. If I have a resulting call time as 00:06:14 and the cost is 4,7 cents per minute what is the appropriate formula to get the actual cost for the call? Thanks a lot

I need a calculation that will return the numeric of the first (or second through fifth) Saturday of a month, based on an entry in a month field. Example: I input July 2017 then 5 fields would populate as follows Sat 1 = 1 Sat 2 = 8 Sat 3 = 15 Sat 4 = 22 Sat 5 = 29 Any and all help greatly appreciated.

I'm trying to create a script that emails to a customer and sets the subject line automatically as the customers name or business name. The problem I'm having is when a business or customer has the AND or & symbol in the name it completely messes up the script. Is there a character or way to ignore an instance of the & or and symbol?

Hello All, I want to trace the Lot Number and How many quantities in which a certain material was used in a certain product until it finished. My problem is I need help to my calculation to my Quantity Balance and Quantity Returned Fields because I cant figure it out. In the attached File If I Received like for example 50Kg and they Dispensed 23Kg I Have a Quantity Returned of 27Kg, but in my Quantity Balance Field it shows only 4 Kg. and when I put 27Kg to my Quantity Dispense Field my Quantity Balance Field Shows 0 but in my Quantity Returned Field it shows 23Kg. Please find attached file that I'm working. Than…

Is there a way to use JSONPath with FM JSON functions? it would be great if I could use wildcards.

Hi I have a timesheet database and sometimes the post call field might result in a negative result. This however means that the Total overtime field will take it off the calculation as it includes the result of 4 fields. Is there a way to make it so the total calculation field doesn't include the post field if it is a negative. In the file attached you can see that the overtime should calculate 6 and half hours (pre, camera and broken added together are 6.5 hrs but it is taking 30 mins off cos the post is a negative . The post is a negative because it is taking the time from the wrap out time to the leave time less 45 mins we give free. But when y…

I am currently working on a database that will score our company's suppliers. I have one table that contains all contact information for the suppliers (Suppliers), and another related table that contains the actual score entries (Scorecards). Suppliers are scored on pieces shipped on time and quality reports. We only ever send a scorecard to suppliers based on information from the current year, so I have a field in the Scorecards table for year. The layout I am working with is based on the Suppliers table, with a portal displaying the related entries from Scorecards in the specified year. Now, where I'm running into trouble is my attempt to retrieve a total for…

I am looking to create a system to administer and report on a series of timed services. I have most of it in place. I can create a new record for one hour, or 2 hours or whatever and produce reports daily weekly or monthly. The bit I am missing out on is a countdown timer for each event (portal record). When I create a new record with a value of 60 minutes I would like to include a timer which you can start, pause, stop, add X minutes to etc. When this timer expires I would like to sound a chime until acknowledged. There would be more than one record visible in the portal simultaneously and all could have active timers running. Is this possible? Are there c…

Hey Guys, Thanks for having me I'm a beginner please take it easy haha! Basically I have a calculation field, where I am trying to force the answer as "0" when the calc returns "?" (due to an empty field etc, for whatever reason I'm not concerned about) My calc is: Round ( If ( IsEmpty ( Cash Sales Total Sales Value )=1 ; 0; (Cash Sales Total Last 7 Days-(Landed Cost*Cash Sales Total Last 7 Days)*1.2) / Cash Sales Total Sales Value ); 2) Now it returns an answer when there is one, or a "?" when insufficient data, all I want is to return a "0" in this case rather than a "?" Thanks in advance and sorry for the noob question

http://www.episcopalchurch.org/parish/all-saints-episcopal-church-duncan-ok http://www.episcopalchurch.org/parish/all-saints-episcopal-church-briarcliff-manor-ny http://www.episcopalchurch.org/parish/all-saints-episcopal-church-greensboro-nc  I have included 3 sample urls containing information I am trying to scrape  I would like to open the URLS within filemakers' web browser and scrape them for this information I have included 3 samples because they vary. I would simply be happy to get the text between The title of the church and "see map: Google Maps"  Basically there are a lot of variables. Sometimes there are paragraphs of text in the middle of where …

Hi, I have a global variable that has been set from previous a script. This global variable is $$abc and has a value of 100. I have two fields on Table A, Field1 and Field2. Field 1 has a value of abc, and field 2 is a calculation field with this formula: (Evaluate ( "$$" & Field1 ) ). The calculated result, in this case, should be "100" since that is the value of the Global Variable that I have set on the previous Script. However, I don't know what I am doing wrong. I Evaluate the formula on data viewer and it evaluates it correctly, but on the calculation field, it is just not working. I have tried to set the formula as $$abc directly, and still, it d…

I'm trying to build a global calculation that returns the value of a field in another record. I need the field value ("field1") for the record where the field ("startDate") matches the start date of the current week. I can build a calc that determines whether the current week's start date (7/16/2017) matches "startDate" for a given record, but I need to show the value of "field1" for that record only. I tried creating a self-join relationship to isolate a related record based on "startDate", but any such date calculations are unstored and thus not usable as match fields in a relationship. I'm probably missing something obvious, but for now, can't figure th…

I wasn't sure where this question belonged...(feel free to move it, Lee) In a system I'm converting from FM11, there exists a set of 4 global containers which are set OnFirstWindowOpen to 4 corresponding pref field containers. This allows for system-wide highlight colors and the ability to change them in one place. We reference those global containers in unstored calculations, simple example: dsp_hilite , result container = if ( field=1, gBlue, gGreen) Then, dsp_hilite is used in the background on a list view to color-code the row and a key is displayed in the footer. Is there a way with themes/styles/conditional formatting to achieve a similar r…

Okay, our sales reps run in a weekly cycle starting on Wednesday. I need, regardless what date it is, to find all records from the Wednesday before through the next Tuesday. Oh, and only the current year except if the search is done early in January, it needs to go into prior year. I tried many things but still fail in the find but it fails. Added - I think it good to have a day of week in Admin in case this changes to different day. Then the find would use that day of week to jump back and ahead and find the records we need. Week of year seems to fail for me. I searched but still need help.

I'd thought I'd solved this but looking at the background data it's not calculating properly. Any ideas much appreciated. I have a file with a date, bottle format, total cost and converted cost for larger formats (so, for example, 75cl might cost $20 but 1.5l costs $45. The converted cost of the 1.5l is $22.5). Assuming one beverage and just one bottle of each, I want to output a report which looks at the costs and converted costs over a specified period (eg 1/1/2016...12/31/2016) and outputs a 'premium' for, say, the 1.5l bottle. In this example, let's say I have 6 records: date1 75cl $19 date2 75cl $19.5 date3 75cl $20 date4 1.5l $43 date5 1.5l $45 …

Would appreciate help on what I thought was a simple problem: I would like to auto-update a date field ("FinalOutcomeUpdate") any time a user changes the entry in a text field ("final_Outcome"). Something like using the Auto-enter Modification Date, but only triggered by changes in (a) specified field(s). So far I tried variations of this calc, with no success: Case ( IsEmpty (final_Outcome) ; "" ; ContainsValue ( Get ( ModifiedFields ) ; 1 ; "final_Outcome" ) = 1 ; FinalOutcomeUpdate = Get (CurrentDate) ; FinalOutcomeUpdate ) Right now, this yield a "?" when modifying "final_Outcome", and entering a date into "FinalOutcomeUpdate"…

Howdy, all. I know I'm close...maybe. I'm trying to create a calc that'll strip the last five characters off a field name, regardless of its length. So far, I've come with: Left ( Get ( ActiveFieldName ) ; Length ( Get ( ActiveFieldName ) - 5 ) ) ...but it's not working, e.g., with a field name of County__lxt the result of the calc is Co. I thought of using multiple Substitute commands but that's shaping up to being real klunky. What's the final bit that I need? As always, thanks in advance for your help!

Greetings good people. I need help in printing. So I have a portal that I setup like a spreadsheet with rows and columns and a scroll bar. New entries to the portal will of-course jump to the new row until the portal window is full and then a scroll bar down for new entries. My problem is that I setup the print option to print the preview of the portal but then it will only show the top full visible part of the portal but I want it to scroll to the bottom of the portal and then print that preview part instead but if I manually scroll down and then print...it will always print as if I didn't scroll down the rows. Any suggestions please?

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

I have a time table and every time an employee logs into filemaker a record is created, the field Time In is set to get current time, date is set to get current date, account name is set, the record is given a record number and an $$variable record number is created. When the user exits filemaker a script is run: to to time layout, enter find mode, set field $$variable, find record, set Time Out field as Get Current time. This creates the time in and time out record overtime an employee uses our system and I have a time field that calculated time out - time in = time elapsed. Every day an employee will typically have 2 records, morning time in, time out for lunc…

If using the week number function to define a field, is there a way to get the first date of week 25 (for example) and last date of week 26. I'm using this for payroll so I can show on the pay slip for weeks 25 and 26 include dates June 19th - July 2nd.

I hope this is the right place to post my threat, right now i print ID card just on PVC card. Customer's need is change then they want me to print ID card with data encode. i prefer to use contact less card. Can anybody show me how it's work. thanks. Hasru

I have 2 fields, time in and time out that are filled in when a user clocks in or clocks out. I'm trying to create a 3rd field that calculates the lapsed time. If there is no end time then the field should (get current time) so these hours are calculated on the fly. Here is my field calculation so far: Case( Time Out; Time Out - Time In; IsEmpty (Time Out); Get( CurrentTime ) - Time In )

Hello Friend, Here I want to synchronize a layout ( attendance table Summary) field record ( Current Month Total Absent No) of selected employee name with selected salary month name to another layout (salary details) field records(No of leaves) with the same employee name and salary month. How it can be done by calculation or by Sql method or any other way to do so. Here is small file for reference with some transactions sample. Thanks Samarth Salary & Attendance Register - Copy.fmp12

G'morning, all. I'm sure there's a better way to do the following other than having a litany of Case statements in a calculation. There are three fields for three tests: Math, Reading, and Writing. Currently, if there's no value for each test then a Calculation (text) field displays (for the Math test) "Our records show that you have not passed the Math portion of the COMPASS exam." A separate, but identical, calculation/sentence applies to Reading and Writing so I have three, separate calculated fields to use as merge variables later. The problem is that in a letter, having the same sentence listed three times in a row with the same text (save for the different val…

There may be a simple solution to this but I can't think of one. I have a field that calculated total hours for the day. How can I have a field show any time over 8 hours for that day? Thanks

I have 3 different time fields and each have a corresponding time summary field which summarize total hours for the week. How do I summarize all 3 fields so it shows the hours correctly?

I'm in the process of seeing if it's posable to parse emails orders? We get the emails that are very standard format. Getting them into filemaker was simple with 360works. Parsing the text above "1 SKU....." is simple. My issue is I need to create a loop and be able to break out each Line Number for it's own record.. In this example text there are two line numbers. Trying to grab SKU #, Item Name, Item Description, Item Cost, File URL Any help would be greatly appreciated!! PURCHASE ORDER PO # : 2974822-1 Date : 2017-06-09 From: Company 1111 SomeWhere Middletown, IL 11121 Ship Account Number : 3…

I have a table with the field Producer Number. This table has about 10 records each with a different producer number 1-10 and these numbers change daily. Some days it will have numbers 4, 5, 6, 7. Other days it will have 1, 2, 5, 6. Etc. I'm trying find a way to to set a field when entering a layout to a random number within this field so when 4, 5, 6, 7 numbers are entered in the field a random number from that set will be selected and entered into the field. Further, is there a way to use this field or value set from my table and enter these numbers sequentially? Please let me know if I wasn't specific enough. Thank you!

I have data which has the problem where it's duplicated text in the fields Example: Bob SmithBob Smith David JonesDavid Jones Henry MorrisHenry Morris I just want to delete the duplicate in the field so it just shows Bob Smith, not Bob SmithBob Smith. Any help would be greatly appreciated!

Hello forum. Is there any method with a global checkbox set (or any checkbox set for that matter), when you uncheck all boxes (none checked) it auto enters a check mark in all boxes? Here's what I'm trying to do and maybe there is a better way 1. My goal is to filter a list view of customers by a global checkbox and a relationship with GTRR (two types of customers). The checkbox is global, and the relationship is: CUSTOMERS|---------<CUSTOMERS_Type with the fields: Customers::gType (the checkbox)|-------<Customer::Type Clicking on the checkbox performs GTRR in CUSTOMERS_Type. This works fine for clicking either checkbox or both check…

I have a field with a text string separated by right arrows. It goes something like this... Category Field: Cat1>Cat2>Cat3 I need to extract the first two Items Cat1 and Cat2 into their own fields...

Hi, I am looking for a simple method to select a week in a year, based on an assumption that there are 48 weeks in a year (see mock up attached, where the grey week buttons are clickable). The calendar picker in FM is great, but not suitable for my requirements, but I WOULD like my requested solution to be accessed in the same way (maybe returning something like YEAR, MONTH, WEEK_NUM. Also, not that bothered if the week are 1-4 for each month or 1-48. Has anything been built that i could leverage? Thanks

What could be the reason for anything coming through on the Final Grade higher than "100" showing up as a "B+" in the system. I added in a solution for it to look for a "100" in the Final Grade slot but then made every option an "A+". If(IsEmpty ( Sub1Final_Grade );""; Case ( Sub1Final_Grade ≥ "96.5" ; "A+" ; Sub1Final_Grade ≥ "92.5" ; "A" ; Sub1Final_Grade ≥ "89.5"; "A-" ; Sub1Final_Grade ≥ 86.5; "B+"; Sub1Final_Grade ≥ 82.5 ; "B" ; Sub1Final_Grade ≥ 79.5 ; "B-" ; Sub1Final_Grade ≥ 76.5 ; "C+" ; Sub1Final_Grade ≥ 72.5 ; "C" ; Sub1Final_Grade ≥ 69.5 ; "C-" ; Sub1Final_Grade ≥ 66.5 ; "D+" ; Sub1Final_Grade ≥ 64.9 ; "D" ; Sub1Final_Grade < 64 ; "F" ;…

I'm trying to work on a formula that will do the math for this grading system that I'm working on but using Case and IF keep send back the first TRUE. There are couple different parameters that the "Semester Average" are looking for. First the solution looks for the First Semester Exam(SubE1) to see if it's empty or not, then if it is, it should pull Quarter 1(Sub1Q1) add Quarter 2(Sub1Q2) divide by 2 to get the solution. If Quarter 2(SubQ2) is missing and so is First Semester Exam(Sub1E1) then it should just fill in with Quarter 1's grade. The last part is for weighting of final exams per grade level, but it just doesn't seem to work. Help needed. …

Hi, I have a configuration table (named 'Usager actif') with 'Projet ID actif' and start/end date values (field name 'Bilan date début' and 'Bilan date fin') linked to a timesheet table (named 'FTemps lié Projet bilan') to show only the records that match, according to the following relation: This table is also linked to a step table (named 'Étapes lié FTemps lié Projet bilan') to show a list of steps used by the timesheet table (named 'FTemps lié Projet bilan'), base on the following relation: So the last table (named 'Étapes lié FTemps lié Projet bilan') is shown in a portal and it's working correctly (show only two steps used, for all the tim…

Hello in my DB fin FMPadv 13 I have containers with movies. Is there a way to calculate the duration of these films? I dont have the GetContainerAttribute in my application and i understand it will not return many infos for Movies. Is there an Applescript I could use to get the duration? Thanks a lot

I'm stuck in my thinking.... How would I go about evaluating (getting the content) of multiple fields specified as merge fields in a text field? For example: "Name: " & <<Name>> & ", " & "Category: " & <<Category>> & ¶ & "Type: " & <<Type>> Which would result in: "Title: MyRecordName, Category: MyCateogry MyType" Any input most appreciated!

Hi...I have looked high and low for this solution...maybe it is too simple for anyone to comment on! I have a number field...say I enter 50. In one year from the date I entered 50 in that field, I would like it to automatically increase by 1, to 51. I do not want to have to re-enter the number, and if I don't visit that record with the field in it for 3 years, then I want it to increase by 3. Increase by 1 each year. OK, let me tell you what it is. I enter the ages of clients' children, next to their names, in a database. I do NOT want to have to enter their birthdays, nor even their birth year, I want to just enter the age, in a number, like "12". In a ye…

I was wondering, what to do to make the calculation engine do the same calculation with every repeating field. The case is: I have field A, which is of type number and has 6 repetitions. I have field B, which is of type calculation and has 6 repetitions as well. Now I want the algorithm take every repetition of field A to calculate and put it into the same repetition of field B. It may be very simple, but still, I don't manage... Thanks a lot!

Hi there... I have a table with about 350 new fields in it. All just text fields at the moment. I want to convert them to calculation fields... thats easy. I want to then have the same calculation (quite simple, something along the lines of If ( GetField ( Substitute ( GetFieldName ( Self ); "-product"; "product"))>0; 1; 0 ) " for every field Have I got to open up every field and paste that calc in 350 times or is there a simpler way? Matt

I would really appreciate some input on this, as I think I've got lost along the way trying to figure this out and whilst I have a working solution it seems somewhat convoluted (and is a bit sluggish to update)... I needed to create a report from the context of contacts so that this can display sub-summary data (sorted by contact::area and contact::category) of related survey data for each contact from the single most recent survey record (within a specified report date range) as well as count how many survey events took place in that date range. Because of the context, I concluded – perhaps wrongly – that I couldn't simply put the required summary fields in the sur…

Hi forum, I'm struggling with a time field. I'm importing a CSV file that contains a field (it is a text field at the moment) with a duration value in decimals, followed by a letter "m" (i.e. "1.5m", that is for 1 minute and 30 seconds). I'm not being able to let that field shows the value in a mm'ss" format, as I would. Do you have any ideas? Thanks!

Hi Folks, I've broken a pdf saving script which used to work. The filename itself hasn't changed so I don't think that's the problem (there are no /s for instance) - I'm pretty sure it's the file path and Mac OS re-organising itself to sync with iCloud. I'm trying to save PDFs to Dropbox and I think that the Dropbox folder on my my has changed location. According to info, the location is /Users/mikekentdavies/Dropbox/KJA Music Library. The calculation I'm using to set the location variable is below; can anyone see where I'm going wrong? (I've also tried various combinations of filemac: / file: / .. / : etc having read the FM creating file paths webpage.) …

Hi Folks, I just can't work out what I'm doing wrong here! I've got a set list. Each song has a duration (as a time field) in a song list table. In the same table I have a summary field that totals the durations. This works fine, except in the one place I actually need it - the "SetListPrint" list layout. I assume that somehow I'm sorting or finding wrong - can anyone see what I'm doing wrong? - Ideally I need total durations for each set and also for the spare songs as well as a total. Cheers! Mike WKBBNoNameCopy.fmp12

I am creating an app for a conference. Each seminar is assigned a code -- such as 101, 102, 103, etc. -- and the app will automatically create signage and displays for us. One of the services I want to provide is to tell people not only what seminar takes place right now, but which seminar will be *next up* in a particular ballroom. I have created a field called Next, into which we would enter the code for the seminar that takes place next, but that's as far as I have gotten. How do I automatically gather the information? For instance, let's say that Seminar 101 is entitled "How to Think," and is being led by John. Next up in that room is Seminar 103, entitled "How t…

Hi I've seem to have hit a mental block .. it might be easy and I'm thinking too hard about it Below is a screen of a layout with 2 sub summaries 1st - contractorID and 2nd the number of days worked each month based on their time sheets (using an inverse count calc / summary field someone here gave me a while back :-) .. where they might do multiple time sheet records on a particular day, but it's just counted as 1) As you can see, some didn't do any work during a particular calender month so it doesn't show. What I need to do is count the number of months showing ie for contrator 458 it's 3, for 643 it's 1 .. I then can do an averge days worked, BUT I can onl…

Hello forum. I'm trying to come up with a calculation to do the following. I know my date calc is wrong, but date calcs are just not my strong suit. It works everywhere except when crossing thru February. It's based on 3 fields with the calculation in the due date field: terms (30,60,90,120 days based on a value list),invoice date, and due date I'm trying to do 1, 2, 3, 4 months, minus a day. So technically, I think I'm doing it right, just not getting the desired result. Results: Invoice date terms due date 11/30/2016 90 days 02/29/17 <<doesn't work, gives 3/1/17 11/30/2016 60…

I'm going beg your indulgence and use my old geezer card since I don't remember how to fix this so it works: Here's the calc for "Hide object when": JOB::CountryAbbreviated_Company__lxt ≠ "US" or ( IsEmpty ( JOB::CountryAbbreviated_Company__lxt ) and MAIN::_g_CountryAbbreviated__gxt ≠ "US" ) Yes, I know it won't work because the equation needs to be Boolean--how do I modify it so it is? As always TIA for your help.

Greets, all: I know there's a more elegant way of coding this part of a Let calc but I'm having a brain fade: Let ( [ Country = CountryAbbreviated1__lxt ; State1 = StateAbbreviated1__lxt ] ; Case ( Country = "CA" and State1 = "NU"; "Territory"; Country = "CA" and State1 = "NT"; "Territory"; Country = "CA" and State1 = "YT"; "Territory" ) ) What is it? The Evaluate command? Thanks, and cheers, Rich

I have a relationship calc for a portal that will show either all products, or all products pertaining to another drop down of the season I am viewing. Case ( not IsEmpty ( Settings::gCheckBox ) ; Project::cProductID_ListSeason //occurrence filtered by season selector ; Project::cProductID_ListLocal //all products occurrence ) This all works fine, but I'd like to update the Project::cProductID_ListSeason to also show products that have 0 seasons assigned to them. My entities are: Projects <- Products <-> AssignedSeasons <- Seasons What would be a good approach to gather ProductIDs that contain no SeasonIDs and combine them into the lis…

I have a list of records where I'd like to sum values from one of the fields but "in groups". Each group effectively shares a value in another field. If those other common values match, records in that subset of records are summed. Here's an illustration of my data set and the calculation I'm trying to find. Common value Summed value Sum result (calculation field) 1111 5 8 2222 10 33 1111 3 8 2222 10 33 2222 13 …

I'm using {{FoundCount}} to get count of filtered records in a portal. This works well when just displaying the count on the layout but I'd like to use that figure as output to a field (regardless if this needs to be a calculation field or just number). How do I have a field track count from a portal?

Hi I have attached a filmmaker pro file I am trying to build. I am using Filemaker Pro 15 and I will use the app in Filemaker Go The username is admin and there is no password. The field calculation that I am having problem with is the field labelled Post Call. When you type in you have done 1 hour in the camera field, it will take an hour off in the overtime field, but will not take it off the Post call field. I need the Post call field to calculate how much overtime is done from when we wrap to leave (less an hour cos we give one hour free) If we do Camera overtime this is taken off the post call time overtime as it is a different rate. Eg. I…

Hello I am having table in which I am sorting with a particular field. I am getting following type of view after sorting: Group 1 ---------- Item 1 Item 2 --------- Group 2 -------- Item 1 ------- Group 3 etc. When only 1 record is found after sorting records, I don't want to show that record on sub summary part. Is there any way so that I can hide sub summary part ( or may be body part ) when I am getting only one record? More detail can be found in the attachment. Please let me know for any issues. Requirement.pdf

I'm looking to create a calculation field that takes the field 6 Total Premium and if that field is between 0 and 2,501 then calculates field Financed Monthly Amount by 1.06032941. If 6 Total Premium = 2,501 to 5,000 then calculate by a different amount, etc etc. What is the best way to accomplish this, I started setting up the calc below but it will not work. Thank you so much! If ((${6 Total Premium} > 0) and (${6 Total Premium} < 2501)) ; Financed Monthly Amount * 1.06032941

I have a simple multiplication table solution ("times tables") that I have made for my daughter and I'd like to improve it and would appreciate some guidance with the strategy to implement it. Currently the records are presented in the format a*b=c, with a*b presented and the input is made into the answer field "c". Currently the times tables to be tested are selected (e.g. for 7 times tables b=7), then the number of questions desired for the test are selected. The records for the table are then created by scripting in a loop from 1*b to 12*b then sorted randomly and the appropriate number of records omitted to show the desired number of records for the test. I…

Amateur hour here. I've been messing around in FM for years but only for personal use. Now I want to do something I thought would be easy to programme with my knowledge but I got stuck and can't seem to get unstuck. I have an Invoice database. My regular clients have a payment delay. Some of them are late of course. Every payment is filed so every invoice from the past has a calculation showing Late: Yes or No. I want to display on every new invoice the amount of money that client owes based on due invoices at that moment displayed as a simple sum. I did manage to do this through a portal but only managed to display a list of unpaid but due i…

Hello. I have a few databases for fire departments and in a Personnel Table I have a "Date of Hire" field. I then uses a calculation to get their Years of service and display it as Years, Months, Days. My question is: some members take a leave of absence or go inactive. I'm not sure how to approach subtracting a leave of absence start and end date or set a field with a date they they went inactive and then their Years of service would calculate up until that date and not the current date. Thank you for your time. Chris. Sent from my iPad using Tapatalk

Hi. First time poster :-) I'm trying to show lists of names of people belonging to a group on each record in a layout. Is this possible by a calculation or am I on a wrong track here? Thanks in advance Best regards -Daniel

Hi, I have a field called 'mytournament1'. I also have a field called 'mytournament1played'. With a form, I create the variable $tournament to bring 'mytournament1' to my results page. $tournament = $_GET['tournament']; I'd like to force the results page to search for the field 'mytournament1played' for each record by using the variable $tournament and combining the word 'played' or the variable $played. $played = 'played'; $tournamentplayed = $tournament . $played; $tournamentplayed2 = $record->getField('$tournamentplayed'); (I want it to deliver) $record->getField('mytournament1played'); I know the 1st '$record->ge…

Our institution assigns numbers to represent our quarter and year, "672", for example. The right number is the quarter (1-4, where summer is 1, fall is 2, winter is 3, spring is 4); and the left two numbers represent the year. In this case, "672" would equal Fall 2016. When student enters program (e.g, 672), it is entered into Quarters::CoreNumberCalc Then converted into plain text "Fall 2016"using calculations : Case ( Right ( CoreNumberCalc ; 1 ) = 1; "Summer";Right ( CoreNumberCalc ; 1 ) = 2; "Fall"; Right ( CoreNumberCalc ; 1 ) = 3;"Winter";"Spring") Case ( Right ( CoreNumberCalc ; 1 ) <= 2; "201" & Left ( CoreNumberCalc ; 1 );"201" & Middle ( …

I suspect I’m forgetting something simple. I have a relationship and I’m successfully showing the correct related records in a portal. On that same layout from the context of that main table (not a TO of it), a calculation is failing with ValueListItems ( Get(FileName); “valuelist_name”) It’s coming up blank. The value list however, is working. It is being evaluated from the context of the main table. When I put a field on that layout and assign the value list to a drop down field, I see the values. How on earth would this ValueListItems function be failing. Other calcs in this solution (albeit in a different table) with ValueListItems ( Get(FileName); “val…

Hi forum, I know I saw this before, but I can't really figure out how to properly search for it. What I have is a report. In the header are 6 categories in a checkbox set, based on the Value list 'Categories', and it's a global field (gSearch). When checking boxes, the OnObjectModify script trigger takes the value(s) searches and sorts the sub-summary part. The sort on Category is based on the value list (not alphabetically). So it the sort is in the order of the checkboxes you see in the screen shot. All of this works fine. What I was trying to accomplish was to put a merge field (gSearchParameters) in the header that states what boxes were checked (…

Good Day, Has anyone see this before? I have a simple date calculation: Get(CurrentDate) - DateAccepted - So for instance, if it was today it would be 03/06/2017 - 03/01/2017 (accepted date) which would give me 5 days. That's fine, works ok. HOWEVER, when the dates are the same, like 03/06/2017 - 03/06/2017 (so it was received on the current day) I consistently get the number "4" Should be "0". This happens between any 2 sets of days, if they are the same. Anyone know why this happens? It's very strange. Thanks for any enlightenment on this! Karen