Calculation Engine (Define Fields)

I'm struggling to remove quotation marks from a symbol/operator such as this: "<" I have tried Substitute (Text; "\""; ""), as well as Brian Dunning's custom function RemoveSmartQuotes (text). Both works in the field of question perfectly with normal text, such as {hello "world"} ends up being {hello world}, but ho matter what I run "<" through, the quotation marks remain untouched. Any ideas?

Hi, I have a database containing all sales i make for different clients. I'd like to make a running total field for all sales in the first quarter, another for the second quarter etc. So in the end i can easily see how much i made each quarter. This field should self update upon new sales. How can i do this? Found a way around by doing a search for all 1st quarter fields and copying total invoice value to a container field. Very unhandy and i have to fire a script each time a update... Any help is appreciated very much!

ISO 8601 Week Numbers ISO 8601 defines the Week as always starting with Monday being Day 1 and finishing with Sunday being Day 7. Therefore, the days of a single ISO Week can be in two different Calendar Years; and, because a Calendar Year has one or two more than 52 CalendarWeekISO8601.zip

Hi all. I'm new to this site so I apologise if this is the wrong place to post this. My database tells me how many days are left before the due date and how many days overdue. This worked fine until the year changed. Now invoices that were due last year, are now saying 200 to 300 odd days till due instead of overdue. I can see that the problem is it still thinks it's the same year. For example. An invoice due on 31 December 2015 should be 26 days overdue on 26 Jan 2016 but instead it's about 339 days till it's due. I have looked and tried changing things but can not get it to work right. Any help would be much appreciated. Thanks in advance. The code I have is…

The main database I work with contains thousands of records - one for each customer we have ever worked with, and a new record is created for each new customer. And there are dozens of layouts similar to the one I am working on now - all available for, and unique to, each customer record. The portion of this layout I am working on is much like an Excel spreadsheet, with a header row of values across the top, a column of ages down the left side, and a grid of values each calculated based on the value above it and the age to the left of it. See the attached example. I make use of repeating fields for every row on the grid to avoid having to create hundreds of individual cal…

This must be so simple, but I cannot work it out or find the answer! Using Filemaker 14. I have an old db in which my records are stock items. I currently have a number field showing the price net of tax (PRICE_NET) and a calculation field (PRICE_INC) which shows the price including tax at 10%, using the calculation PRICE_NET * 1.10. I also have a date field (DATE) showing when the item was acquired. Let’s say the tax increased to 15% at the beginning of this year. I therefore want to change the calculation in PRICE_INC so that it displays the net price + 10% if the date in the DATE field falls before 01/01/2016, and the net price + 15% if the date in the D…

I have multiple records That I have imported via a CSV file. I need to seperate the info of one field which is not consistent. for example 1 record has (field a) with a value of 12 x 120mm 2nd record has (field a) with a value of 1 1/2 X 2" i need (field b ) as a calculation to extrapolate the first part before the X i.e 12 and 1 1/2 respectively and field c to calculate the last part 120mm and 2" respectively .. How can I calculate this? TIA

Here is my situation I got a main db with 2 other dbs. Databases are set with account names and passwords. When a user logs in, I want to get his account name in a calc field with Get (Account Name) (Text) but I still get the full access account name. Thanks for your tips

Hi, In my database I have different records of the same person in different years. As an example let´s say each record contains the amount of money the person has and the amount he wants to have next year. Is it possible to create a field with a calculation that will indicate whether the plan is achieved? The calculation will have to find a record on both name and year, and compare the planned amount with the actual amount. Edit: Just finding and retrieving a value from a different record should be sufficient actually, is this possible?

FM 12 Advanced on Mac OS 10.7.5. This is for a TNC Drivers (Uber, Lyft, etc.) database. For the purposes of this question I'm dealing with 3 tables: Shifts, Rides and Cities. Rides (child table) are displayed in a portal on each Shift (parent) record. So, for example, the shift record for 9/20/15 shows 25 rides, each with a destination City. I have a separate table for Cities that contains one record per City. I'm trying to display, on each Shift record, an aggregate list of all the Cities ranked by Count of rides for that City. For example, of the 25 rides, 12 were to San Jose, 7 to Santa Clara, 4 to Sunnyvale and 1 to Cupertino. I've created relationships that c…

I have a simple parent and one child table relationship built. "Production Order" and "Copy". An Order can have any number of pieces of Copy. I'd like a way to pull the titles of each piece of Copy back into the parent table to be able to email the Production Order data with the Copy titles. I would appreciate any insight.

I'm looking for the best way to handle this. I've got two tables. Orders and LineItems. The orders layout has a portal to show the related records for LineItems. I've made a line item_Item status filed in LineItems that is a Number Filed. The purpose was that a number greater then 0 would cause a notification in item_Item_status that line item has issues (based on the scripts) This works fine and I'm also do a conditional for the portal row to show visually each line item that has issues. What I want to do is, In the Orders layout. if any related LineItems have an error I want to mark/show that the Order record has issue. What would b…

I have a table which is related to another one. Trying to keep it simple lets say one is a table of cats (first table) which has fields for cat species, colour, weight etc. I can also record the details of when I bought the cat, price paid etc. It is linked to another table where I record all the prices of cats I can find (second table) and they're matched according to species and colour. I create a report based upon a time period using the first table and by doing a find of records which match certain criteria I wish to includewhich shows certain data for example: min price, max price, av. opening price, av. opening price. The report doesn't need a script it is simp…

Can anyone explain how FM treats a summary field if weighted by date? For example, say I have one transaction each month over 5 years. I want an average of these that would be a total each month's transaction values divided by 60. What if this were weighted by date?

