Calculation Engine (Define Fields)

Hi, I have a filtered portal which lists Jobs::Job Sub Total. How can I get the total of all the Jobs::Job Sub Total fields in the filtered portal? Not sure if its important but the filter is: Jobs::Job Status = "1" or Jobs::Job Status = "3" or Jobs::Job Status = "4" or Jobs::Job Status = "5" Thanks,

I have a main table and a separate table called the checklist which has 7 different people, each with their own checklist. I'm trying to create a little red number (like the red email number on the bottom of the Mac dock) that shows in real time the number of checklist items for each user in the main table. Is this at all possible without slowing down the system?

Sorry if this has been asked earlier, but I've searched extensively and have been unable to find the answer to my dilemma. Say I have a particular field called "car type", and each record will contain either Ford, Honda, Mercedes etc; how do I create a summary field that will contain the total number of Ford's, Honda's etc... Thank you

Hello, I started a database for a volunteer fire department back in 2012 and they have been storing preplan data for individual addresses since they started using a field "Address/Location". They are now going to align themselves with the format that the Police department is currently using which has separates fields for the address number and the address street. I have created two fields "AddressNumber" and "AddressStreet" and thing a replace field contents step is what I want with a calculation to pull the relevant data into the new field. I'm having trouble with the calculations to pull just the address number into the first field and just the address s…

Let’s say we have two related tables: “Invoice” and “Invoice_Item”. We could create a calculation field in the “Invoice” table called “total_amount” with this formula: total_amount = Sum (Invoice_Item::amount) This field would have a negative impact in performance when appearing in the layout, since it would have to be defined as unstored, because it’s referencing a field from a related table. Now let’s suppose this field is not used for any scripts, tooltips, conditional format, etc … would the performance of the database be negatively affected ONLY when this field appeared in a layout? In other words, would adding an unstored calculation field to a …

Environment: MacOS 10.6.8, Filemaker Advanced v11.04, Troi File Plugin 6.0 (downloaded 212-06-15) I have a severe problem with these functions. I need the values to establish drag and drop using the Troi File plugin, but the problem is with the basic FM functions. Note that all fields I need are only present once in the layout. Here are the script steps I used to get the dropzone boundaries: Go to Field [ BOOK::CoverImage_c ] // container field Set Variable [ $field1; Value:Get ( ActiveFieldName ) ] // just for debug Set Variable [ $dropzone1; Value:FieldBounds ( Get(FileName) ; Get (LayoutName) ; $field1 ) ] Go to Field [ BOOK::Dragnote_gt ] // global t…

Is there any difference in terms of performance between a calculation field (stored and indexed) and field defined as auto-enter calculated value (indexed)? For example, we have an “INVOICE” table, with a field called “date_invoice_sent”, and we’d like to have a boolean field called “is_sent”. The calculation would be “not IsEmpty(date_invoice_sent)” So we have two options here: - Calculation field (stored, number result). - Number field defined as “auto-enter / calculated value / do not replace … unchecked”. Would there be any difference in performance between the two options? thanks in advance!

Hi, It's my first time using executesql and I'm struggling with getting the results to show in my field. I basically want to get the latest record, based on the "obsdate" field for a given staff member from a table where they have numerous observations. I then want to show information from this record on a portal which will show the latest obs date for each member of staff which can then get sorted by date. the code I've written is below but would return the key and date. How do I then loop through this to return the information I need? Thanks in Advance. Let ( [SQLString = ExecuteSQL ( "Select [__fk_Staffteacher], max(ObsDate) from tbl_TeacherEvalua…

Could someone please help me to extract a part of a URL an put it in to a new one. In this URL I want extract the youtube videoID, and it is located at the end after = operator in blue: https://www.youtube.com/watch?v=X2k1EOpC6Mk The new URL would be: http://img.youtube.com/vi/X2k1EOpC6Mk/maxresdefault.jpg I think about a calculation field with the extracted value in between, something like: "http://img.youtube.com/vi/"& extractedfromURL_field & "/maxresdefault.jpg" I am not sure how you extract the videoID. Thanks!

I have two container fields, one named 'Image' and another named 'Image Thumbnail', the latter which uses this calculation resize the image: GetThumbnail (Image; Total width cm * 5.1; Total height cm * 5.1) This works fine, except that 'Image Thumbnail' is only created immediately after importing an image into the 'Image' field. I would like to force regeneration of 'Image Thumbnail' whenever I change any parameters of the above calculation, such as the 5.1 factor. How can I automate this?

I wish to replace entry via field calculated value from: 2/13/2017 2-13-17 ...etc to: 02/13/2017 Including the "leading zeros" month, day, year format, and use of "/". Anyone have a canned calculation I can plug in here? I wish to go beyond simple data formatting facade.

We are a high end jewellery business and I am about to start a project to cover the entire business. The first part of this project will be calculating stock in multiple branches. I will need to be able to calculate the stock values and quantities for different dates at all times, so for instance today is 31st January 2017, how do my stock levels compare to 31st January 2016. Most stock is made up of one off pieces although some repeating products might have 20 years of history and up to 100 sales. There would generally be up to 15,000 different articles in stock at any one time, but I feel I should be thinking about growth in this area. I know that I am aski…

