Calculation Engine (Define Fields)

Hey Guys, Using a calendar generation script that requires all of my eventstart field and eventend field to be timestamps rather than dates. Since all of my events are dates I've had my database set up with just date fields. Can I use a calculation to generate new fields which take the date field and spit out a timestamp of date + a time of 00:00 ? Any ideas? Thanks James

Hi, I want to create a manual filemaker synchronization button for both relookup and update information in the server, desktop, and ios devices. If I use date and timestamp, would time difference in various part of the world be a factor of making mistakes in the synchronization? Is it possible to fix all the timestamp for all the devices the same even they are in different parts of the world? Thanks. Kent

hello I want to calculate the age of person via the date of birth but it calculates 8000 years... :/

hello Could you define me to how to convert uppercase just the first letter of every word. (there will be 1-2 words only) thanks

hello, whenever I choose a field as Number, the number 1090921212 turns into 10+10e12. how can I disable this option? Thanks

Trying to calculate next month in a field with the following calculation Month (Get (CurrentDate) + 1 Not working

I have a script which uses a calculation to make a voucher number. It works fine for a calendar year, but our fiscal year begins on April 1 and I can't figure out how to modify it. Here's my calculation: Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & "KS" & Right ( "000" & ( $Last_Voucher + 1 ) ; 3 ) I would appreciate your help. Thanks, Kevin

I have a check box thats currently having some on/off issues. The field of the checkbox (QuotedStatus) can be checked with the value "Quoted" I have another field that basically says if QuotedStatus = "Quoted" Then... However its not working... its not recognizing the "Quoted" when the box is checked. Anything I'm missing?

Hi, I am getting an issue regarding Calculating the Total of Each individual Item per month. I have created 2 tables in FileMaker. A Item table which will have ID and Date. Another table LineItems. which will be related to Item with via Items ID as FK in LineItems. http://imgur.com/vdbmhEA I wanted to create a List of items per month based. and there Total QTy per month. Something like that. http://imgur.com/qUWXF8v Of course its showing all the Items sum. I want it to have sum on each individual item i have that month. For Example: Marks are are repeated 2 times in second image. it shouldn't have only it showed once and its total sum. The Files i am …

I have a field thats conditionally formatted BWIA2 FMP11 2::CUST number > "9000" Then - turn background Red The problem is, sometimes it works, other times when say the number is 11935 it doesn't work and actually sometimes acts in just the opposite way, ill have a less than 9000 number in the number field and the background will turn red... Is this a known issue or is there a better way to do this? Thanks

Hi, There is probably an obvious way to do this, but since I couldn't figure it out myself, here it is: How can you prevent users to change a value in a field, say Time1 [time], based on a value of another field, say Checked [number, 0/1]? (Checked[0]=modifiable, Checked[1]=not modifiable) Jari V

I have the following function that is operating correctly (returning true when more than 1 "Gas" or "Utilities" entries exists. If( (ValueCount( FilterValues( List( Assistance::Assistance Type); "Gas")) + ValueCount( FilterValues( List( Assistance::Assistance Type); "Utilities"))) > 1; 1) I want to add another limitation, but I am not sure how to and am hoping for help. I only want the above function to return true if the "Gas" and "Utilities" criteria that it is finding appear more than once within the past 365 days - currently it returns true if the criteria appear more than once ever. The date of each Assistance Type is stored in Assistance::Assistance Date. T…

I have a little dilemma that I would like to get to the bottom of. My system formats are Swiss French. So quite normally defined date fields are almost always given in French, which is what I want. However I have come across one date field that stubbornly is rendered in English. I have verified that the file in question uses System Formats as does the file it originates from in Relationships. Can someone tell why this field wont render in French and how can I troubleshoot this?

Hello All. I'm using FilemakerPro12 on a single MacMini. I'm using FMPro12 as a single, desktop database, to massage-and-output text; I am the only person who will access the database, no other users, Web users or anything like that. I have a text field called 'source' in which there is somewhere (one or more times) some text like this: <strong>Submitted:</strong><span>17thOctober2013</span> I need to get the text "17thOctober2013" into a field called 'submitteddate' given that <strong>Submitted:</strong><span> will always mark the start of the text I want, but that </span> can subsequently appear many times wit…

Relatively new to FM, but here's quick question: Is there a way to calculate the maximum length of all data for a particular field? For example, I have many layouts that contain concatenated fields for text (usually an address), and it feels like the width of the field is much too large for the data within in. I could always export all my data to Excel, use the LEN function and go about it that way, it feels like there should be a simpler solution. Any ideas would be very appreciated.

Hi. My issue is a calculation field is not being calculated. I know because they are empty. I can find a way around that issue. I tried some methods i saw in FileMaker Help. Like using of if AND case statement. But it give out Error the an Operator is missing. Table There are 2 tables involved in it. First Employees which stores Employees Records along with there Original Salary. Another Table is Adjustments. Which stores the ups and Downs of salary. Adjustments table is related to Employees Tables via relationship where Employees id field is used as FK in Adjustments. Fields In Employees table there is field for salary. which stores the original salary. its nu…

Hello everyone. I'm trying to build a tag browser interface in FileMaker. I'm open to a broader discussion on the subject of tags and would welcome any references to previous tag-related threads. My database is a project management tool with records like Contact, Project, Service, and Note. I want to give each user the ability to apply his or her own tags to each of these records. I also want the user to be able to browse by tag and generate a list of all tagged records regardless of record type (i.e.: table). When a user applies tags to a record for the first time, a TagList record is created and matched to the tag-able record. This TagList is a join table with…

I have a table whose records are shown as portal. One of the fields is populated with a "Yes" or "No" and that depends on whether it is complete or not. I'd like to have a separate field that gives me the percentage of completed tasks (Number of records with a Yes / Total Number of Records * 100). Thanks for the help.

Jobs Table, has a portal to JobVendors. Job #1 has 3 portal rows, and the vendors have categories (utility, furniture, cable, etc) I have a checkbox set of Needed Vendors: Furniture, Cable, Utility. I want a calculation that does: If ( PatternCount ( JOBS::needed ; "Cable" ) and not PatternCount ( VENDORS::type; "Cable" ) ; "Need to order cable and/or internet" ; "" ) It only does the first row. Any ideas on how to do more than one row? I've researched but can't seem to find anything... Thanks!

We have a script which runs a report showing all time card entries posted for this week (for payroll purposes). See the attached screen shot showing the script as it currently stands. This script was created a year ago, and I have just discovered that the insert calculated result line is not only finding the current years entries, but all entries with the current week number. Hence why last years records are also being included now. Can someone please help me to tweak this calculation to find only this years records for this current week. I am not sure whether or not I should be inserting the following somewhere into the current script: Year (Get (Current…

I guess this is the best category... I am redesigning my solution as I built it as a complete amateur and piled on my mistakes. I have a table, Jobs. It tracks move ins, move outs, vendors etc. I have a "Warning table", filled with calculations that are related to my jobs table. If a calculation is Yes, then on that particular Job record, I have a little flag container field show. I have simple find buttons that find all records with a count of 1 or more, and a portal showing the warnings. The table has calculations and summary fields so I can find flagged (warning) records. If I decide to add a warning calculation, it's a bit of a headache. I have to add the …

The basic idea is that there are Clients who have Consultations for Psychological issues. Important fields to note for Clients: Agency (there are 4 different agencies that a client can be assigned to - due to NDA let's call these Agency 1, Agency 2, etc. Employment type (Employee/Volunteer) Referral Cause (Work/Personal/Incident) - this is to determine the initial reason this person became a client. Underlying Issues - there are about 30 different reasons for this - one or more may be selected. Some examples - Anxiety, Depression, PTSD, etc. Â I can create the summary of consultations and costs, etc. but where I struggle is how to get a summary and graphs of everyt…

Hello,  I being trying to run the script but cant find any help with this.  basically i am trying to perform an condition where if the available qty is less then the delivered qty, it should not proceed. but the error i am receiving is different.  the error is also seen even it i have sufficient qty in the stock.  Please find my attached script and let me know if am making any error.  Regards

I am sorry if this is the incorrect forum to post my query to but I am very new to this and am about to throw my machine out of the window...but before I do I thought I would ask the question: I am writing an If Statement and need help! I want to say that If a field has a value greater than 1000 but less than 5000 then give a certain result. EG: If ( Donations::sum pound ≥ "1000" and ≤ "5000" ; "Grade 2 contributor" ; "Grade 1 contributor" ) with the calculation result being set to 'text'. I am trying to do a database for a charity I work for, and they want to grade their contributors depending on how much they have given, but this is really, really and I…

Hi All, I have an issue with a calculation and I cannot seem to figure it out. I run a theatre school and have pulled field to create a register where parents/members can sign in. On the box where the signature goes I wanted to display the words "Happy Birthday" when i its the members Birthday within the next 10 days. However the code I have displays the words with regardless and I cannot see where I am going wrong. The calculation also displays a number directly after the word i.e HAPPY BIRTHDAY1, HAPPY BIRTHDAY2. I am using FM starting point as my base and working in FM13. Any help would be greatly received as I am a complete beginner.

Our database has a quotation table. We have a drop down menu for products and one for quantity, total and price. The price field is a calculation field which the calculation is If (Product = "A2 Poster" ; "£10) which works fine. However obviously we have more than one product we sell, when i try to put another product calculation next to this it does not work. How can i have multiple calculations in the price field for each drop down in "Product field"

I have a large database, with a separate relationship/table called phone log, we store all phone logs in this table and it is linked to the main database via the Customer Number Field. This phone log stores auto-enter dates, typed phone log messages, has a container field for documents, and a Document title field. etc. within my main database, I'm trying to create a field that says (if the document title contains the word "application" then display the auto enter date that corresponds to this record. Any thoughts?

I'm trying to create a field that displays a single number 0,1,2,3,4 depending on the following. If field A is not empty, display "1" if field B is not empty, display "2" If Field C is not empty, display "3" If Field D is not empty, display "4" If field A is empty, display "0" Thoughts?

I need help writting a calculation for a scheduling tool. I need to write a calculation that determines the start date when you know the end date (delivery date) and the amount of days for each milestone between the start and end dates and of course calculating only using weekdays; for instance: Start date: unknown Picking: 2 days Packing: 3 days Shipping: 4 days Delivery date: 4/1/2014 Any help would be greatly appreciated.

I'm trying to clean up some field data where some users of a database enclosed titles in a TITLE field with "Quotation Marks" while other ones did not. I want to remove the quotation marks from those who did include them. I figured I would create a new field called TITLE_New and use some sort of calculation strategy to find the relevant title records and massage the data into it while leaving the original Field data alone. Once I'm satisfied the data in the New field looks good, I'll delete the old field and use the new one in its place. As a warm-up I thought I'd search for the records where the first character was a quotation mark ("). I thought I recalled th…

Hello I am trying to find out how to change scripts to execute conditional on if a certain text exists in the current field. so If the current field contains the word "list" then perform script A but If the current field does not contain the word "list" then perform script B NOt sure how to do this I just need a script like this that will check for the existence of a word in the current field and if the above conditions are met to perform the appropriate script. Thank you

Trying to use the Get (CurrentTime) in a calculation field to get the current time, if its less than 11:59am then display "Good Morning!" in the field, else "Good Afternoon" Any suggestions?

Hi All, I have an events search icon with a script which enters find mode and set selection on my event description field... the event description field is set to auto complete with existing records to help find the right event record quicker... but it does this by matching the words at the start only... Is there any way to set it to match any part of the event description? Surely this is an easy one! Any help much appreciated.

My database is organized by a 4 digit customer number field (ex 4571) and a 2 digit year field (ex 12) the customer gets a new file each year, so 12 would indicate 12 separate files for the customer. the 4571 customer number field is associated with the general data for the customer, and the 2 digit year field is associated with data that changes each year. We have a field for Total Premium thats associated to the 2 digit year field (so each year it changes) On the current file say the (12) file, I would like to show the Total Premium field associated with the (11) file. So I can show what the customer paid last year on my current file. Any way to do this? …

Hi- I have a text field where I have stored dates in the format ddmmyy. I want to change this to a date field (I know, I should have done that from the start) without losing 10 years worth of entries in this field. I'm guessing if I have a formula to re-format all the data from ddmmyy to dd/mm/yy this would be enough to preserve the dates correctly when I switch the field type from text to date. Can anyone confirm if this is the case and give me a few pointers on how to write a script for this? Thanks, Jon

Hi, I am trying to get the sum() of field base on two condition. Attached is my calculation : If ( Invoice Entries::INVPaid=0 and Invoice Entries::Age of record<-30;Sum ( Invoice Entries::Inv Amount );0 ) Please let me know if i am making any mistake.

Hi, This is probably a very simple question for most of you... I am trying to define a field so that it automatically inputs a serial number in the following format: mmddyyyy## So, if the date is 01/09/2006 and it's the first entry of that day, the serial number would be 010920061. If it's 01/09/2006 and it's the second entry of that day, the SN would be 010920062. Any help would be appreciated! Thanks and Happy Holidays!

Hi All, I am trying to perform a date calc based on a certian situation. I have the following fields start date, start time, finish date, finish time, hours so.. I have the hours field with 18.5 hours filled in. I select my start date and start time. But I want to be able to calculate the finish date and finish time automatically because I know the hours to be added. The problem also to be added.. The day/shift only has a certain amount of hours to be allocated that is 08:00hrs between 6:00 and 14:00 so.. if the start date is say 24/02/14 the start time is 14:00 and the hours is 18.5 I need the finish date to be 26/02/14 and the finish…

Hello, I'm working on a solution whereby Tools (records added on a Tools form) should be able to be checked out, or assigned, to a Project record, created on another form. A Project can use many Tools, but a Tool may only be assigned to one Project at a time. I would like the Project layout to have a portal showing any and all assigned tools, and the Tools layout to have a field indicating to which project it is assigned. I'm pretty much a novice and I'm having trouble hashing out the relationships, fields, and layout here. Any ideas or help?

Hello All Not sure if this is the correct forum for this, because it is combination of calcs and scripts, but I'll start here. I have a sensitive database, that I am currently tracking who modifies which record and when. I have a layout script trigger that move the simple auto enter modified time stamp and account to one of six fields. It builds, if field 1 is full, it puts the data in field 2, etc. I need one more piece of data: Which field was modified. There are ~200 fields, and any of them can be changed. Has anyone ever been successful in logging which filed was modified on the fly? Gil

Hello, I have about six variables that contain a list of record keys. I need to filter these 6 lists so that only those keys that are in all of them remain. I am guessing I would use the FilterValues function but is there an easy way to do this? Regards! Stephen

Hi, If I use "Get (CurrentDate )" in a Field Calculation for each record … will it get the current date when the user starts the file up ?? Each Day ?? Or will it only get the current date once ?? When record is created ?? If ( not IsEmpty ( Invoice Paid Thru Date ) and Agreement Billed 1 ≠ 1 and Invoice Paid Thru Date - Invoice Alert Value ≤ Get ( CurrentDate ) ; 1 ; "" ) Here is why, I am using this "1" to build a relationship … When I use a Global Fields in the calculation to hold the CurrentDate ... Filemaker does not allow me to select "INDEX". Without the "INDEX" check box checked, the relationship does not build. And the only way I…

I have two attached pictures which my help on what i am talking about. I have the Employee ID in the employee picture, which will be used to find the name, middle name and last name of the employee.  The goal here is to have the employee ID be the get(accountname) login, so that way the data base will automatically know the name of the person. However, i want to be able to have the name of that person across all the records on the other layout. I know about global fields but don't know exactly how they work. There will be more than one employee, so I dont' know if global fields work for more than one option.  Also, I made the date global on the attachment where …

Hey guys, I am writing time sheet deb for my employees. Each day they can login and allocate their hours to a job. This is tracked in the jobs and I would like to display daily or weekly time sheet layout. I have a table for labour which stores their entries with date, employee, hours and job number. Is there a way I can create a new layout that would display a list of their labour by day. So I would like to have a heading of day name, then the list of entries below that. Thx

I'm trying to develop a Sports Competition Management System, which ties in with a few other features for my sports association. We have a number of leagues, based on Gender, Age, etc. which vary in team numbers, for example some leagues may have 12 teams, other only 7. Some teams play each other only once per season, other two + times, it depends on the size of the league. I have a few scripts which combined build the schedule of games (Time, Date, Round of Play) and my next step is how to create a Balanced Schedule for an Even Number of Teams, or a Cyclic Schedule for Odd Number of Teams. Obviously, an IF function determines which Scheduling Algorithm to us…

I have a calculation that works up a bunch of text and fields and then sends that via my macs mail program when i click a button. Works great, but I'm having trouble formatting certain words in bold. I've tried using TextStyleAdd( StyledField; Bold ) and that may work while in filemaker but the bold formatting doesn't transfer to my mail program. Thoughts?

Hi All, I have a database with a setup table and a globals table. On the setup layout I have placed a globals description field and a globals price field. I have a laptop hosting the file and I have 3 other laptops that I remote into the database. If I change the a price on the master db the other remote databases do not update. Any ideas would be helpful thanks MG

hello to everybody ; I need some calculation fields. Ex. Product A : include a,b,c products Product B : include z,x,y products. When i sell A product 10 unit and a,b,c products decrease 10 unit. Can anybody help me how to do this on filemaker ? if previously making example available i examine that and make myself. Thanks. ( i dont know that problem suitable on this forum. excuse for this.)

hello all. I am trying to find out the right way to find totals of retail dollars by specific departments or sales staff. so in my mind i think... Get retail if dept="A". or Get retail if sales="debbie". and what i would want would be a total of all retail dollars for all entries that that department or sales person equals. make sense?

Hi, I've searched through the forums but can't find what I need, which is a calc to filter a portal to only show records for the current fiscal year, but as I'm in OZ that's 1 July to 30 June I started a long winded calc using if(month(date) =>7 and month(date) <= 12; etc etc etc but I thought there should be a more 'elegant' way, possibly using the weekoffiscalyear function .. BUT .. it thinks everyone is American and for whatever reason isn't interested in the system settings any suggestions ? cheers Cate

Hi all, apologies if this is the wrong place to post... Here's my problem. I have four tables in my relationship... a 'Contacts' table, an 'Events' table and an 'Email Correspondence' table and an 'email templates' table. In my email templates table, i have merge variables which are replaced in a 'merged view' calc field using a pattern count find & replace, which works great. So for example if <<event_venue>> appears in the template creation field it is replaced by the actual 'event venue address' in the related events table... all good. What i want to be able to do is automatically replace another <<primary_contact_1>> with th…

I'm at a loss here. The big picture is that I'm trying to filter a portal by way of a calc field that captures id's based on several filters. I'm just pushing the calc field into the global field so I don't have to refresh. But for some reason, I keep getting a "?" in the g field. The correct values are reflected in the data window but regardless of whether I try and capture the array by way of a set field script step or capture the array by variable, I still get the "?". I thought the problem might arise from something related to inserting and deleting records by way of sql (specifically, something to do with updating the g_field with a newly added or removed id) but eve…

Hey I am upgrading a 12 solution to FM13 and trying out some new features. In this system I have a layout with a list of names in a portal. There also is a second one row portal to display detail info. The user clicks on a portal row to select it, then in the one record portal it displays more fields about the selected name. Our current solution works fine. When the user clicks on a name, it triggers a script to set a $$Var. then there is a refresh window script step to refresh the single row portal. Sometimes this can take a couple seconds. I replaced the refresh window script step with a refresh object. However the Refresh Object does not appear to f…

Hi I am using the formula GetLayoutObjectAttribute ("page" ; "content" ) to get the page source from a web page http://www.wordproject.org/bibles/kj/40/18.htm The calculation gets part of the source and puts it in a field But when I manually view page source in safari I get the entire page of code . I am trying to extract different bible verses in this case with the above url. For example, I would like to get the verse 19 and bring it into a field. The verse shows up in the page source but not in the GetLayoutObjectAttribute ("page" ; "content" ) calc. How can I get the entire code by calculation of the page I have above in the above url?…

I understand all of the issues associated with using repeating fields. I have an application where I have three fields - A, B, and C. A and B are defined as a number. C is defined as a calculation with a repetition of 2. I want to populate the first repetition of C with the value of A and the second repetition of C with the value of B. Calculation result would be a number. Thanks!

Super basic question, but I was wondering how to capture single quotation (") marks in variables or calc fields. This is just for display purposes as I'm using FMP as way of simplify a bunch of repetitive cutting and pasting between fields on an online database at work. FMP won't allow me to use the symbol for inches (") and if I cheat and use two single quotes (''), it doesn't display correctly in the work database where I'm pasting the data into. Any help would be greatly appreciated.

I've been using FM 11 Advanced for some time now without an issue. But with recent upgrades in Bitdefender and Flash, FM is now hanging, and I get the awful Windows message "so and so program is not responding." I took off Bitdefender 2014 and went back to 2012, and even removed that, but I still get this message on my main system. I have a backup computer with FM 11 Advanced, as well, and my FM solution runs perfectly on that system, with no "not responding message." I have run the Windows Memory Test--no problems. I have run chkdsk /f on all drives, no problem. I have run the "repair" function of the FM 11 Advanced CD, and even that didn't make a difference. I ha…

I want to create a field that auto-populates the name of the current month (MonthName). I would also like to auto-populate a field with the current year. I copied fields from a starter solution I found them in… but can't seem to get the field to auto-populate in my layout. I found a thread that addresses this (http://forums.filemaker.com/posts/ed41960dd2) exactly, but it suggests doing what I've already done. For the month field, I created a field named "Current Month Name." It's a global text field that is supposed to auto-populate from this calculation: MonthName ( Get ( CurrentDate ) ). I placed this field in my layout, switched to browse mode… and it's just a…

I am trying to get some XML data (currency exchange ) inserted into a field, but not luck. URL http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22USDCAD%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

I had taken the time to rename our accounts without a space. I thought it would make them more 'key' like. Of course it was wrong. There are 3500 records I did this to and now I am hoping that there is a way to quickly undo what I did. I did it so I could get them with LeftWords. I searched and looked on the custom functions and nothing now splits it. So take AccrossTheRiverLodge and make it Across The River Lodge. I tried searching for second capital to insert a space before it but I can't even get started on how to look for a capital of any letter. If no hope, I will spend my day paying my penalty for stupidity.

I was given a color 5204378 I do not know what it is but I was hoping that FM would. So I made a calculation container and put GetAsText ( 5204378 ) and many things and even GetAsCSS() but nothing turns it to the purple it is supposed to be. I see an RGB() for colour but it wants the numbers split apart and I do not know how. What am I missing please?

This has to be so simple but I have tried everything I can think of for two days trying to get it to work. I have 3 fields (Flight_Date, Flight_DepartureTime, Flight_ArrivalTime) I need a function that will take the departure time and the arrival time and determine if the arrival is on a later date. For example; let's say that on June 4, you have an evening flight the leaves Ft. Lauderdale at 6:35 pm and arrives at Albany at 12:01 am the next day. On my printout, it want it to display as "Departs: 6:35 pm Arrives: 12:01 am (Jun 6)" Every way I have tried to accomplish this using nested If statements and Case statements turns out wrong. If …

Help me please is there a way to drag multiple file in a container field heres my sample https://www.dropbox.com/s/0bea42cnpc66dkb/Drag%20and%20Drop%20File.fmp12

Dear fmforum, i would like to create a field in my INVOICES table which sums the subtotal values of the whole month. i would like to see the income for each month. maybe there is a option to add a drop down menu so i can choose the specific month. i have a field called month which looks like this: 01/13, 02/13, 03/13 etc. for each month. i add the corresponding month to each invoice. e.g. for january 2013 i add 01/13 to the field called month. where do i start?

Hi wonder if someone can help. I have a field which is y or no for attendance. How can I count all the Y and also seperately the N to give a total of each. Thanks very much for any help.

I'm not completely certain if this is the right section for this problem / question. I have a situation where I need to make sure that a field in one of my tables has a unique number under certain circumstances. I have student information system which has a lottery module I built so that they can manage student applications for the coming year. Each student once entered into the database is assigned a lottery number and associated with a specific lottery which is associated with the school year and the grade they are applying for. When each student is entered they are given a lottery number, starting at 1. The problem is I need to make sure that that number is not…

I have created a calculation field named Number2 I want to use this field to remove all of the puncuation from another field Number1 Both fields are text fields becasue they not only have numbers in the but also have letters somtimes. and example of what is in the field Number1 is 11-44-25-08-000B1.00CE and I would like the calculation result in Number2 to look like 11442508000B100CE Any help would be greatly appreciated. I tried all kinds of posts and could not find anything that would remove puncuation from a field containging number and letters.

I'm trying to create a calculation that prorates rent based on next month. So if their rental rate is $1,000 per month, and they haven't submitted a move out date, they owe $1,000. If they submitted it for 2/15/14, I need to be able to calculate the prorate. 15 days of rent (2/1 - 2/15), divided by 28 days in the month of February, so they owe $535.71. Any help would be appreciated... Thanks!

(My first post to this forum - if I've misplaced this question please let me know and I'll happily move to a more appropriate folder.)  I'm trying to build a database that I can use to log video footage. I've a system that seems to be working for me so far, but there's a couple of tools that would be extremely useful to me and I can't find any way to make them happen.  Attached is an image of what it looks like at the moment.   I've got one entry for each film clip. I can name it, give it tags for people who appear in the clear or themes, make some notes about the type of shot and location, etc.  One of the key features is the portal on the right. I go …

Hi folks, I have a field which is defined as a 'text' field as it will contain both numbers(currency) and text. The field is defined by the following calculation: Case(IsEmpty(_pk_InvoiceID); "(Not Invoiced)"; Paid - Subtotal) Because this field is defined as a text field (to allow insertion of "(Not Invoiced)" string), I can't use the currency formatter in the Inspector. Any suggestions on how I can force a currency format on the numerical values in this field? TIA

Hi there, I have a bug with a timesheet calculation that determines daily hours worked. Hours less than 6 am and after 8 pm are excluded as they are treated as time in lieu. For the following calculation the starting time where 5:30 am, the finishing time is 1:00 pm and the lunch break is .5 hours, the hours worked displays as 6 hours. It should be 6.5. The first 30 minutes (5:30 am to 6:00 am) are excluded as they are before 6 am which leaves a total of 7 hours minus .5 hours for lunch which is 6.5 hours. What have i done wrong? All replies gratefully received. If(IsEmpty(StartTime24) = 0 and IsEmpty(EndTime24) = 0; Round( Hour( If(EndTime24 > Time ( 20 ; 0 ; …

Good morning all, I have to do an import of data into FMP, but the field we are 'matching' to is configured differently than FMP. EmployeeID from the CSV file we are importing is a 6 character field, where the employee ID itself is actually 4 or 5 characters and any remaining empty values are prefixed with a 0. Such as a 4 digit employee ID is shown as 001234 and a 5 digit employee ID is shown as 012345. In our FMP database, the EmployeID is shown without any prefixed 0's. So it is 1234 or 12345. When importing, these don't match up. I'd like to make a calculation in FMP called EmployeeIDConverted where if the EmployeeID in FMP is a 4 digits, it disp…

I'm having a problem with date fields sometimes being passed in a parameter in a strange non-date format. It may appear in the field as 1/11/14, but is passed in the parameter as 11.01.2014. Data in this format can't be put into a date field in a script, and GetAsDate returns a question mark. I can't figure out what's going on. This glitch is somewhat inconsistent. Jim

Hi, thanks in advance for your help. I have learned a great deal on this site.  I have a table of Events. Linked to that table is a table of Expenses. The Expenses portal is inside a tabbed area and works well showing the expenses associated with each event. One of the tabbed portals EXPENSES is filtered to only show expense values above zero so it shows only expenses. The other tab, BALANCE, shows all the expenses and and payments related to the event. If everything has been paid, the total charges are zero  I have a one line portal with a summary field REPORT ANOUNT TOTAL (It's labeled as Expenses that totals the expenses in the filtered or unfiltered portal. I…

I have a table that first sorts all records by month, Jan, Feb, Mar etc. Within those months i have multiple records of different years, mainly 2014 and 2013. I'd like to separate the years within the month so for example January 2014 - Year Separator 1/5/2014 1/10/2014 1/15/2014 2013 - Year Separator 1/5/2013 1/10/2013 1/15/2013. February etc etc Any advice?

Not sure if this is the correct location or not. We have a field called "Record_Number_Alt", this is nothing more than a serial number plus 1 that we reference on a couple of searches. Last week we had a record created and the number was set in place (lets call the number 99500), other records were created throug the week and all increased by 1 up to 99555. A new record was created this week that had the old serial number of 99500 (the number from a record last week), even though the next field number was showing it was set to be to be 99556, and all records to date have had the number set correctly +1. Has anyone ever seen this issue or have an idea as to…

Hello everyone. I'm an American professor at a Japanese university. I have expertise with Excel but am relatively new to FMP. I am trying to make reports of a student satisfaction survey to return to teachers. There are 25 courses, 14 questions, anywhere from 5 to 20 students per course. All questions were multiple choice, Strongly Agree, Agree, Disagree, or Strongly Disagree. I would like the teacher report to simply report the raw count of course takers who answered Strongly Agree, Agree, etc. for each question. Something like this: Course: American business Teacher: Mike 1. The course goals were clearly presented: (SA) 7 (A) 5 (D) 1 (SD) 0 2. The…

Hi all, Does Get(PersistentID) behave as expected (i.e. is it truly persistent?) when using it to uniquely identify virtual machines? Thanks in advance! -Kent

I have three tables (1) Contacts (2)Tickets (3) Guest.. The calculation I have for my EventStatus field is Case(Tickets::Ticket Qty ≤ 130 ; "SOLD OUT" ; "AVAILABLE TICKETS" ) I'm trying to make sure when the tickets are sold out that that merge field turns red and says SOLD OUT. and if it's not sold out than it will show Available tickets. This should be an conditional format calculation within the merge field.The problem I'm having is that the Tickets::Ticket Qty = 130 its not showing sold out, when I place other numbers that are more than 130 or some that are less it pops up. Maybe it has something to do with the relationship? What do you'll think? This calculation sh…

Hi there I have a filtered portal that uses pattern count to filter.... It works fine but I would quite like the pattern count to behave a bit differently.... Instead of it looking at the whole word I would like it to act on just the start... eg... If a user types "Neff" then Neff should appear but NOT Benne or Hene!

Hi all, Just a quick question on using variable field names; If I have a table with a field called Month (populated as Jan, Feb, etc..) and a different table with an Item (populated as Widget1, Widget2, etc; also with a field for each month; Jan, Feb etc..) Is it possible to use a calculated field in the Month table using the Month field as a variable field name to reference the column in the Item table? I bet that was clear as mud... Month Table Month - Count (calculated) jan - feb - Item Table Widget - Jan - Feb Widget1 - 2 - 3 Widget2 - 0 - 2 Resulting in Month - Count jan - 2 feb - 5 Thanks for the opportunity to…

This is how I generate my report. I have 2 different table, one (Project) is for me key in the project, another one (Money) for user key in their expenses which link to the project I created. Below is the result i generated from the "Money" table..... each project have different group of people working on it. My question, how can i get the value from 2 sub summary area? Which I can use on my Project table. Project Toilet bowl $13000 <--------- I need this total value for each project Team A $4000 <----- I need this total value from team A who work on this Project …

FM accepts the following calculation but it's not doing what I require it to. I'm trying to conditionally format a date field if the current date minus 3 years is >= TOP 006 Records 3::Most Recent Date. What's glaringly wrong, the first part of my calculation between Day, Month etc won't accept ; hence &, but the second part does? Day (Get (CurrentDate)) & Month (Get (CurrentDate)) & Date(Year( Get (CurrentDate) - 3) >= Day ( TOP 006 Records 3::Most Recent Date ); Month (TOP 006 Records 3::Most Recent Date );Year (TOP 006 Records 3::Most Recent Date ))

OK, so here's another thing that I thought I knew, but apparently I had remembered my results wrong. Say you want to find something in a list of items like this: $value = 'apple'; $multiValue = 'orange¶apple¶banana' It was my recollection that you could use this statement: If ( $value = $multiValue ; 1 ; 0 ) to determine if the value 'apple' existed somewhere in the field or multi-value string/list. That doesn't appear to work, even if the value being checked for is the first value in the list. I would have sworn (I must swear a lot) that I had done this before. But apparently not, because it doesn't work. Instead I have resorted to using position(): If…

I would have sworn that I had done this before, but my testing now appears to fly in the face of my memory. Here's some test code to demonstrate: Let ( [ _string = "Blah" ; _num = 1; _num2 = "1" ] ; if ( _string ; "true" ; "false" ) ) To run the test, you just change the logic clause of the IF statement to be one of the variables at the top of the LET statement. The two 'number' values/strings evaluated to TRUE. But the string evaluated to FALSE. I would have sworn that I have used the definition of a string as a logic control before to see if it is set...but I guess not. If you change the string value to "1blah" then it will evaluate to TRUE.…

How would I write a calculation to replace all non-alphanumeric characters with an underscore? For example ABC 123 (hello.world!) would become ABC_123__hello_world__

OK, I should be able to figure this out, but I can't. Have a field "AmntDue" which is defined by the calculation '= subtotal - paid'. This is for an invoice, related to a visit consult. I want to rework the definition of the "AmntDue" field so that if no invoice has yet been created, i.e. is empty, is show the text "No Invoice" Not sure if I should be using the IsEmpty function, Case function, or both (or neither!), and how to structure the calculation. I figure the calculation needs to perform the 'subtotal - paid' calculation first, and then run the IsEmpty function on the result of that calculation. Thanks in advance

I'm seeking help with a calculation that would round the duration between 2 dates as follows: Fields: Rental_Start_Date Rental_End_Date Rental_Duration Rules: Rental_Duration will calculate how many weeks between Rental_Start and Rental_End date Rental_Duration can show partial week (e.g. 1.25 weeks, 1.50 weeks) Rental Duration will be rounded to 2 decimal places Rental_Duration fractional value must be either .00, .25, .50, or .75 Examples: 2 weeks, 1 day (i.e. 15 days) would be calculated as 2.00 weeks 2 weeks, 2 days would be calculated as 2.25 weeks 2 weeks, 3 days would be calculated as 2.50 weeks 2 weeks, 4 days would be calculated as 2.50 we…

I have an application which uses long date format made on my Windows 7 computer  (appear in french only)  Font used is Arial  The end user has a Mac OS computer.  On his computer, dates show up partly scrambled, and queries based on these date fields always return no results even if choosen Inside the built in calendar.   See example:  Obviously, there is another step to perform to make that compatible on both Windows and Mac OS but I don't see where is the option. Â

Hello,   Trying to add records in a portal and have them link to a specific foreign key. Normally filemaker would do this automatically, but the layout I am on is not the table I am trying to link these new records to. The reason is because I already have another portal on this layout, and I need to add records to it as well. That portal, is the one I need to link my second portal to.  So I have my first portal, which is working fine, called levels ( as in levels of a house ie Basement), then I have my second portal, rooms. I have a select button on the first portal which, when clicked, executes a script that saves the primary key to a global variable. Then…

I have in an FMPro12 table a field containing data which is always text, each word separated by dashes (they are parts of URL's). Some entries will be a single word (so no dashes); others may be 2, 3 or more words (always separated by dashes). There will never be a 'space' in the field's data. Typical contents might be for example: entertainment european-holidays womens-jewelry-and-accessories Can some kind person give me the Calculation I can use to: (1) replace each 'dash' with a 'space' (2) then uppercase the first letter only of each word (ie, initial capitals) (3) and replace any occurrence of the word 'and' with '&' Many thanks in anticipatio…

I am running FM11 Single user on Mac OSx 10.6.4. Image database with 30,000 records, with jpeg images stored as reference in a folder within the same folder as the dbase. When i move the whole thing to another computer, the dbase loses the image files. there was never a problem with earlier versions of FM. anyone out there seen this? jhfwolf

I hope I am submitting this in the right place. Also I thought I already did this but cannot find it anywhere. I looked in my profile console and did not see any messages. So maybe I did not submit it like I thought. I have a database that is not letting me put anything in the field. I am getting this error: "This field cannot be modified until "Room" is given a valid value." I read up on it and it says something about a matching field. Cannot figure it out. I attached a sample database so you can see what is going on. Thank you. Inventory_ForumUpload.zip

Hello I have a field which contains text of recipes. I want to grab the text between 2 words that are always the same. example: Ingredients apples, oranges, salt, sugar etc. grabs the text "apples, oranges, salt, sugar" Ingredients Blueberries, sugar, salt, wheat, butter, corn meal, milk, cream etc. grabs the text " Blueberries, sugar, salt, wheat, butter, corn meal, milk, cream" Could I please ask you help how to write this calculation to put in a field "Ingredients" Thank you

Hello, I work for a school and I'm creating an evaluation database for our principals to use. Within that we have proficiency areas in which teachers get evaluated and receive a score of HE, E, NI, or U. I have one domain (that holds the proficiency scores) which a can receive 4 possible combinations (PAR1, PAR2, PAR3, PAR4) of scores (ex.. he, he, he, he or he, ni, e, e, etc....); based on the combination, the teacher will then receive a final score for the entire domain of HE, E, NI or U. So example: Teacher1 gets: PAR1 = HE PAR2 = E PAR3 = E PAR4 = E The teacher's domain final score will be an E. What is the easiest way I can script these domainX score…

I am having a really hard time generating the serial YY-XXX, yy:year two digits, XXX: three digit serial to increment. I need it to check the current year and if the last serial is last years, to start over (eg. the last serial being 13-067 in Jan. 1 will lead the next serial to be 14-001). Otherwise to simply increment from the highest XXX by one. Upon the suggestion of another developer I am using executesql for this since fm’s context awareness in the find command renders this unworkable. Having said this I fail to see how fm doesn’t have a way to output this pretty rudimentary serial without resorting to executesql. The script outputs a ? after the executesql…

Get(CurrentLayout)::FieldName Why can I not do this and what should I try instead?

Is it possible to apply a calculation to a text field that will decrease the font size automatically should there be too much text to display on one line so that it fits? I could decrease the font size for the field in question across the piece but it happens very rarely and I'm pushed already for layout space with regard to increasing the field width.

I am wondering if there is a simpler solution, (and perhaps wondering if I am stuck in the 90's) Let ( [ target= "¶3¶"; array = "¶"& "52¶47¶4¶3¶1" & "¶" ; total = PatternCount ( array ; "¶" ) -1 ; pos = Position ( array ; target ; 1 ;1 ) ; sub = Left ( array ; pos ) ; count = PatternCount ( sub ; "¶" ) -1 ]; count & "/" & total )

First, I'm using FileMaker Pro 11 Advanced on a Mac platform I've created a calculation that returns the number of rows that appear in my portal, but it only shows the number of rows before the portal filter is applied. My calculation is this: Count ( Child::MatchField ) I've managed to do this on other layouts by creating a match field that encompasses all the criteria for the records that I want to appear in the portal. However, this particular portal iis filtered by variable information that basically allows me to narrow my results to more specific data. For example: In the Parent table, I have one record and in the Child table I have 20 records that match…