I'm needing a calculation field that returns only a portion of another field (Product_Number). For Product_Number 0797-8851-1 the result should be 797 (no leading zero) For Product_Number 5-4781-3-43 the result should be 5 For Product_Number 579-287-9-154 the result should be 579 Get the idea? The result should equal the number before the first dash (except if there is a leading zero). In the case of a leading zero, I do not need the leading zero.

I do not want a decimal allowed, nor a minus or dollar sign or text. I want only whole numbers allowed. I tried GetAsNumber but that did not work. I tried using filter like Filter(number field ; "1234567890") but that allows leading zero which I do not want either but I need to include the zero for numbers ending in 0. I am not sure whether to use auto enter and just remove what I do not want or to provide an error message. Can I get ideas on how to both correct automatically or fail validation? This is for version 12 so I know I could use SQL but I do not want to. I even tried Exact with GetasNumber. I fail on all tries. I should know how to do this by now but…

People like using cats in this forum and it keeps it simple. Let's say I have 3 types of cat (C) - Moggy (M), White (W) and Siamese (S). I am recording prices and using these prices for a report which produces their average price. I want to work out the premium that one breed of cat has over another expressed as a percentage of, say Moggies. I have 3 prices for Moggies: $20,$21,$22; 3 prices for White cats $30,$31,$32 and 3 prices for Siamese - $50, $55, $60. My nice summary report produced from a script shows the average prices OK but how can I work out the premium for the other breeds given the calculation has to be on a value which is already a summary? …

I am disappointed I can't seem to solve such a simple problem. I want to take the value in a field (say, field A), copy it to a new field (field B), check which range of numbers it is in and then convert this number into a symbol. 10-100 = $; 100 - 250 = $$; 250 - 350 = $$$ and so on. Thus if A = $110. Field B will show $$. If A=$275, B becomes $$$ etc. I tried copying A into a global temporary field (Field C) and then making this work on that but it doesn't work either. It seems much more complicated than I first imagined. I suppose I have to convert the number to text first too?

hi in my solution i have a text box which allows me to enter four lines. i selected that it should allow to auto complete using existing values entered in my solution. however it only brings up one line not the entire field. please can someone help me that it should bring up the entire field or when i selct the first line from the list the entire content is entered. thanks in advance.

Hi, I need some help with a little problem that I have found repeatedly. I attach one image of one of the cases In the first table, I have a ProyectoIdFk (Proyecto Foreign key, to relate the values in this table to another one, the projects table), an Id field which is a unique number for each row (or specifically, an addition. A value added to the initial value of the project), TipoAdicion (Addition Type) that could be either Obra (Construction) or Interventoría (Oversight), ValorAdicion with the value of each addition and c_ValorAdicionesOBRA (Calculation field to add up all the additions defined as Obra) which isn't working properly yet and therefore I need…

tried many method but no close to what i want please help..... All I need is IN-yymmdd-serial number, once new date then serial number reset and start from 1 automatically. just like below image Thanks everyone

Hello, I've imported a .csv file that has the date formatted as yyyy-mm-dd, it imported into FMP as a text field. Is there a way to convert that field to a date with the mm-dd-yyyy date format? I've searched the forums and couldn't find anything specifically talking about it. Thanks, Jim

Please excuse my ignorance, I've very new to FMP. I've searched the forum with little success. Here's my problem: I've imported a .cvs of timesheet data and need to format the data and fields so when viewing the records it's easy to read. I have field 'day1StartTimeInSeconds' that contains a start time in seconds '32400' I'd like to show that as the actual time 9:00 when viewing the record but I can't figure out how. I've tried formatting the data as text, number, and time with the option calculated value 'GetAsTime (day1StartTimeInSeconds)'. Formatting the data as time is the only way I can change the time option is the Data Formatting section of the inspector…

I am using a calculation field to combine first and last name. The calculation (First Name & " " & Last Name) works fine when the last name contains no blanks or hyphens. When the last name contains a space or hyphen between two words, such as in "De Liban" or "De-Liban", the calculation drops everything after the space or hyphen. What can be done about this besides the obvious of not using spaces or hyphens in the last name? Thanks everyone. Linda

I'm using a modified version of the Invoices Starter Solution form FM13 and I've added a boolean field to the Invoice Data table which indicates if it has been ordered or not. What I'd like to do is add a boolean calculated field in the Invoices table which looks at all related records, all the items on the invoice, and if they've all been ordered it's 1. If I create the calculated field in invoices and set it to If(InvoiceData::Ordered=1,1,"") it works as long as there's only 1 item. If there are two or more items it just moves in lock step with the first item and ignores the rest. Any way to do this without a script? Thanks, Isaac

I need to use the Get ( ActiveLayoutObjectName ) function for some window positioning scripts. However, I can't seem to find a way to actually get the active layout object name. The example from Filemaker's own website states: "There is a named button on the current layout called cancelButton. When the focus is on the button, Get(ActiveLayoutObjectName) returns cancelButton." Perhaps I'm unclear on the concept of "focus". I have a Button on my layout called "test". I have named the button/object "test" using the Position Tab in the Inspector. I made a simple script using only the Show Custom Dialog step which returns Get ( ActiveLayoutObjectN…

hello , i have different customers with different prices for each product, about 10 price levels for each product , no more than that. but sometimes not all products applies for 10 different prices , so if i use calculation i would need 10 fields on products table and the invoice table price i have to set up calculation to check the price level and then set the right price. the other option is to have on my products table one portal that stores the price levels for each product and then on the invoice price set the field as lookup . now my question is , which method is more faster , stable , easy to maintain? please let me know your thoughts or how do you…