Hello, 2nd post here on the forum, thanks to everyone for their help! I have a few databases for local fire departments and they all reference their incident reports with an incident number. They like to have this number start at 1 on January 1st and restart at 1 each year. I have a field (incidentYear) that auto enters the year of the incident. I also have a field (IncidentNumber) which the departments enter manually. The number starts at 001 and they manually increment up 1 number for each call. The IncidentYear field and IncidentNumber field are then combined in a calculation (IncidentNumberDisplay) field so we can sort, reference, and search for them …

Hello, I have a Case calculation that does not return what I expect wd1 = width of image in container1 , ht1 = height of image in container1 wd2 = width of image in container2 , ht2 = height of image in container2 A script would open up a popup window sized to the parameters of the image in the container. This is how it starts: Set Variable [ $contfield; Value:Get ( ScriptParameter ) ] //which is the name of container1 or 2 which it gets correctly Set Variable [ $ht; Value:Case ( $contfield = Untitled::container1; Untitled::ht1; $contfield = Untitled::container2; Untitled::ht2; "ERROR") ] Set Variable [ $wd; Value:Case ( $contfield = Untit…

I have a car rental solution and for rental of short term one to two weeks i just need to create a new record one by one which will appeared in my calender View layout via Portal. In case, the customer decided to take the car for one year, i can't create 365 new records, It their any possibility to create 365 records automatically by selecting Start Date to End Date? Thank you.

I have 3 fields and the first field is Amount the second field is Deposit and the third field is Balance. Generally my calculation should be Balance = Amount - Deposit If i insert the Amount, example $100 and the Balance automatically changed to $100. I need the Balance remain empty unless i insert a number in the Deposit Field. In my outstanding due report, if their is no Deposit, the Balance field should remain empty. Any help.

TOTAL= MSRP - (MSRP * DISCOUNT) - CC FEES) - COST This is how I thought it would go in my head but I'm obviously doing this wrong. I have no clue about nested operations with parentheses. The goal is to output the MSRP amount minus the discount amount...say 25% then pass that number to the CC Fees which is say 2.25...then subtract the cost and end up with the total. eg: MSRP= 100 Discount = 25% = 75.00 - CC Fees which are 1.69 on the 75.00 - the Cost which is 45.00 leaving a total of 28.31 Trying to do this all in one calculation.

I have two fields called MAP and Price. I have another field called Profit which is a calculation of (MAP - Price). However for some records the MAP isn't required and is therefore empty or has 0. So of course profit ends up being calculated as a negative in those instances. Is there a better way to make that calculation so that it ignores the empty or 0 MAP field?

I have a vendor who has defined their shipping charges thusly: < 1lb. = 8.99 1.01 Pounds to 10.00 Pounds = 8.99+.50 per pound 10.01 Pounds or more = 9.99 + .40 per pound I have a field called Billable weight (in Lbs.) which is essentially just the dimensional weight pre-caclulated. What formula would I use to calculate shipping charges based on my billable weight field?

I've done a lot of searching on this and can't find a solution. All I need to do is count how many records in one field has the word "clacorp.org" and also the word "sadccf.org". The field only has two allowable entries, which are: "clacorp.org" and "sadccf.org". I would like to get a count of the "clacorp.org" entry and put the number count in one field and get the count of "sadccf.org" and put the number in another field. I tried: ValueCount( FilterValues( List( domain_name); "sadccf.org")) and other caulations but can not get the numbers I need. I am only using one table. I need to know how many clacorp.org emails and how many sadccf.org emails. Seems simpl…

Hey... I need help with a date function. The NJ Tax rate has changed and I never planned for it 12 years ago, simply because it never changed. My easiest solution is to work off the invoice date. Prior to 2017, tax rate is .07. In 2017, it will be .06875 and we know that 2018 will be .0625. In hind site, over 10 years ago, I would have field set the tax rate into each invoice, but working with what I have, the best solution is to work off the "Invoice Date." I can sort of see the code.... Case ( Invoice Date > 1/1/17, .07, Invoice Date > 1/1/18, .06875, Invoice Date > 1/1/198, .0625) Something like that. All help appreciated and this is n…

I have a date field and I'm currently sorting all of my records by year is 2016 then 2015 then 2014 then etc etc. I would like to sort this by current year i.e. 2016) then all other records 2015, 2014, 2013 together. What is the best way to accomplish this? Thanks

Hi, I wonder if anyone can help. I have a folder on my desktop called “quotes” and have a working script that saves quotes in a PDF format to that folder. 1). This works just fine… Get ( DesktopPath ) & "/" & "quotes" & "/" & Quotes::Quote_Pk & "-Quote.pdf" 2). But can anyone tell me why this doesn’t… Get ( FilePath ) & "/" & "quotes" & "/" & Quotes::Quote_Pk & "-Quote.pdf” …using the second I get the following error message… I need the second script to work because we have had to move the database as well as the "quotes” folder, to another location. Any help would be greatly appreciated. …

So person performs a find for part of the text they wish to find. The resulting display should bold just the part that they searched for in that field. I have tried to search for examples but don't even know what to search for. Hints would be appreciated. I think I need pattern count but that just gives me a number.