I have a Portal containing a Checkbox with the value of "Paid" When "Paid" is selected how do I get it to add a set amount, say 20, to a field called "Total" outside the Portal? Also when un-selected it needs to remove the same amount from the "Total" field. With Thanks Mick

Hello, I am still on FileMaker 11 and am having a problem I'm hoping someone might be able to assist me with. I have a very simple database as an example with one table and two fields, date and customerid. I would like to create a field that counts the number of unique customerid's in my current selection and have been following the FileMaker knowledge base article here... Counting the number of unique values in a field The article describes 3 methods to do this, the 1st uses the ExecuteSQL method which won't work for me because it requires a newer version of FileMaker. The 2nd requires a sort I'd rather not do so I have been trying to do this using th…

I am creating a database solution that needs to read the text from a pdf in a container field and put it into another calculation or field for parsing. I have read many articles and can't seem to find a solution to something that a simple Mac Automator Action can do. I have done this in Filemaker for Mac (not Go) via AppleScripts. I need to do this native to Filemaker without plugins or external scripts as this will end up being on Filemaker Go residing on the tablet/phone. Is this possible?

I have a summary which is "Average of Amount" which gives incorrect value so I made a new file to figure it out. In my test I have 10 records in the found set which each holds a '1' in the Amount field except for one field which is empty. The result of this summary is 1. It should be .9. In Help I search for Summary and nothing comes up. It is not listed in functions reference and I found "Summary functions" but it only lists GetSummary. Ideas what I have wrong in this summary? thank you for reading

So I tried calc with Date ( 12 ; 19 ; Year ( 1951 ) + 65 ) and it says 12/19/0071 and that is not what I want. I have date field with person's birthday and I want to know what date they will turn 65. What am I doing wrong? This does not work either Employees::birthdate + Year ( 65 ). I suppose I could calculate the number of days in 65 years and then add it but I am just not understanding this. Ideas guys?

Hi All I have a Database that deals with Bills and due dates. Every bill has a due date and I need to calculate every day the status of a bill: Overdue, Short Term Due, Long Term Due, etc. The Get (Current Date) - Due Date (field) is not the right one, as I read in this forum, this function is not dynamic. My question is, which would be my best approach? I was thinking in setting up a Date Field in which I replace every single day the current date. What do you think? Thanks in advance for your answer. Regards Mariano.

I have a database that tracks employee jobs that are completed on a given day. I have a summary report that provides the total number of records for each employee, however, I wish to also show the number of days (unique dates) that were spent completing the jobs so as to provide a summary (by employee) with number of records and number of days. I've bene unsuccessful finding a solution to count unique dates. Ideas?

I have a database where the user enters a selection through radio buttons (HE, EFF, NI, U). 30 total questions labeled rdoIndicator1, 2, 3, etc... Before entering their selections, they are required to enter they school level (ELEM, MS, HS, ALT) In a separate layout I want to count all the HE for elementary, for middle, etc. I have a related table called ElemCounts, MSCounts, HSCounts, AltCounts with fields labeled Indicator1_HECount, ECount, etc.. the part I am getting stuck is with getting a calculation to work where it checks if the school is Elementary and rdoIndicator1 was HE, then again for EFF, etc... Any help is appreciated

I have a rudimentary question about optimization logic. I have been re-writing the database I use to run my cabinet shop. In order to minimize overhead I have eliminated as many calculated fields as possible. The calculations that do occur now are script based and, for the most part, are performed on the server side before downloading to iPads in the shop. There are not a lot of users at any given time and probably 300 fields in play for any given cabinet. My question now is about the overhead associated with Auto-Entered Data. Many of the pieces of math I use never change. The thickness of a drawer bottom, for example, is always 12mm. When performing calcula…

I'm wondering how to set fields up so that the prices for items purchased will fill in the COST field when the items are selected while at the same time having the option to fill in an alternate price. So if I indicate that Customer 1 selects Item A, the cost will fill in with $100 as I've pre-specified elsewhere for that item, but I could go in and make it $80 for one customer. Currently if I go in and alter the price in that customer's record, it also changes the price for that item for everyone who has bought that item, because it's a calculation field. But if I change it to a lookup field, then all the records that are currently filled in go blank, and there's too …

Hello, I've encountered a problem that I need assistance resolving. Background info: using FM starter invoice solution both on the desktop FM and FM Go for the ipad. I have the invoice details set up so that whatever is entered into the "item" field on the first line item of the portal is copied over to another field ("summary") by means of a script (go to portal row (first), set field). The issue arises when an invoice is created on the invoice detail on the Ipad- this layout only shows the most recent line item entered on the display no matter how many line items have been entered i.e. if I enter a "1" into the "item" field on the first row of the portal line …

Given that I have a table called Projects, and a table called Timesheets (which is related to Projects through a Project Unique ID field), and that the Timesheets table has a Date field, can I create a calculation field in the Projects table that will give me the date from the most recent related Timesheet record? (I realize multiple related records might have the same date, but it doesn't matter to me if it's one or many on the same date.)

Hello forum. I'm having a little trouble parsing a QR Code. The problem is the length isn't always the same, but the letters used to separate the data are consistent. In this example, how would I extract the value of 10.0 which is between the C and the H. I guess what I'm trying to figure out in words is (not so simply for me); "Take the value between C and H and put it in the calculation for the field CO2'. After that, parsing the rest would be similar. I'm ok with the parsing with Left, Middle, Right, and using Position, but I cant figure out how to get the result of the varying length between any 2 letters (C & H) in this case. I've seen example…

I need to count the number of occurrences of four numbers in a field, Price_Lookup, and place the four counts in four fields; Summary_Lookup1, Summary_Lookup2, Summary_Lookup3, Summary_Lookup4. The Price_Lookup field is in the dash_LINE_ITEMS TO. The four Summary values would be placed on a portal on the Dash layout, the numbers are 1, 2, 3 and 4, only four numbers, for all invoice line records for a set of invoices. Basically I would like to do a Summary>Count of the field but only if it contains a specific number. The is part of the relationship tree I am using to do other summaries. The key for dash_PRODUCTS to dash_LINE_ITEMS

I'm trying to figure out how to do a "Count" on a subset of records. I can do a search to find the records I need and do a count of the number of records found, but I also need a "count" on a subset of records. For example: I can do a FIND to list all of the widgets (1record per widget) I've sold in the month of November and do a COUNT of the number of records to total the number sold. I also need to count the number of Red, Blue, and Green widgets within that same list. i.e. November: 30 widgets sold; 12 Blue, 10 Red, 8 Green

Using the Hide Object When calculation engine feature I'm attempting to hide a button no matter what the state of the layout... Get ( WindowMode ) = 0 or Get ( WindowMode ) = 1 The first function occurrence works, no matter if I'm referencing 0 or 1 as a result, but the second function doesn't seem to work at all. Is OR the wrong expression here or is the calc engine expecting an IF statement? I have some hidden off-layout pop-up buttons that seem to appear when the user enters Find Mode (when I use 0 as the first result). I want these buttons to be hidden in every mode except layout mode.

Based on a large DB I have created a summary report (I'm not interested in the detail) which extracts products according to certain criteria and groups them under sub-summaries. The product at the top represents the most sales, and the product at the bottom - the least. I wanted to obtain a ranking no eg No1 at the top and had thought to define a field which would accept this number by inputting the rank using Replace Field Contents with a serial number. However, it inserts incremental numbers through the whole file rather than just for the sub-summary. For example: say, I have a file of 2000 different hats in 50 different colours. A red hat is the most expensive at $100,…

Well, I thought this was a no-brainer... I have a simple Count calculation field (number result, unstored) that works as it should, but I want to use a "Hide Object When..." text box--with an error message inside--to make the message appear when the count is zero. The first calculation that came to mind to use in Hide Object's "window" was: RESUME_PARENT::Inclusion_test__lcn > 0 It works, but only if I flip to another record then back. Is there a tweak in the calc I can add so it will update without having to flip records? TIA for your help!

I'm trying to implement an If statement in my calculation. It works fine as below but that limits found field values only to the specific string - "Part A". I need to be able to also catch values that may have text following the string I have specified, i.e. "Part A and something else". Any suggestions appreciated. If ( tableName::fieldName = "Part A" ; resultOne ; resultTwo )

Hello, New to Filemaker. Is there a way that I can create a table, or log, of entries inside a file? So, for instance, there would be a button for a "new entry". This then brings me to a couple fields, such as date, time, and notes. You fill them out, click accept or submit or another button, and it logs it on the file. This is useful for a log of times contacted someone, for example. Ideally I would then run a summary over the number of log entries and have that data analyzed by another program. I would also like to have it visible, populating a background with each new entry. Thank you

Hi, Can anyone help? I'm using the script below and after adding to the DB it has altered the date format ( payment date) to US MM/DD/ YY style. Case ( IsEmpty ( PaymentMethod ) ; " Invoice Status" ; TextStyleAdd ( PaymentStatus & " " & payment date ; Bold) & ¶ & TextSize ( PaymentMethod ; 10 ) ; PaymentStatus & " " & payment date & ¶ & TextSize ( " " & PaymentMethod; 12 ) ) How can I get this back to UK DD/MM/YY? I've tried using the Inspector and my original date fields used elsewhere on the layout are unchanged. I've put it on the layout as <<payment info popover>> (borrowed from t…

I have a field called Premium Summary which is a Total of a field called Premium within the same table. When I display this field Premium Summary in list view sometimes the premium is displayed, other times you have to click on each individual line to get the premium to display. Should I be using Running total or another way to get this to display 100% of the time?

Apologies if this has already been covered somewhere. I couldn't find anything related closely enough. And, it may be something simple that is just not occurring to me. I have a FMP 14 server-based database that keeps records for a number of individuals that share a single vehicle. Every time one of them uses the vehicle, they create a new record in which they enter the details of that trip. One field shows the difference in how much fuel was added by the individual that created that record compared to the previous record. If the previous record showed a full tank of say 10 gallons and the current record shows the current user also filled it up to 10 gallons, then t…

Hi everyone. So 9 years ago i started using filemaker. i started by using the attached file as a base as i knew nothing. Over time i have changed it quite a bit but still have a problem i need fixing. 1. On the jobs layout i would like it to show how many times an employee has worked on that job (In the portal). i have managed to sum up how may time entries (total) and how many employees (total) that worked. i have tried calculations and summary but still cannot get it right. thanks Rob. Job Time Tracker FM Forums.fmp12 2.zip

We have contracts with StartDate and ExpireDate (both dates). We have single-record Admin table where NumberOfMonths is stored (number). I wish to pin down 'what is a month'. Well, not me actually, but the city using this file needs to pin down their interpretation so there will be a checkbox in the Admin table which applies this theory: If the day of ( StartDate + NumMonths ) is not the same as the day of the StartDate, consider adjusting the date according to flag field in Admin (adjustDateDownFlag) FileMaker handles automatically adjusting FORWARD similar to: Date ( Month ( StartDate ) + NumMonths ) ; Day ( StartDate ) ; Year ( StartDate ) ) What I …