I did some googling, but could not find an answer, so I hope some of you can help me out. Here's the thing: I have 2 tables with a relation. Table 1, 'BOX' has the field 'BoxColor' with 3 records: Red, Blue and Green. Table 2, 'CONTENT' has the relational field 'BoxColor' and the field 'NumberInBox'. there are several records with different numbers (let's say 1-9) in the different colored Boxes. Now in the end, I have 3 Boxes with Numbers in it for example: Red: 1, 3, 3, 5 and 8 Blue: 3, 2, 8 and 4 Green: 5, 4, 7, 8, 2, 2 and 1 Now my Question: How can I list/find the missing numbers (in the list: 1-9) So the answer should be like: Red Missin…

OK I admit it ... I'm stumped. I'm not a FileMaker professional, merely an enthusiastic tinkerer. I'm trying to create a feedback system where a series of standard questions are checked. Based on this, those questions answered 'yes' are combined together and those answered 'no 'are ignored. I have created 2 fields per criteria, 'Preset text' and 'preset question'. So ...Preset message = (if preset question 1 = yes, include ‘preset text 1’ else, blank){cr} (if preset question 2 = yes, include ‘preset text 2’ else, blank){cr} …. (if preset question 10 = yes, include ‘preset text 10’ else, blank){cr} I've managed to get the display of answers based …

I have an inventory database that shows when the price was updated by a user. The PriceMod field is a timestamp that enters the current date based on a script trigger from the price field being modified. I want to be able to hide the pricing (which is a calculation field) on the count sheets for any items that have not had their pricing updated since Feb. 1, 2016. This way the data entry people know that current pricing still needs to be entered for that item.

Hi there, I realise that this section of the forum may not be the best for this post, but after going up and down the list, I thought I'd start here. Moderators, please feel free to move it if you see fit. I have for some time looking to be able calculate tide tables for any given location for a long time now. There is very little I can find in terms of actually calculating them with the necessary parameters - most of them are pre-calculated and ready for download. Quite by accident, I came across the page below and wondered if anybody by chance was interested in these types of calculations and might have something - at least for FM. Cheers, Gre…

Hello, I am new to FileMaker and still trying to find my way around the scripting language. Even though the scripting language is quite rich, I don't feel as comfortable and flexible as I do in lets say VB. To my query now; I have designed a solution for my company which processes invoices. These invoice forms input numerical amounts tied on budget codes for each department and in turn are stored in a different table. As you can see on the image attached, I cannot find a calculation function that will sum all the amounts for each BudgetCode into AmountAllocated. AmountAssigned is the yearly department budget predefined. I will be grateful for any sugge…

I'm a dentist. I'm trying to generate written treatment plans for patients to have as a treatment estimate and for our records. People have 32 teeth, therefore I have setup a field (ToothNumber), displayed as a checkbox set utilizing a value list = 1 through 32. When I click my CROWN script button, it opens a selection window wherein I choose the tooth number from said field (1-32), the material the crown is to be made of (porcelain, gold, zirconia, etc.), whether I want to use lab-processed temporary crowns, and so forth. If I have only one tooth number checked and run through my nested IF statements, which looks up the official description for the type of cro…

I have found a number in a summary field which calculates the average I require. How do I round this number up or down - I have the formula I need what I don't see is where I can do this e.g. it is not available when on defines the field or by setting the number format or in Replacing field contents.

Hello guys & gals, I want to get help to calculate the vacation allowed base on the condition below Years of Service Vacation Allowed 1 - 4 10 days (2 weeks) 4-5 12.5 days 5 - 9 15 days (3 weeks) 9-10 18 days 10 - 19 20 days (4 weeks) …

Can anyone advise please? I have field data in the form of (7 digit + 4 digits + 5 digits) (1111111-12-11111) I want to automatically separate this string into 3 separate fields... I have discovered how to so with the 1st 7 Using "right" (Field name 7) and likewise for the "left" 5 Digits How can i grab the middle 4 (-12-) in my example. Thank you for any help offered.

I currently have a field that takes the first line from an address field (could be a law firm or an attorney's name): GetValue ( Customer ; 1 ) (for example, John Smith, Esq.) I also have a second field which takes the data from the above field and, if that address line is an attorney's name, removes the trailing ", Esq." leaving only the attorney's name: Case ( Right ( PDF_BillTo ; 6) = ", Esq." ; Left ( PDF_BillTo; Length ( PDF_BillTo ) - 6) ; PDF_BillTo ) (for example, John Smith) Is there a way to consolidate both calculations into one field so it's more efficient? I can't seem to wrap my head around this and it's driving me nuts. …

I need to be able to standardize the contents of a field to just the characters after two letters, including the two letters. It's easier to give a few examples rather than describe the calculation: R01AR064810-01A1 -> AR064810-01A1 AR064810-01A1 -> AR064810-01A1 DA035813 -> DA035813 F32NS084680-01A1 -> NS084680-01A1 14SHSP603-01 -> SHSP603-01 T32AI007524 -> AI007524 In other words, the calculation needs to examine the text string, find the location of two letters next to each other (there will only be a single occurrence of two letters next to each other in the string), then delete everything to the left of the …

I'm trying to create a simple calculation that returns 1 (true) if a date range condition is met. Specifically, I'm looking for a true result if the date in a given field ("Date") is between 10/5/16 and 12/31/16. I figured this syntax should do it: If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 ) However, this returns false for all records within the date range. I'm able to at least get true results for different calculations, such as If (Date > 10/5/16 ; 1 ; 0 ) and even If ( Date > 10/5/16 or Date < 12/31/16 ; 1 ; 0 ) but not If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 ). Any help is appreciated. Thanks.