Hi,. First post here so sorry if I don't give enough info. I have a fire dept database I made and manage. In our Event Report table I have a Timestamp field for recording when fire incidents occurred. I also have an Attendance table used to record members attendance for each call, the Event report table and attendance table is replaced to the Event Report table. Normally when the scrip runs to create an attendance record for a member at a call it currently sets a field in the attendance record as "Incident". i need to modify the script set to Determine the time the call came in and set the field in the attendance record as "Incident Day" or "Inc…

Not sure if this is the right place in the forum to post this topic but it appears to be the closest to what what i think I want anyhoo: I am just trying to discover and disect the Let Function. and have a "problem" I thought it might be useful in the following siuation but I am hust buggered as to where to start. The problem for this example is; I am working on a small POS for a friend they want the possiblity of having a voucher print if a certain amount of money is spent in 1 transaction. So for the exercise i have set up a table (related it to Sales) to input 1 promotion that has an ID, Description, Barcode, Min Spend, and Active Status. On th…

I've searched high and low and am simply not getting an eloquent solution to this problem, though it seems like it should be common need. I have four unique fields which may contain "Yes", "No" or empty in any combination of the three states. I need a calculation that finds whether ANY one of the fields contain "Yes". Thooughts?

Ok, so I have an invoice thing that calculates the total of the invoice by having a summary field that add up the cost of all the line items on it and displays it in the corner. The problem I have is if I give someone a credit on an invoice it displays correctly in the corner, but in the script that fills out and prints the invoice a value gets assigned to be the summary but it sets the wrong value! I do a credit on a invoice by simply adding a line item with a negative cost, this gets added to the value I have in the corner to show me the total, but on my printed invoices (different layout with different) the total on it is assigned to be the total on my original, but it…

Hello There! I work in a healthcare HR training department. We provide courses for our hospital staff. I inherited a FM database that is used to track all enrollments, attendance, test scores, etc. I have never attended any FM training myself and have not been in a position to make too many changes to the already existing database, so please be kind. Our database has 3 tables: Courses, Contacts, Registration. We have a portal linked to the Registration table which links Courses and Contacts. Courses are added to a students record and once they complete a class, the "Attendance Status" field is updated to "Attended". I need to develop some way to identify w…

Anyone interested in partnering together to help small businesses create online forms on a website, then send that link to anyone who wants to fill out that form, then notes can be added to that form. The small business owners can work with these forms like they'd a spreadsheet. This enables automation of the business' workflows in a way that's still familiar to FileMaker Pro users. The mobile web app is ready for anyone to start using. Check out a demo at https://formgram.parseapp.com/formPage?formObjectId=ejj8YHJIZy Formgram is 100% free.

I have a calculation setup that gives an aging amount based on current date and date entered for when the contract was signed. I swear it used to work ok but for some reason it no longer does, the number of days is off vs what it really should be. As can been seen in the screenshot examples taken just now (11/03/15), the Days Old shows 19 days when in reality its been 34 days since 10/01/15. Any ideas why this wouldn't be working? Seems like a pretty straight forward calculation. Ok so I did a test of deleting the "Contract Signed" field and the Aging when to "blank" as it should, then I added the date of 10/01/15 back in and it …

Field not validating by calculation correctly when entering through show dialogue Script step. I am trying to force entry of five characters into a field through a show dialogue entry. Also not able to clear the global entry field correctly using the set field script step before showing the dialogue. I can get the field to validate correctly only if I do not use the validate by calculation option in the field definition. Please see attached. Using FileMaker Pro 12 Advanced Win/Mac Thank You for any help with this. Validate Dialogue Entry.zip

Hi everyone, I have a field for for hourly rates, price per unit is $100, however, subsequent hours are half of the original unit price ($50) I need a auto calculation that reflects the actual amount, based on the number of hours inputted. (e.g. 5 hours = $300) I'm raking my brains on how to perform such calculations. Anyone can shed some light to me? Appreciate it!

This could be one of those easy ones..? Two tables.. I have a text field in the Product table, called mlfk_DepartmentIDs, which contains paragraph-delimited numerical Department IDs. That field is then used as the left side predicate of a relationship to the Department table, into the Department table's pk_DepartmentID field (its primary key field). Department table also has a text field called DepartmentName. From the perspective of Product, I want to show a comma separated list of all DepartmentNames, for each department ID in that multi-line key field, mlfk_DepartmentIDs. I am familiar with using a Join table, but I want to see if this can be solved with just a calc…

I've been trying to adjust a custom function "removeduplicates" from the brian dunning site to do the following.... I have a calculated field which returns a list collated from a relationship in the form of a block of text. It has 3 items per line - date, time and subject - each element separated by a tab, and each line with a carriage return 10/24/2015 06:00 Alpha 10/24/2015 07:00 Beta 10/24/2015 07:00 Charlie 10/31/2015 06:50 Delta 10/31/2015 07:30 Echo What I want returned is... 10/24/2015 06:00 Alpha 07:00 Beta 07:00 Charlie 10/31/2015 06:50 Delta …