hello, i would like to know how to take just the last 2 characters of a field example: if the field is "342399" i would like the field "99" or field is 3467564354677 i would like the field "77" i know how to delete characters but i dont know how to leave the last 2 characters. please let me know thank you

Hi i am wondering if anyone ever created a some thing that would be able to create my calender in my filemaker file to include hebrew calender. i would appreciate if anyone has anything to offer me on this.

Whats the quickest way to do this I'm having a mental block! 6 Fields in a record, each field contains a number, how can I use conditional formatting to highlight the top 3 Values?!? Eg: Field1 Field2 Field3 Field4 Field5 Field6 23 25 21 28 20 90 Highlight Fields 2, 4 and 6

Hello I am trying to automatically create subfolders in Windows which should be named year_month, like 2016_11, 2016_12 and so on. This will be done once the month has passed, probably around 10 to 15 of next month. For example, on the 15 of November 2016 a folder named 2016_10 has to be created. I have managed to create a calculated field which content is exactly what I need. Below is the calculated field: "md"&" "&( If ( Middle( Get ( CurrentDate );4;2)="01" ; (Middle ( Get ( CurrentDate );7;4)-1)&"_"&"12" ; Middle ( Get ( CurrentDate );7;4)&"_"&Right(0&(Middle( Get ( CurrentDate );4;2)-1);2))) Then, a script, export…

Hi guys, I'm doing loads of data entry into a table and I need to use the show custom dialog, input data option. Eg: Ive got a table with "Sample", "Stored in", "Patient" I need to enter the blood samples I have collected from various patients. The blood samples are stored in 3 different reagents depending on the patients that I need to input. So say patient 1 has blood stored in solution A Patient 2 has blood stored in solution A and B Patient 3 has blood store in solution C. The current set up I have is that I have a custom dialog appearing asking me what Sample I have collected (which i will enter Blood) and What solution is it s…

Is there a calculation that can do this? I've been testing in FM and googling it but can't figure out. Thanks.

Hey... I am trying to make a calculation, not sure of the correct terminology here. I have a simple database where as I am tracking products. There is only one of each product. So, if a product is used on a specific date for an event, but a new event on the same day has the same product, I need a warning. Record: 1234 Product: XYZ Date: December 1st = Record: 1235 Product: XYZ Date: December 1st This would create a warning "Product not available on that date" I hope that made sense... I am stuck on how to go about doing that. I would think this is an easy calculation, right? TIA Mike

i would like to ask if i will have performace issues in my inventory-invoice database for using unstored calculations ? i choose unstored calculation to see real time inventory. my database is products - invoices hosted in filemaker server, i have about 8 unstored calculations in each record im planning to use 200 products per semester and about 150 customers ... right now im not having troubles about performance but im worried in the future , i had read that having unstored calculations with many records it slows down your database. what i have to do to prevent that ? better hardware for server? or changing structure of database? please let me know you…

Hi, In an existing solution one of my client my client has requested to add a field name ‘Item Movement’ (e.g. Best-seller, Fast, slow) and this should be based on a formula per product item and linked to 'Stock re-order level’ field. For example, if product A reaches ‘Stock re-order level’ in less than one month, it is categorized as ‘Best Seller'’. If Product A reaches ‘Stock re-order level’ in 2 months, it is categorized as ‘Fast’. If Product A reaches ‘Stock re-order level’ after more than 2 months, it is deemed ‘slow’ Since it’s depend on a date, I found it a bit complicated. Any help will be most welcome. I have attached my solution and i have th…

I have a field called Profit % that is calculated using If ( price = 0 ; -100 ; ( 1 - ( cost_price/ price ) ) * 100 ) which of course displays a percentage. What I would like to do is make another field called Discount with a dropdown value list of say 0,10,15,20,25 etc...that will subtract whatever percentage is selected in the dropdown from the Profit % field and display the results in another field called Final Profit.

I have a couple of issues with the attached "mock" database and hoping to get some resolution here. I have aerial survey data. Surveys are conducted each day and each day several legs are flown (one to many relationship). Legs can be "on effort" or "off effort" and the duration of the leg is the time between the next leg timestamp and the current leg timestamp unless its the last leg of the day. A timestamp is created whenever the leg effort status changes (each new record). My objective is to total the amount of time flown "on effort" and "off effort" separately for each survey day (sub-summary when sorted by Survey day). Sometimes survey data are imported from another i…

I have a table that is a log of weekly records, and when I'm presenting the current record, I need to be able to pull data from the week before, so I'm currently using a calculated field with a reference to This Week Start Date - 7. But every now and then, the weeks are not a week apart. So what's the best way to find the Previous Week Start Date when it isn't an exact 7 day match? Thanks!

Hi, I can't seem to get filemaker pro to plot google charts the way I want them in web viewer. I want the graph to look something like this: ...Seems it would be pretty basic at first but I can't get this particular multi-series chart to work. It needs to plot ALL the records and MORE THAN ONE series. I've created one table called 'MyTable' with 3 fields 'CarName', 'CarSpeed' and 'CarWeight'. I've populated the table with 5 records. CarName on the Y-Axis, and against this, CarSpeed and CarWeight are plotted on the X-Axis as 2 series. i.e. series one is 'CarName' vs 'CarSpeed', series 2 is 'CarName' vs 'CarWeight' I've managed to successfully create 2 graphs…