I've got a list of television broadcasts, with a parent Programs table - I can view the related Broadcasts of a given Program. Now I want to aggregate the Broadcast channels, dates and times to a field in the Parent record - aggregated by Channel. I can obviously produce a List fairly easily, but given the child data: Channel Date Time TEN 11/11/2015 14:30 TEN 11/11/2015 16:30 "TEN + 2" 11/11/2015 16:30 TEN 13/11/2015 08:30 TEN 15/11/2015 10:30 I would love a result like: TEN, 11/11/2015, 14:30, 11/11/2015, 16:30, 13/11/2015, 08:30, 15/11/2015, 10:30 TEN + 2, 11/11/2015, 16:30 Note: The data-set can be sorted by any/all of the three fields. CustomFunction? Calculati…

Hello everyone. I am new to FM Pro, so I bring my apologies if my question turns out simple and stupid... Table 1: Companies: Comp_Code ... Table 2: Loans: Comp_Code Loan_ID Amount Currency ... Table 3: Currencies Curr_Code ... I have a portal showing all loans per company based on Loans table (related to Companies through Comp_Code field): Comp_Code Loan_ID Amount Currency AAA 1 100000 USD AAA 2 50000 USD AAA 3 200000 EUR AAA …

Dear all! I have 2 tables: Companies and Ownership. The first contains fields: Comp_Code ... Country ... etc. The second contains: Comp_Code, Mother_Company ... Country. First 2 fields lookup company names from the first table (Companies). Country field in the second table is calc field and must lookup country name for Mother_Company based on Country assigned to a company in the 1st table. Can you advise on correct relationship between both tables and proper Lookup function script for my calc field? Thank you. Example below: Table 1: Companies Comp_Code … Country … AAA USA … BBB … USA …

I have a simple solution for entering students test scores. The enrollment table has 4 fields namely : subjects, score, level and semester. The subjects values are gotten via a value list, same with the Level and Semester fields. Records are entered from the students layout via a portal My challenge is how to avoid students from entering same subject more than once, because this would affect their calculated result.. Thanks

I'm wondering if there is a way to achieve the following using relationships instead of additional aggregate function fields. I have a TNC Driver's database (Uber, Lyft, etc.) with a parent table (Shifts) and child table (Rides). One Shift record is the equivalent of one day's driving. A Ride record is each occurrence of picking up and dropping off a passenger. Each Ride record has fields for Service ("Uber" or "Lyft"), Fare amount, Date, etc. I have various calcs in the Shift table [ Sum (Shifts_Rides::Fares), Count (Shifts__Rides::_kp_RideID, etc ] that aggregate Ride data for each shift, such as the Sum of all fares or the Count of all rides. I also have 2 additi…

I have a script that loops thru [ go to next ] tab designated fields on a layout and generates a substitution list as such ; "[\""&Get ( ActiveLayoutObjectName ) & "\"; " & Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName )&"];" appending "Substitute(report flow data::report" and ")" at end (report flow data:: report) being a template loaded with "key words" example; Substitute(report flow data::report ["rdate"; report flow data::flowtest_date]; ["rgpd"; report flow data::gallons per day; ["syaddress"; selected system::c_NameAndAddress]; ["wdesc"; selected_water::c_waterDescBasic] ) the key words to be substituted a…

Hello All, I have two tables Materials Table and History Table In my Materials Table I Have the Following fields Quantity Received Quantity Used Quantity in Hand History Table Quantity Dispensed Quantity Returned Can you please help me find the right formula on how to calculate my Material Balance. For example If I have a 20 Kg Received ; then they Dispensed 5 Kg, Then My Quantity return will give me 15 Kg ; The formula that I'm using to find my Quantity on Hand is If ( IsEmpty ( Sum ( History::Units In ) - Sum (History::Units Out ) ) ; 0 ; Sum ( History::Units In ) - Sum ( History::Units Out ) ) The result is not matchin…

Very fundamental concept query. This might not belong here, but I'm here to discover if I am thinking 'case' and 'if' wrong. Quite often I come to a situation with 2 tables (something like this): Table A fields: ID_A, RaceID, Horse Name, Time, Finish Table B fields: ID_B, RaceID, Winner, Second In Table B I want to populate 'Winner' and 'Second'. I would do a simple look-up for 'Winner', but then I go for 'Second' and I start bumbling around and thinking like this (just English, not FM-ese): If Table A RaceID = Table B RaceID and Fin = 2 then give me the Horse Name. Then I start fumbling around. I check the manual, the Missing Manual, videos ... and then I start thin…

Hi, After the weekend races and all the results are imported into FM, I mark (now by hand) the “Key Races”. I’ll find each horse who either won or finished 2nd. As in this screen shot of Cherry Summer you can see he won his last race on Oct 3. (1) Then I mark the preceding race. (now I’m using a ‘K’, but I intend to change it to a number — different problem). The point is to show which race produced winners. The screenshot shows I have put a 'K' in Cherry Summer's race on may 31 and changed the 4 to a 5. Then I find matching records for RaceID (31may15tok5) on the record I just tagged, and count the ‘K’s and enter the number in KRG. So before I updated this there …

Hi everybody, please I need tips to solve this problem in the table you can see the partial result of search operation operated on "serial" field. I need to have partial count in the field "tipo" for each "E", "R" "N" type, how many they are? I tried relation, i tried to count partial, but i not solve it out. please can anybody suggest me out to fix?

Hello, I have 2 tables (for these purposes) that look at employee records. The first, Contract_Records has one record per person per week and records extensive information about each employee week. The second, Contract_Weeks has one record per person with one field for each week to record either on or off contract. I need a calculation that finds a record in Contract_Records where Contract_Weeks::EmployeeID=Contract_Records::EmployeeID and Contract_Weeks::WeekID=Contract_Records::WeekID. Right now I am using Case ( Contract_Records::week_ID="1"; Contract_Records::Type; "NA") and it is returning the correct value for Week_ID 1 but not for the subsequent fields such as C…

Hey guys, i'm pretty new to Filemaker, so I'm sure there is an easy solution to this problem. Each invoice record has a "total" field, where it calculated by "Invoice Total" - "Payments". This "total" field is not always zero, so I want the amount of this total to appear in the next month's invoice. So on the Invoice table I created a calculation field named "Previous Total", but how do i retrieve the previous amount? I have to call back the previous invoice for the specific customer, i.e. if the new record is Invoice Number 50, I may have to refer back to invoice number 40 because the other 10 invoices in between is related to a different customer. Thanks!

I have a system for calculating golf handicaps. I noticed that a handicap of 14.5 rounded to 14 instead of 15. A players playing handicap for a round of golf is calculated as Handicap Index / 113 * Course Slope Rating. In this case the Handicap Index is 14.5 and the Slope is 113. The playing handicap is 14.5, which rounds to 15. So I opened a data viewer window and entered Round(14.5, 0). The result is 15, which is correct. Again in the data viewer I entered Round(14.5/113*113) and the result is 14. That seems wrong to me. I entered 14.5 / 113 * 113 on my calculator (an old Casio), and strangely, the answer is 14.4999999. On my phone calculator and in excel, 14.5/…

Hello, I would like to know how to place the current time on a section of my web page, display it a hh:mm am/pm, and for it to change every second without refreshing the browser manually. I would also like to do the same for the date. Thanks in advanced!

I have a List view form set up to enter data for a cash flow analysis. All of the entries are entered manually, except for section Section III., which is a calculation base on figures in sections I. and II. The list is dynamic, will be adding and deleting lines. The Depreciation calculation would be Hardware items that are marked Non-Leased and would be multiplied by a factor depending on the year. Amortization calculation would be Software items that are marked Non-Leased and would be multiplied by a factor depending on the year. How can I set up this calculation in section III. Year 0....Year 5, while all of the other values are being manually entered. At the end of…

when I export data from a relational database it does not export all data. i.e. I am running a session data base that is related to a speaker database. when I export the description an objective field plus the speakers the export contain all the field however when a session contain multiple speaker it only bring the first listed speaker not all. any Idea how to do this 2015_conference.fp7

What I want to do is have a calc field look at a text field and extract the account name that's left of the "@" sign in its e-mail address. Since the right side of the "@" sign is the same server address 99.9% of the time, at first I thought of using the Filter command just to filter the "@lcsc.edu" part of the address but sure enough, that .1% will appear so that command won't work. I also thought of using LeftWords ( Email Address ; 1 ), but that falls flat with hyphenated names. I found this link (http://help.filemaker.com/app/answers/detail/a_id/6481/~/example-of-how-to-extract-data-following-a-special-character-in-a-field) that comes close, but it's through a scrip…

Hi, Since I could not use 10ths of seconds in FMP I’ve been converting the time and presenting it this way [Time*24**60*60]. This is fine by me, but I suppose some users might not care for it. Is there a way to reverse this in FMP — to take, i.e., 73.3 and get back 1:13.3 or 1.13.3? Thanks, Paul

I have a script that looks for a number of values in a field. I one of the values is detected a certain counter is set, depending on that value. If no value is detected, still another counter is set. In one record I found this problem: somehow the field being tested had gotten a space entered...the script could not find an action for this and skipped the record, not setting a counter for the null field. My question is, is there a way to script this test so that a space (or any number of spaces) will still be considered a null? Thanks,

I'm having issues with my database users copy and pasting text into fields within my database that is either different font than my default database font or different sizes/etc. Is there a setting that automatically formats whatever a user enters into a field, to the default font setting for that field?

Hello, I have a database with a lot of field with a button that can be sticked/unsticked. I decide to create a calculation field to wrap up all the content sticked/unsticked. For example: Playstation 1 √ Playstation 2 x Playstation 3 x Playstation 4 x Xbox √ Xbox 360 √ Xbox One x Wrap up field: All console: Playstation 1, Xbox, Xbox 360 This is how I created the calculation field: if ( isEmpty ( Product::Playstation1 ) ; "No Playstation 1" ; "Playstation 1" ) if ( isEmpty ( Product::Playstation2 ) ; "No Playstation 2" ; "Playstation 2" ) if ( isEmpty ( Product::Playstation3 ) ; "No Playstation 3" ; "Playstation 3" ) if ( isEmp…

Hi I can't find an answer for this. I have a fairly simple FMP12 db with expenditure allocated to categories and sorted by fortnight with a summary field for each category. I can't seem to find an easy way to add the results in the summary field into a separate field that I would call a balance field. Is there an easy way? Thanks

I am relatively new to Filemaker and am trying to make a simple database for taking inventory of fabrics. I have a table with the fields Quantity added, Quantity issued and Quantity in stock. I would like the Quantity in stock field to be Quantity added - Quantity issued for the first record and for subsequent records I would like it to be Previous value of Quantity in stock + Quantity added - Quantity issued. I don't know how to do this in Filemaker. Here is a screenshot of my table as it stands now. I can do this quite easily in Excel. In the example below, for the first Quantity in stock value, I have the cell F2 = C2. Subsequently (F3 onwards), I have the val…

Hi, I have a calculation that starts by getting an "id_user" value from the table "checkins". Instead of returning this id number, I would like to use it ("id_user") to look up a matching "user_name" in table "users". Is this possible? Right now I solve it by the common practice of creating a popup menu where the first value is the id and the second the name. I then only show the second field. However, I was curious if I could somehow do all of it in the calculation field so I could insert the name via a merge field and not having to use a popup menu. Thanks, Phillip

I'm starting to use the Let Function and need a bit of assistance please. I'm creating a variable based on the results of an ExecuteSQL(). It is returning the date from a table (so it can be used later) based on criteria selected by the user. The date in the table is stored as a date field as dd/mm/yyyy. However, when the query returns the date field and populates the variable its bringing it back as yyyy-mm-dd As a result when I want to use it further down I'm not getting any results. is there a way I can get the query to return the date as dd/mm/yyyy or convert it to this format to use later? TIA

Hi all I need to extract from my students PDFs certain dates they appear originally as follows: Printed: 28/08/2015 01.15 the seconds are missing and the separation is a full stop rather than a ":" In the TOOL menu I successfully tested the calculation field and it works throughout the hundred files I have in the MONITOR window This is the function: Substitute ( Middle ( Prova::Mail ; Position ( Prova::Mail ; "Printed: " ; 1 ; Prova::PcntPrinted ) +9 ; 30 ) ; ["." ; ":"] ;[¶ ; ""]) & ":00" The Mail text field, extracted from PDFs files, has several, "PRINTED: " patterns: hence I use the fie4ld "PcntPrinted" which returns last time the "PRINTED: " pattern appe…

Hi, I made a simple FM file for my webshop. Each sale is a record. Each records has 5 checkboxes representing the 5 products i sell. The values from the checkboxes come from a value list and are product names. I'd like to have a running sale count of each product over all sales i did. How can i do that? Thank you for your help.

I have a table with 2 fields, one called Charge and another called type. The Type field is a drop down menu with the following selections: Debit, Cash, Check, Finance So the table looks like this: Debit $500(Charge Field) Cash $200(Charge Field) Debit $300(Charge Field) Total (Summary Charge Field) I'm trying to create a field that just summarizes the Charge Field when Type = Debit. So I can break down and summarize each type separately. What is the best way to do this?

Main customer table has portal that has now grown fairly large- about 15,000 sales records related to about 2000 customers. The month end report layout includes summary field in the footer. As it appears sometimes, when that month end report is selected, FM sometimes goes into a long sort. I know this has been asked on the forum, although I have not been able to grasp yet but how is that long, locking-up sort avoided? A calculation field I suppose? Not have some field sorted? I'm not a professional developer but I have some knowledge. Could someone point me in the right direction? Right at month end when we are slammed- this just shuts us down for quite a while. T…

I am trying to figure out how to extract a portion of a filename from a field, when the filenames differ. It is for a file database. This would be easy if all the files are named the same, but we have multiple different filenames based on the type of file it is. So the idea is I have a "shot_name" field that pulls in the name from the file I am importing into FileMaker. For instance, here are two of the filenames...one is for a traditional shot and one is for a development shot. Traditional Shot: BOG8515_150731_Line-Up Development Shot: BDC_dev9150_150721_Line-Up Essentially the only portion of the shot I want is the following for each: Traditional Shot: BOG8515…

I have a field called Producer Code and when I enter a layout I would like to randomly enter 1 of 3 numbers into this field. Either 2, 3, or 9. Whats the best way to do this?

Hi Forum :). Always wondering/trying to learn if there is an easier way. This does work but it seems I may have over complicated the calculation. Basically I am selecting from a drop down (global), a value in a value list that will give me a range of aging criteria. Based on that criteria, convert to dates to perform a find (and sort). This is for a basic aging report for finding balances due. The calculation is for the Set Field script step. Here's the Value list: All 0-15 16-30 31-45 46-60 61-90 90+ And the screenshot shows the calc. I figured 90+ and 'All' had to be calculated differently (for the way I'm trying it), and the other ranges can be converted th…

After a long separation from Filemaker I'm back and I have a new project. I am creating an inventory system for a high end retailer where they sell unique individual products. I hope that you will be able to help me get the concept right from the beginning. Generally there are about 10,000 stock items of these half will be non repeating items that we be reported on by a style of Item. There will be about 5,000 items that will be repeated but in total there will be only about 10 shipments in for each and 20 to 30 units in total of them. Finally there will be about 300 items that would have hundreds of purchases and sales. There will be manufacturing where one piece w…

I'm trying to count related records (within the same table) where two values are met. The first value is a serial number (Serial), the second value is a "1" (flag). For example: Record 1 / Serial: 12345 / Flag: 1 Record 2 / Serial: 12345 / Flag: 1 Record 3 / Serial: 12345 / Flag : (empty) Record 4 / Serial: 6789 / Flag: 1 Record 5 / Serial: 6789 / Flag: (empty) Records with serial number 12345 should have a count of 2. Records with serial number 6789 should have a count of 1. To quickly explain, this is for conditional formatting. I have a portal with groups of records that fall under a "header" row. If one or more items within a group (joined by Serial) have a 1 in t…

Is there a simple fix for a field to detect that it only has space(s) in it (" ", " ", " ", etc) and make the field blank if it does?

I'm trying to create a calculation field that would hold count of fields with value in them (out of a range of 6). For example, I have fields: fld1, fld2, fld3, fld4, fld5, fld6. If fld1 has anything in it, my calculation field would display 1, if fld1 and fld2 had something in them, calculation would display 2, etc. Calculation field would be blank if none of the fields would have values in them. What's the best way to approach this?

I have a text field that can be answered in one of four ways. Is there a field definition method of calculating which percentage of the found set of records have value A, which percentage have value B, etc.? So far the only methods I've thought of to do this require a script, but for something that I would anticipate is fairly common, there would seem to be a more direct method. Any suggestions?