XPOST I need the options that appear in the pop-up menu for a field (MNL LMB A Amounts) to change based on the value in a separate field (MNL LMB ST) which is unique to each record. Specifically: If MNL LMB ST = 2012, then the pop-up menu needs to show: 125, 250, 375, 500, 625, 750 If MNL LMB ST = 2015, then the pop-up menu needs to show: 125, 250, 375, 500, 625, 750, 1000, 1250, 2500 (note the changing increments) As done previously, MNL LMB A Amounts contains the following calculation and makes use of a self-joining relationship in Value Lists to generate the desired options: Let ( [ n = Extend ( MNL LMB A Min ) + Extend ( MNL LMB …

Hi I need to set a file path to a referenced image but I can't get it done. and really could use some help or guidance. I have an FMServer running on a Mac at a remote location. There are Win users that import images into a database, all as referenced files on a NAS disk (Z: drive). On Windows, the referenced file path works of course, but I need the Mac users to also see the files. A filepath to an image looks like: file://Z:/VKB Archief Data/DATA BELGIË/BE 2016-06 Motor/BE 2016-06 Motor inlezen/20160616-DSCF2970.jpg I've tried numerous ways of calculating the mac file path but I can't get anything into the container other than 'file not f…

Hi all I'm using file maker 13 and trying to create a data base for multiple users on the web with a privilege set. and it would also be controlled by two people with full access privilege. one of the requirement is to set a lay out for the members where the would be able to submit requests to the full access privilege people. and a message should pop-up on the Admin (full access) start-up window when a new query is submitted, also a message should pop-up on the member's who submitted the query start-up window when the request is answered. I wrote a script that obviously didn't work, it looks so complicated to me, set Error Capture [on] If [Get (Ac…

I think i have a general misunderstanding. but first things first Filemaker 8.5 on Moc osx I try to find a specific month in a date field. so if i go to the search mode, i can simply type *.10.2016 and it works so if i try to do this with a calculation field and type If ( date = *.10.2016"; " ok" ; "not OK" ) it don`t work. The only think that works is when i type a exact date like If ( date = 12.10.2016"; " ok" ; "not OK" ) but how could i search just for a spesific monthe for instance.

Hi Guys, once again thank you for a great resource. This will be simple for you guru's I am get text put into a result field and want to pull out certain data. Here is the result: Messaging Services provide delivery enhancement tools you can apply to your messaging applications at the toggle of a button. Examples include sending messages over long code (10-digit number) when short code messages fail, and automatically distributing high volumes of messages over multiple numbers to ensure speedy, successful delivery.", "status": "queued", "num_segments": "3", "num_media": "0", "direction": "outbound-api", "api_version": "2010-04-01", "price": null, "price_unit": "U…

I have a TABLE::DATE field that has content with varying date types: e.g. 1) 12/12/2015 and 2) Dec 12, 2015. The field is type "Date". I have another field TABLE::c_YEAR that is of type "calculation" returning a "Number". The calculation is Year (TABLE::DATE). This works for dates as in #1 (e.g. 12/12/2015) but fails for dates such as #2 (e.g. Dec 12, 2015). Any idea why and how to fix (within the TABLE::DATE field)? (I am importing the problem rows from an Excel XLSX file (where the dates are in Oct 12, 2016 format) in an FM Script with Auto-validation turned off on import. Not sure if this is an issue)

LS, Maybe this is well known, but couldn't find post about it. --- So a client of mine had a database crash. I recovered the database ( 7GB ) and imported the data into a clone of a backup. There is a relationship of any to multiple between two tables, eg. link_all ( value : 1 ) and a field called active_link with this calculation Status= "On Hold" or Status="Active" or Status="Booking" ( result : number ) ( this is a boolean result ) For some strange reason the imported data continued to behave like they always did, eg. the result of this calculation was 1. ( field value : Active ) All records created after the import had no value ( fi…

HI Guys firstly thank you for an amazing resource... I am building a rental system and the period could either be "Weekly" or "Monthly" so say it's set to weekly and the item is $100 per week and when the user goes to create the invoice they are prompted to do a Pro Rata invoice or normal. If Pro Rata I need to know how many days left in the current week, e.g 3 days then I would have to divide the $100 p/w fee by 7 and times it by 3. And the same if its monthly I need to get the amount of days till the end of month, divide the monthly fee by number of days in the month and times it by the number of days left in the month. Companies do this to move into a 1st of month/week…

Hi All, I am trying to parse text from a Job Card Items table into the related Job Card and not getting the correct result.. as the Items in the Job Card are completed any anomalies have to be reported on the Job Card and with up to 50 items in the Job Card Items there may be more than one anomaly to report, (very rarely more than two) . If the Findings field is empty I am just inserting the Variables but when the field already contains data I am using this to parse the text: Position ( JobCard::Findings ; ":" ; 1 ; 1 )&¶ & $ProdName &" "& $ItemLocation & " " & $QItemSerial On the second insertion I am getting this: PR00023 FC i…

I have a field, ‘CRM Category’ (I.e. Diamond, Gold, Silver) for B2C End Customer – and another field for Total purchase over last 3 months – Calculation - If customer purchases for Rs 10,000 over last three months, a Diamond Customer, If customer purchases Rs5,000 over last three months, Gold customer. If customer purchases less than Rs5, 000, silver customer My question is for the calculation of the Gold customer as for the Diamond i will use the grater symbol and for Silver the less symbol. Case ( Total_Revenue < "$10,000" ; "Diamond") Thank for your help

Case ( tradename1="ACTON OR"; "AFTER FOOD"; tradename1="ALLEGRA"; "AFTER FOOD" ; tradename1="AMPILOX CV"; "AFTER FOOD" ; tradename1="ASPIRIN"; "AFTER FOOD" ; tradename1="ATARAX"; "AFTER FOOD" ; tradename1="BECONEX GX"; "AFTER FOOD"; tradename1="BESICOR"; "AFTER FOOD" ; tradename1="BETHADOXIN 12M"; "AFTER FOOD"; tradename1="BETADINE GARGLE"; "AS REQUIRED"; tradename1="BUSCOPAN"; "AFTER FOOD" ; tradename1="CEPODEM"; "AFTER FOOD" ; tradename1="CEPODEM XP"; "AFTER FOOD" ; tradename1="CETANIL-T"; "AFTER FOOD" ; tradename1="CETANILI-TRIO"; "AFTER FOOD" ; tradename1="CIFRAN "; "AFTER FOOD" ; tradename1="CIFRAN CT"; "AFTER FOOD" ; tradename1="…

Hi, I am a beginner user of Filemaker, and i have some problem about the date calculation that it give me a result of numeric instead of date format i home someone here can help me thank you

Hi, I'm importing records and have a date field that i need to use to set my Fiscal year, month, week based on the fiscal year starting February 1st and ending January 31st. Any ideas or sample i might be able to take a look. Thanks in advance.

Hi All, I'm currently looking for a way to use the Get(Device) to display its corresponding device name instead of the number as quoted below. Any help would be greatly appreciated. Nathan

Hi there, it's been a long time since i was here. We are doing an invoice solution for our work. Everything seems fine for the most part. We are experiencing some issues with taxes. Each product has two tax rates. 13% and 5%. Sorry almost every product. I have buttons tied to products that when clicked populates the portal with product, amount (as lookup) and the tax rate for that product as a lookup. The tax is calculated as follows; all products have the 13%, but some as I said have the 5%. The second rate kicks in when the gross invoice is = to or under $4.00. Example, coffee: 1.76 net. By itself has 1.76 price and the 5% tax as the total bill is under $4. …

Hi all This has been a well discussed topic however I have struggled to find a direct answer to: Should the 'currenty qty' field in my stock table be a calculation that refers to related line items, or should it be modified via script when related line items are added/deleted to Invoices? I have looked at Todd Geist's excellent Inventory Transactions tutorial and am very excited about implementing it for this and other functions. For one it would mean I could remove a constant server script that hides/unhides stock items (from the stock list that appears when users are creating line items) depending on whether the quantity is zero or not. To be honest I'm a bit …

How can I reset a count an make it start over again? I'm counting records from one account, depending on another field the count will reset to zero and start over from there on, not affecting the old records. In other words one user may have multiple records, I need to count a particular field in order to get to a certain level, but if he level drops in the future, I need to count from there on not affecting the past records.

Hi! I have several fields in one table that use same value list. Is there a way I can count how many times values from this list appear in the fields? I'm aware I could count this for each field individually but I'm intrested to do this for all fields together. TnX!

Hi, I have a customer who wants to enter their Latitude and Longitude into one fields to avoid the human error, so they copy these values when they right / left click on the google map. I've tried my global field in Number and Text but I always get the "positive" value regardless of the negative and the filter I am using ? Here is the value the customer would paste into my global field: 12.918267, -65.841355 Any suggestions would be grateful, see attache calc's. Thank you.

I need to get the Current Time in UTC and reformat it to HH:mm:ssZ I was thinking that I need to use Get ( CurrentTimeUTCMilliseconds ) Any tips? Thanks

Hi all, I have a requirement to re-create Quotations on a monthly basis that are fixed by a 12 month contract. On creation of the QuoteItems I want to automatically insert a fixed ID that stays with the item. Each month a new Quotation has to be produced which is then reviewed to potentially add to the contract in order to acquire a new Purchase Order but I do not want to increment the ItemID on existing items. Any help appreciated Sorry everyone, I figured it out

The Max function is not behaving as I expected. What I have is a "month" field that is number field. It accepts multiple values using checkboxes in the layout. But I have a script in which I want to take only the latest (highest number) from that field, assign to a variable and do stuff with it. But if the field has, say, 1 2 3 4 5 6 7 8 9 in it, Max gives me 123456789 rather than 9. So how can I consistently get the high number out of there? I've played with GetValue a bit, but that solution is a bit fragile, because the user could select the month number checkboxes in any order... meaning the highest is not always in a particular location in the seri…

Hi, I am trying to pull data from a portal, for example I have Standard and Type fields. I would like to pull that portal information into a text field that would show: STANDARDFIELD & ": " & TYPEFIELD STANDARDFIELD & ": " & TYPEFIELD STANDARDFIELD & ": " & TYPEFIELD ASTM: 12345 ASTM: 34556 ASTM: 85858 or ASTM: 12345, ASTM: 34556, ASTM: 85858 Any help would be greatttt! Thanks

Hi guys, I would really appreciate your help with this one. So there are 3 tables, Student --< Enrollment >---- Course On the enrollment table I have a 2 main fields, field for the final grade and a status field that indicates "Passed / Failed" (depending if the final grade >=3.0) , on the Course table I have a field for Credits. Each course has a specific amount of credits, for example Artificial intelligence has 4 credits. If the student passes the course he accumulates those 4 credits and adds them with all the other credits of all the different courses he has passed. Student Course Enrollment …

Is there a way to create an autocompletion field that shows entries from two other fields, so that when I start typing, it searches for the entry in either fields and runs the autocompletion.

I would like to post data in a field in one table, and automatically post the same data in a field in a different table. I don't even know where to start: Each table has the same fields in it.

I'm not sure if it is possible but I am trying to separate text from an imported file. A sample of the text I am given is as follows; Flicker/18/70/O I would like to be able to separate the different parts into separate fields. Field 1 = Flicker Field 2 = would be 18 Field 3 = 70 Filed 4 = O Is there some calculation I could use to do this? Many thanks for all help

Hello. I have two tables: Person and Recruiting Within Person, I have a field Person::Source (which indicates the recruiting event we first met the person) Withing Recruiting, a record is created for each recruiting event. I have the Person::Source field set to run the following script each time I enter a new Person record, which then creates a new Recruiting record Freeze Window Set Variable [$Source; Value: Person::Source] Go to Layout ["RecruitingList" (Recruiting)] New Record/Request Set Field [Recruiting::Recruiting Event; $Source] Go to Layout [original layout] The problem I am having is that we have several P…

Hello forum. What I am (hopefully) trying to accomplish is the following: A user goes into a portal (Sessions) to enter (create) a new related record. On the portal row is a field called 'Exercise' tied to a value list of available exercises. The first line in the value list is "-Add New-". If the user selects this, they are able to create a new exercise (via magic key). All of this works fine. However, if the user selects 'Cancel' in any of the script steps, they are able to later, commit the record with "-Add New-" as an entry in the Exercise field (on the portal). I would like to revert the record. So I tried this script, which doesn't re…

fmp 14 last release. i put this applescript in a global field and also all the fmp field are global. i don't understand wy it doesn't work, if the applescript var are set as ... "set ServerDisk to "afp://2.236.100.143/dati" ... it work. Were i am wrong? Many thanks. set ServerDisk to field ("ServerDisk_as" as text) of table ("__AS" as text) set ServerFolder to field ("ServerFolder_as" as text) of table ("__AS" as text) set VarAccount to field ("VarAccount" as text) of table ("__AS" as text) set VarPw to field ("VarPassWord" as text) of table ("__AS" as text) tell application "Finder" display dialog ServerFolde…

Hi Folks, I have a rounding issue which is probably very easy to solve if you know the correct function, which unfortunately I dont !! We have a quoting database ... The system allows discounts so if something is £10.25 at list and they get 66% discount then the unit price after discount would actually be £3.485 but its rounded up to £3.49 and displays as per below.... e.g. Product Code Unit Price Discount Unit Price after discount Qty Line Price (after discount) Product A 10.15 66% 3.49 1 3.49 …

In my solution, I have a table that lists all possible errors, and each of them have a unique ID like ERR001. I have another file containing a dictionary table with global fields that corresponds to the error messages and are filled according to the language of the user. For example, this table has a field ERR001 that will contains the message to be displayed when error number 1 occurs. I would like to have a field in the Error table that is the error message from the correct Dictionary field, based on the ID of the error record. What I would like to do is something like D::$ID, where D is the Dictionary table and $ID is the ID of the error. I tried us…

I use the following calculation to determine the investment gain or loss between two successive records. The records are sorted by date descending. Case ( Get(RecordNumber )>1;Total investment-GetNthRecord ( Total investment;Get(RecordNumber) -1 ) ) The calculation works well except that on the last record in the found set it results in a question mark. How can I eliminate the question mark? I would like the result to be be empty.

Hi i have a portal of task , ,i want to be able to count the task which are associated with 'something' , ie assigned , due etc , ,how would i calculate that , ,i want get found count and then filter by "field result"

Attention: Before posting in the general discussion forum please review the other forums that would best suit your topic. If you cannot find one then you may delete this text and post your topic in this forum. Hi All, I try to find a solution that sort field in sub summary. For example, my report now is a sub summary by sorted "field A". After I got the result, I want to sort "field B" to Asc or Dsc in the sub summary. Any solution about this problem? Thank you for your help

Hi. What script steps would extract the data from one text field and place them into separate fields. The field text is this: [{"Ship To":"Company | Full Name | Address | City | State | Zip"}] I need to set field data into corresponding fields: Company, Full Name etc I have control of how the data in the text field is formatted. I used vertical bars, but can change to something else. Thanks

Hello Forum, Please assist me, i am struggling to get a working calculation for sick leave field, how to go about calculating how many sick leave days are due for employees if every employee has 30 sick leave days in 3 years, which will lapse if not taken. Can someone please point me in the right direction how to go about all this? Please ask if anymore question as i am also in an ambiguous situation. Many thanks, Miss Amen.

Good afternoon, I am trying to count the amount of sub summaries that meet a certain criteria. I have a table that includes studentID and a progress score for subjects at certain weeks of the year. The data looks something like screenprint 1 (attached). Then I have another layout that shows that data without the body section just the sub summaries, so a summarised view like in the attached screenprint 2. A calculation is used to create the Above, Below or Expected progress indicator based on the progress points. What I really want to do is count the amount of pupils that are showing as 'Above', 'Expected' or 'Below' where they should be. This data n…

Hi, I started out just wanting to trim "Fields" due to customers inadvertently entering "Carriage Returns". Not wanting to build individual trigger script for each endless possible fields, I built a simple trigger script I could attached to any field that requires clean data. However, please see attached script which errors to: "Field Missing" ?? I have absolutely no idea what is wrong. It was suggested to use the "Auto Enter" feature and this too does not "TRIM" the field even with "Validation - Always" selects ?? What is up with that ?? What am I doing wrong in the script, and can someone explain why the "Auto Enter" is not working either. Is it…

I have rewritten this code several different ways but the if statement will not recognize the value in $RefStatus. What am I missing? Even though the Data Viewer shows that the value of $RefStatus = 1, the statement evaluates as false.

I have a simple solution where teachers can enter an overall mark for a task. One teacher now wants to be able to deduct a percentage of that mark for poor grammar, spelling etc. This is not a commercial situation, so three fields would be acceptable. I'm not sure where to start, except for three number fields: mark; penalty; final_mark. Any tips much appreciated.

Hi i am trying to product a calculation that would only show an asteric if field 1 is filled and field 2 is empty. if field any other combination i don`t want it to show. for some reason it is not working . see calculation below field 1 = Ingredeint_Production 3::date field 2 = Ingredeint_Production 3::Approved ( IsEmpty ( Ingredeint_Production 3::Approved ) and not IsEmpty ( Ingredeint_Production 3::date ) ) or IsEmpty ( Ingredeint_Production 3::date ) Any idea whee i went wrong and why the first calculation is not working as expected. i would appreciate some support , thanks in advance.

Hi Guys, Stumbled onto this (old) article: http://www.databuzz.com.au/an-approach-to-filemaker-server-side-script-debugging uses a case with a single condition: Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("DELETE FROM ServerLog") ) ] My question, what would be the benefit of using case as opposed to an if statement? Both would look and act pretty similar. Is there a performance difference?

So I switched to primary key of Get(UUID) but now I can not duplicate record. It says it must be unique. Well sure. I do not want to remove the unique validation. I want to skip the validation just one time and then I can set the id field with a different id in my Duplicate Contact script but I do not know how. I find many search results for 'duplicate record in filemaker' but nothing I can use. Is what I want possible or do I need to completely remove validation unique? What do professionals do in this case?

Help, I setup a summary field. Then made some changes. Now the field wants to summarize all 310,000 records. how do I stop this from running?

Greetings good people, I would like to take up your time if you could lend a hand in helping me on how to hide records that are no longer needed. To be more specific, I created a database with employees record in it. Any new employer will be put in the database and update his/her profile. My question is if an employee is no longer working with us, we want to keep his record but somehow exclude him/her/them from the current working employees. Options that came through my mind is that I could mark them as EX-EMPLOYEE on their photo in red or something like that but that would still include them with the current staff. So I want to hide them permanently like t…

Hello FL gurus, I am trying to create a calculation using the "Timestamp" function but can't get the result I need. I am hopping someone can guide me. I have three two fields and two buttons Start Time (button once press it enters the time in the field using "Set Field" and "Get (Get ( CurrentTimeStamp )" End Time (button once press it enters the time in the field using "Set Field" and "Get (Get ( CurrentTimeStamp )" I also have a calculation field (Start - End) with a result of "timestamp" is if possible to ger a result to show elapsed days and time e.g start 1/1/2016 1:00PM - end 1/1/2016 2:00PM I would like the result to be 0 day and…

Hi I have a database that is meant to work out time calculations. I have set the fields that require time to be entered as time fields and changed the way they are displayed as hhmm, but they display as 0.00 in the field, till you click on the field then it shows the time entered , then when you click on a different field goes back to 0.00 I also put the fields that have the calculations set to time results rather then number so they would display using time rather then decimal, it displays them as an odd calculation, but whiny set it to display to hhmm as well they also just show up as 0.00 I am not sure what I need to do to fix it. as I thought se…

Attention: Before posting in the general discussion forum please review the other forums that would best suit your topic. If you cannot find one then you may delete this text and post your topic in this forum.

I have a calculated field that is based on the following. Now I want to add this: If ( LABEL = "Eloquence" ; Case ( ANTAL I ALBUM = 1 ; "79,50" ; ANTAL I ALBUM = 2 ; "149,50" ; ANTAL I ALBUM = 3 ; "219,50" ; ANTAL I ALBUM = 4 ; "219,50" ; "Mangler kalk"); Where and how do I put it in? I have tried almost everything, but I keep getting error messages. --- Current definition --- If ( GENRE = "Klassisk" or GENRE = "Jul" or GENRE = "Jazz" or GENRE = "Pop / Rock" or GENRE = "Soundtrack" or GENRE = "Traditional" or GENRE = "…

Hi everybody. I'm a newbie with filemaker, but I'm using it with satisfaction.. Sadly, I've made a mistake with my first project and I'm trying to correct it without changing my database, because now it's been populated. I've create two tables (A and B). In table A I've create a list of value, each of them with a primary key. In table B I've create other records, and in every record there is a field (named "fKTableA") in which I've selected all the values from the table A that are related with the record of table B. The problem is that now I'm trying to create a report, but (naturally) in "fkTableA" there a list of numbers instead of the values. Is pos…