Calculation Engine (Define Fields)

Hi, I'm working on a Database that has a lot of calculation and sub-summary. Everything works fine when I'm on the LAN, but when I tried to test it remotely, pulling up a summary report is really really slow because of the Calculation Fields, sorting and sub summary. So, I was thinking and read about creating a Dummy Table to dump all the records from the actual table with the calculation into a Table to store the calculated value as stored text or numbers. However, I'm having difficulty creating a script that will be run every evening. I've tried using an Export and Import, but the field value does not correspond with the original table fields. Whats the best of of …

Hi, I'm trying to update a Calculated Field in a Child Table, that is getting a Calculated Field using the Auto-Enter. The Auto entry is working fine, but I want the calculated field to change in all the related records in the Child table, when I make changes to the Field in the Related parent Table. I was thinking about using the Evaluate(), but I'm having trouble getting that to work. The goal is to use the Calculated Auto-Enter field for a Relationship for a TO. I can not make a relationship with a Unstirred Calculated value. I've attached a sample file. Thanks. Evaluate.fmp12

Hi there, I have a field which needs to return a value based on the contents of other fields around it, so I'm using a CASE statement to achieve this. Nice and simple, write it once and forget about it. Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B") I want to add something to the CASE statement which evaluates another field based on text contents, so I have tried to use the POSITION command in the CASE statement to make this work, but no luck so far. Here's what I have: Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B"; Position (Description;"TOURIST";1;1) = "Tourist" ; "Tourist") What I'm trying to do is to evaluate the Description fie…

I have a need for a lot of cross tab calculations and have referred to the FM 13 Advanced Training Series, lesson 38 which covers this. I've used it for a while now but it's brutal in that you need to create two calculations for each individual 'cell' you wish to display: One calculation tells Filemaker what should be summarised, usually via a CASE statement. The other is the Summary field itself which appears on the layout. I tend to require 10, sometimes more columns which are based on date fields from an overall record list of 70000 so far. Is there not a better way of doing the cross tab calcs, something like (psuedocode) as I'm wary of fattening up the fields on…

Hi all I've been searching high and low for a custom function but to no avail. Is there a way to search a text field for all words of a particular length and case? e.g. 5 characters and upper case. Thanks

I've searched on the forum and help and can't see if this is something that can be done or not. Any help would be good. I've got the following script as an example where I want to use a subquery to summarise information / choose the top ten etc.The inner query runs perfectly but when I wrap the additional SQL around it I get a "?" as the output. Can Filemaker deal with subqueries like this or is there a way of getting round it that I'm missing? TIA ExecuteSQL( "SELECT a.FullName, a.TIncidents FROM (Select b.FullName, Count(*) as TIncidents From BullyingDetails a JOIN bullying_VICTIM b on a.fk_RaisedBy = b.pk Group BY b.FullName ) a ORDER BY TIn…

How to make filemaker automatically send an email reminder 1 month prior birthday date? For your information this is how I setup our Filemaker enviroment; Filemaker Server is installed on our Server (Windows Server 2008) and the rest of the clients (domain users) have Filemaker Pro 11 installed on their computer. The Database is loaded on Filemaker Server and clients can access it via Filemaker Pro installed on their PCs. On the database, I have user's record with all their birthday dates, I want to setup filemaker so that it will automatically send an email reminder 1 month before the birthday date comes to the user's record and maybe some other users. Please keep in …

I need to covert an ISO 8601 timestamp to FM timestamp. but the supplied time stamp they give the have added fraction of sec. "2014-10-20T16:17:27.300-05:00" What would be the best method of tackeling the date issue? Her is a calculation that I've used in the past for ISO 8601. Let ([ yyyy = Left ( Order_DATE ; 4 ) ; mm = Middle (Order_DATE ; 6 ; 2 ) ; dd = Middle ( Order_DATE ; 9 ;2 ) ; hh = Middle ( Order_DATE ; 12 ; 2 ); nn = Middle ( Order_DATE ; 15 ; 2 ) ; ss = Middle ( Order_DATE ; 18 ; 2 ); …

Hi Guys, I have been searching for a solution to this for awhile now and would appreciate any insights you can offer up, here goes I have database to track and invoice my hours as a truck driver, I have two tables set up, 1. Work (Pic 1) 2. Customers (Pic 2) In the work table i have a calculation field called 'Hours Paid' with this calculation If (Hours Worked<10; "10.0"; Hours Worked) straight forward enough so far... I have recently added a new customer who pays a different min hours to my others so what i need to be able to do is check Hours worked against a field called 'customer::Min Hours' and if less than the 'customer::Min Hours' it will use the number i…

how can i avoid a value existing on another field?

For some fields I use the Proper() function to return the first letter of each word as uppercase, and that works. Only, when there is "sa de cv" at the end of the text (all upper case or mixed), it should be/stay as "sa de cv". Is there a clever calc to put the text in Proper(), except that part, that has to be all in lower case ? I tried several combinations Proper()/Lower()/Substitute with no success, except knowing what is not working. So it is not all wasted time....

Greetings! This is my first post on the forum, and I'm also new to FM. I have a situation where I have a few ideas, but I was hoping to get better advice and direction from others more experienced with this at the start. Preface: The numbering scheme in use may be crazy, but it is established. I am seeking the better way(s) to handle what's in place. Situation: I have an ITEM table, and the items have a unique identifier key which are part of a serial number. A LOG table records each time that one of the items is used. This "serial number" is kept on the item's logsheet. Example: In August of 2014 an item with the ID 46 is used. The serial number would be something li…

Ok Ladies and gentlemen, so I’ve dipped my toes into wanting to start using the ExcuteSQL Function, I have no idea what I’m doing with it. I downloaded Seed codes SQL Explore and I am more lost than ever. I don’t understand the function and I am hoping someone can break it down for me. So something simple to start with. If I wanted to get a total value of a field in a related recorded normally I would just use Sum(TestRecord::Field) This would give me a total How the calculation above gets broke down into ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } ) Is beyond me. Can someone explain?

Hi, working in FM 14 Adv Calc engine [Win]. Till FM13 I could indent lines with Ctrl + Tab What is the key combination in FM 14 ? Or is that not possible anymore.... TIA JM

Hey everyone! Happy Monday! I'm trying to enter a calculation into a date field - then perform find for certain months but I don't quite have the calculation correct yet. I'm trying to get the current month and year (say August, 2015 for example) and then insert a calculated result of August - October for all years prior to 2015. Here is the current calculation I'm using, it works somewhat in that its not getting the 2015 dates but in turn its getting ALL 2014 and 2013 months not just limiting it to the current month August - October (next 2 months) Let ( d = Get ( CurrentDate ) ; Date ( Month ( d ) - 27 ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d ) - 9 ;…

I'm currently using the following code below to text parse a field in my database. It has worked GREAT however I've come to the end of the text and I'm not sure how to say: Pull all text after the Prefix and don't stop at the suffix because there is no suffix. Any Ideas? Let ( [ Textfield = BWIA2 FMP11 2::Web Quote Entry ; prefix = "Comments / other pilots / Explain Losses:" ; suffix = "Bill White Insurance" ; start = Position ( Textfield ; prefix ; 1 ; 1 ) + Length ( prefix ) ; end = Position ( Textfield ; suffix ; start ; 1 ) ] ; Middle ( Textfield ; start ; end - start ) )

I feel like I should know how to do this, but I keep going around in circles. (I have tried to demonstrate what I am trying to do below.) Simply put, I want to populate the field Last Call Date with the date of the last sales call, i.e., Call Date, for each company. (I think I am OK on the sorting afterwards.) Two more things... I need the values in the Last Call Date field to remain unchanged when the data is not sorted, and I can update the Last Call Date when needed. However, it would be nice if it updated itself anytime a more recent Call Date was entered. I would appreciate any assistance! Thanks. Joe

Hi everyone, I have a situation where there are several fields filled out with radio buttons from a value list that contains Yes, No, and Maybe. I need to extract that data and create a report that will give the percentages of how many people selected each of those choices. I can get the job done using multiple fields (say a calc field to extract the Yes, a summery field to count them, and finally a calc field to change them into a precent of the total), but I'm hoping someone has a more elegant way to do this. Thanks!

i´ve got this in the filemaker´s site to know the ages of my contacts (i´m learning to use LET function): Let ( [ T = Get ( CurrentDate ) ; YT = Year ( T ) ] ; YT - Year ( birthdate ) - ( T < Date ( Month ( birthdate ) ; Day ( birthdate ) ; YT ) ) ) what i did before was: Let ( [ T = Get ( CurrentDate ) ; YT = Year ( T ) ] ; If ( T < Date ( Month ( birthdate ) ; Day ( birthdate ) ; YT ) ; YT - Year ( birthdate ) - 1 ; YT - Year ( birthdate ) ) ) both of them work perfectly someone can help me to understand this: YT - Year ( birthdate ) - ( T < Date ( Month ( birthdate ) ; Day ( birthdate ) ; YT ) ) )

Hello! I have a list of attributes in a field that are displayed as a checkbox set. Thus, multiple selections can be made for this field. I have updated my value list for this checkbox set in order to refine it for my current needs. The value list change 1) collapses the value list to provide fewer options that someone could select (eliminates vague values leaving only the more precise ones) and 2) updates the wording of the value list to eliminate confusion. I now want to update the responses to this field by using Replace Field Contents: that is, replace entries that were recorded using the old value list with a corresponding value from the new value list. However, s…

ok, so i'm stuck again and figured the experts here would find this to be easy, but it is just not coming to me. so, i'm building a finance tracking database to track my bills for the month and basically, i'll have two tables. one will be the payees table (which will have the payee name, amount due, date due, etc), and one table will be the list view of all the 'transactions' for the month. essentially, at the beginning of the month, i want to click a button that will run a script to fill in all upcoming bills. which works fine...until i got to one that needs to be paid every friday instead of once a month. so, how can i calculate it to know to add a new record with …

Is there any real difference between using a 'timestamp'-datatype field vs separate 'date'-datatype and 'time'-datatype fields? Both timestamp and time are a number of seconds. Date is a number of days, which could be converted to seconds...there is convenience, and probably some indexing affect, but is there something unique about a timestamp field that can't also be achieved with date and time fields? Is there some inherent difference in their datatypes that enables some beneficial result? Just wondering. -- Justin

How would i calculate the number of days between two dates in filemaker? The dates are in two differnt fields and in the format mm/dd/yyyy. It is generated from a drop down calendar. Someone selects the start date in one field and the end date in another field. I then need a calculation that will know that there is a certain number of days between the two dates. I cant figure out how to do this because of the format of the field. This solution needs to work with file maker 7. Thanks

Hi there I am experimenting on a sample of data in a very simple FM database. I need some help on 'how' FM does things, rather than how to do something myself (although you may scream at my technique below!) I have two customers on Customer_Table with CustomerID and Name as the fields. The only other table is called Data and is joined to Customer via CustomerID_FK. It's a one to many relationship as each customer can have many data rows, around 400 each. Therefore I have around 800 records in the Data table. I have added three text fields to the data table which enable true/false conditions to be set. I use many CASE statements on the Data table to select various…

Hi all Trying to create a more automated way of creating my invoices and I'm running into the issue of calculating elapsed time when it passes midnight. My time is entered as a decimal (i.e. 1:30pm would be 13.50). I'm running into issues when the wrap time (or out time) is past midnight. If the hours entered are between 12am and 12am I'm not having an issue, but as I sometimes work past midnight I need FM to be able to recognise I'm in a new day when I clock out. FileMaker takes the "hours worked" and calculates my daily pay by the hourly rate assigned for that particular job. The "hours worked" is calculated from "call" (in time), meal 1 out, meal 1 in, meal 2 out, m…

Hi Guys, I'm stuck to say I wonder if it has with brain power to do... Table "Pencils" fields: ---------------------------------------- State Text | Has one of two values: "Activated" or "Deactivated" Brand Text |Can be one out of many brands...... Pricetag Numerical |the price of each pencil Sum_Pricetags_samebranded_and_activated pencils Calcu…

Hi there, I have a database with groups of layouts that share the same keyword - e.g. Registrations is one layout, findRegistrations is another and there is RegistrationsList and editRegistrations. I want to create a conditional that is activated when it detects the keyword in the layout. So if Get(layoutName) contains the word "Registrations", perform the conditional. I thought Filter or FilterValues might work but i can't seem to get it to do so. How do I extract the keyword? And is it case sensitive? All advice gratefully received.

HI all, I have an inventory system calculation as follows - Left(SURNAME;5) & DATE & "_" & Right ("00" & Inventory Number ;3) The Inventory Number is a number field with an auto enter calculation - If ( IsEmpty (ARTWORK 2::s_Max_Inventory_Nr) ; 1 ; ARTWORK 2::s_Max_Inventory_Nr +1 ) "S_Max_inventory_nr" is a summary field - maximum of Inventory Number. I'll attach my relationship that makes the system work. Everything runs in order fine.. until a user wants to change the date of an existing record. What happens then is that record becomes the maxium number of the new date (that is fine). However, the old date has not realigned / readjusted, What I mea…

Hi there I'm a bit stuck here and need some help around dates. I have a data set containing values which need to display in columns across the page, left to right in order of date with the most recent on the left. The data is on my table and each record has a date and I'll be summarising depending on other criteria Under normal circumstances, I'll need to display a column per year: 2014, 2013, 2012, 2011 etc. To do this and allow me to easily extract the figures to the correct column: I calculate the most recent year - RecentYear: Summary Maximum on the date field I split out the year alone - Year: Right(Date,4) I calculate the highest year - MaxYear: Right(Recen…

HI all, I have a script (below) which puts in an invoice number. This is then formatted YYMM## into a calculation field. I want the numbers to rest each month. I have done what I think is the hard bit in setting this up. In terms of the numbers reset, they get added in the right order etc. However, as I want to user to have control over the invoice date, I have set the script trigger for my invoice number to run when the date is entered. Thus bringing everything together. The problem is... When a user clicks the date the script runs fine, if the user has entered the wrong date, or wants to change it then the script runs again, thus adding a new number to the same invoic…

I have a WebViewer Text field that I text parse. Is there a way to create a field or indicator that changes the variable to say 1 once the page has finished loading? Then I could set a button for example to appear or turn green ready to click once the page has finished loading Thanks Matt

Hi, I need help removing certain text from a text field. Below is the sample text. Basically, in this case, I need all the text removed (including the date) starting at 11/19/2013 and ending at the last phone number of, in this case, Jeff Kate. Some records may only have two contacts- this one has three. And it's possible that the Work # may be blank, but if it is, the text "Work:" will still be there. There may be text in this field that doesn't have this format (date time stamp, name, cell, home, work). That stuff I need to keep. Thanks in advance for any help! 11/19/2013 4:46:31 AM Bob Smith Cell: (555) 555-1212 Home: (555) 555-1212 Work: (555) 555-1212 11/19/201…

I want to make a calculation that I need help with. I want filemaker to tell me how many days a work order has been late. For instance when we make a work order we give it 7 business days till due. I would like filemaker to tell me how many days its past due if the order is still open. What would the calculation be?

We use filemaker to create work orders. We input due dates between 5-7 days. When we do not finish a job in time we have to manually open the work order and change the due date and print the new work order. Could this be automated for instance: If the the work order is not closed out and it's 5 pm filemaker will automatically adjust the time to the next business day and print a new work order?

Hi Guys, Now if I manually search the date field I can get some record results ie. */1/2015 (in Australia so 1 is January) Now I created a script and the calculation puts "*/1/2015" into the date field YET when it gets to "Perform Find [ ]" it returns no records. Why is there a difference between manually typing it in and searching via a script do you think? Cheers

We have a voting DB. After the first round of votes we need some math to find out if there were any ties and build next election accordingly. There are several groups each group has a max number of positions available. duplicate position numbers mean candidates received the same number of votes. Group A,B,C have one tie in each, so the next election the will be two candidates per group with a max position of 1. Group D, will have a four candidates with a max position of 2 Trying to wrap my mind around this let me know if you have any thoughts. GROUP A - Max positions 13 cand. pos. tie 01 1 02 2 03 3 04 4 05 5 06 6 07 7 08 8 09 9 10 10 11 11 12 12…

Hi there, I am trying to subtract two fields to get the age but I got a wrong calculation. I tried to use Getastext but I am missing something. Please help Today : Get(CurrentDate) Birthday : Text Age : Number

Hi all, Can you help to extract matching domain email addresses from a list of values in a field. Ex.: MultipleValues in a field of record 1: [email protected], [email protected], [email protected], [email protected], [email protected] Output : Want to extract only those email id's whose domain ends with gmail.com. [email protected], [email protected], [email protected] Thanks in advance.

Thanks in advance for anyone who can help me. I use FileMaker 14 on OS X Yosemite Server with FMS 14. We have a file that we just noticed is doing very strange things with the Creation Date and Modification Date fields. 1) Randomly the wrong creation date will be displayed - ie: we KNOW we created the record on 7/6/2015 and it will say a totally different date. 2) As seen in the image I attached... a future date has been created for a record - but it is showing that it was modified prior to the creation date. 3) Creation date in some records is displaying with strange years. ie: 06/19/0210. 4) The creation date created for a few records (random records) has a …

I know this has been answered in various ways before on the forum, but I believe ExecuteSQL might be the best way to achieve this calculation, and I'm currently not very well versed on SQL. I have a table called "Accounts" which tracks the status of our user accounts. The "Accounts" table is displayed within a portal on a layout that is connected to a "system" table which is a global table containing 1 record. They're joined by a cartesian join. I want to be able to show a summary from the Accounts table as follows: How many records are "active status"? - the field would contain a "1". Conversely, I want to also display how many records are "inactive" - the field would …

I need help getting my filemaker to show extra shipping data. At this time my company makes a work order that shows the qty on multiple line items. We then ship certain quantites from those different line items on different dates. My filemaker just shows the total shipped from the very first shipper only, but I need for it to show exactly what has shipped and what is still here for each line item. Please see the attached for more detail on what i'm seeing. Filemaker Screen shots.docx

I would like to simply remove all of the text from after the last /. The number of / would vary so I would first need to count them. Any slick way to do this? /Volumes/mediaProjects4/tntt_71560_murder/forWebReview/Ep_207_Topical_V1.mov

My client has a Contract database. I use UUID for primary keys, however, I have an auto-enter serial to display the Contract ID on the form. Client wants to enter last year's Contracts that were created before FileMaker. So, I opened up the serial field to allow edit (but maintain Unique validation). It's getting messy. As they enter old contracts, they are overwriting the next serial id. Of course, they now have gaps...with which they are not happy. Suggestions for a better approach? Script the Contract ID using a max +1 calc (perhaps using ESQL to obtain that value?).

I was doing a bit of work for a client who is running FM9 clients, connecting to a file hosted on an FM11 server. I don't have FM9, so was doing development on FM11. I thought I had checked out that everything was compatible, but I somehow generated a calculation that doesn't evaluate on 9, but does on 11. The components of the calculations and custom functions used all appear to be innocuous... I am hoping someone here knows what I tripped up on. Here's the field calculation, and the 2 custom function definitions that it uses. The entire point of this calculation is to take a mish-mash text field that they have and make it sort nicely. For this bit I added, I'm j…

Hi All, Please help in calculation: Scenario: I have a table, ABC which has hundreds of records. There is a field, abc_t which has multiple values in it. For example: Values in the field abc_t : In Record 1 : 1 2 3 In Record 2: 2 5 6 4 Now, 1 = A ; 2 = B; 3 = C; 4 = D; 5 = E ; 6 = F Output calculation should be like this : For Record 1: A B C For Record 2: B E F …

Ok, back when I first started my table the standard calculation of end/start worked fine as a repeating value. now that i want to use this time showing function from another "solution" I can't get it to output correctly. it will only show the last repetition i entered and won't repeat to the other values. i know there is a simple solution, but i am at a loss. Let ( [ $end = GetRepetition ( END ; Get ( ActiveRepetitionNumber ) ) ; $start = GetRepetition ( Start ; Get ( ActiveRepetitionNumber)) ] ; Round( Hour( $END - $Start ) + Minute( $END - $Start) / 60 - BREAKS;2)+ If( GetAsNumber($END) < GetAsNumber($Start); 24; 0))

Hi, I have two table, ISLEMLER and GENELPARMAKIZI. ISLEMLER::DATE and GENELPARMAKIZI::DATE in relationship and also ISLEMLER::ID and GENELPARMAKIZI::ID, those are sign persons. I have SIRANO field in ISLEMLER table and SIRANO field in GENELPARMAKIZI table. ISLEMLER::DATE and ISLEMLER::SIRANO are repeating fields, but GENELPARMAKIZI::ID, GENELPARMAKIZI::DATE and GENELPARMAKIZI::SIRANO are nonrepeating fields. I want to get value SIRANO field in GENELPARMAKIZI table to SIRANO field in ISLEMLER table in matching record ISLEMLER::DATE and GENELPARMAKIZI::DATE. For example our person's ID is 127 ; ISLEMLER::DATE[1]=01.06.2015 ISLEMLER::DATE[2]=03.06.2015 ISLEMLER::DATE[3]=0…

I have created a portal on a dashboard that sums invoice totals by the 7 days of the week for a specific store location. That all works fine. DOW Sub Total Amt Sunday $100 Monday $200 Tuesday $150 Wednesday $125 Thursday $225 Friday $210 Saturday $170 I am summing the daily amounts in the INVOICE_SELECT_LOCATION TO by location and as mentioned get the correct results for a location fine on the portal. I also need to chart the portal data by days of the week, the same data on the portal, I am using Google charts for char…

What is the best way to count records of a certain type in a table? For instance I have a table of Categories, primary key field pkCategoryName and a table of Items primary key field pkItemID. The two tables are related (one to many) via a foreign key field fkItemCategoryName (many) in the Items table that correlates with pkCategoryName (one) in the Categories table. I want to count the number of ItemID records that have fkItemCategoryName "X". I know this has to be something fairly simple, perhaps too simple.....but I haven't found a good way to do this. Thanks! ExtraCredit: This is my end goal. I have a table of InventoryItems that contain Make and Model informati…

I'm trying to create a simple inventory system of digital music (downloads) sales so that licenses can be properly tracked. We have to purchase a license for each song sold. I'm having trouble with one part of the reporting. I receive a CSV from the vendor that lists 2 types of transactions: single track (song 1, song 2, etc) and complete album (Album 1 which is made up of song 1, song 2, etc.) I can successfully report totals on the single tracks using a Summary field in the table, but I'm unsure of the best methodology on how to include the songs sold when a complete album is reported. I have 2 tables at this point: Digital Downloads which contains all the data impo…

Hi, I'm pretty new to filemaker and the forums so first I'd like to say hi! Wow what a program filemaker is, I didn't know this was out there until recently, I think it's going to revolutionize my way of working and I've already made great headway on my first project that orginally resided in a excel spreadsheet, but now I'm really stuck. I thought this would be straight forward but I'm struggling to get my head around it. I'm trying to enter two values (number of visits and age) and a corresponding result be picked from an array/table it was pretty easy in excel but it's bent my brain back on itself trying to work this one out lol any advice or pointing in the right di…

Hi All, We have a database that is tracking wild animals that have been released into the wild (radio collared) and requires a lot of calculations based on date data. We are stumped on how to do the following as cleanly as possible: Calculate the number of days to date in a season that an animal has been in the wild. For Example: Table 1 Animal Released Date Today's Date Calc_Days released Wet Hot Season Calc_Days released Dry Cold Season Calc_Days release Dry Hot Season Table 2 Month | Season | num days in season January | Wet Hot | 31 February | Wet Hot | 28.25 March | Wet Hot | 31 April | Wet Hot | 30 May | Dry Cold | 31 etc...for all months A …

Hi. I am just discovering ExecuteSQL and how it can help with powerful calculation fields, but am struggling with something. Could somebody please point me in the right direction? To add to this, I only started to haver this problem this morning shortly after upgrading my Mac copy of FMPA to v14 - but think the problem is me and not the machine or FMPA. Attached is a sample file of what I'm trying to achieve. For example: In this list I have a number of names, each with a score. Let's say I want to find out how many individuals got a TOTAL score of between 20 and 30 and have the "Unique" field display that. It may be the result of 1 score or 10 scores, but the total su…

I have two tables Parent Table - "Company" and Child Table = "Data" each Company has several related "Data" records individualized by a "YEAR" field. Each Year, we import new Data for that year. I need to do some computations between the annual data records for each Company. For example if current year is 2015, average X from the last 3 years(2015, 2014, 2013). I need to do several calculations like this. Bonus, is there a way to automatically update these calculations when the new data comes in for 2016, so then the average would automatically become 2016, 2015, 2014? I'm looking for a maintainable solution and I feel like it's a combination of table relationship…

I have a lot of tables to fill, and I don't want to use a drop down menu to reselect all 3000 entries. Is there a better method to do this? I originally tried lookup but it refused to accept input, then I changed it to drop down, but now It won't auto lookup entries. Is there a way to force this behavior?

Hi there I have a central data set which consists of similar data on a repeating basis for all customers. Data can be grouped by customer, group, status and type. What I am looking to do is flag the first record of each unique 'batch' of data (where customerID, group, status and type are all the same) and then do the same at the first change in one of the fields mentioned. This can be used then in a portal filtered by 'Unique=1'. The data is in the format set out below, with the 'unique' column showing what I'm after: Hope that makes sense - thanks! CustID Code Date Status Type Unique 1010 1 31/12/2014 Primary High Level 1 1010 2 31/12/2014 Primary Hi…

I'm sure there is a really obvious answer to this but help me out if you can. I had been trying to get a "hide object when" object working for a database I am creating and was having issues so I decided to set up a simple test and I still don't understand why I am getting the following result: I have two squares shapes (one blue and one red) and two circle shapes (one blue and one red). I have two fields, shape and colour, with pop up menus covering the square, circle and blue, red options. If I use the following with 'hide object when': Test::Colour = "Red" and Test::Shape = "Circle" everything works fine. Three objects are displayed and the Red Circle hidden.…

K, here is another one for the chart guru's. Using FMPro13 This seems like it should be really easy but i have tried several similar tutorials i found and tried reverse engineering several sales total charts by month and just cant get it to work. On my [CLIENTUPDATE] table i have these two fields in question: (Update Date) & (CLIENTUPDATE_ID) (CLIENTUPDATE_ID) is the primary key in this table. I am trying to create a line graph that counts the number of primary keys "updates" and charts them by month over the last 12 months. Thanks for your help!

Hi - I have several fields that I want to show zero if the value is zero (rather than be blank). Some are calculated fields and some are user editable. On the data tab of the inspector, I have left UNCHECKED the option Do not display number if zero. Yet, they remain blank if zero. The fields are formatted as currency with fixed decimal set to 2. Is there some simple option I'm missing?

Hi there As a part of my simple-as-possible-for-users import routine, I need to import 90 records from an excel spreadsheet. I import first to a checking area, and from there the user clicks 'Go' to import to the final data table. The first record is a date header, along with other confusing things that I need to import, but don't want the user to see. I need to use the date from the header on the remaining records for the final import and when the row is visible on my found set, the calculation I have in the date field for all the other records displays the date. However, when it's hidden, the calculation obviously cannot calculate correctly which is a problem. I n…

I need to identify if a record is the first record on a page. I was hoping to use getnthrecord to do so--along with an unstored calc of get (pagenumber) named pageNumberC to determine the page number of the previous record in a report. The formula is getnthrecord ( pageNumberC ; get (record number) - 1). If this value was less than the current record, I would know that the record is the first on a page. It appears that get (pagenumber) is fully contextual and does not function with getnthrecord. No matter which record I select, it only shows the current page number. And as far as I know, FM can't write data while in Preview mode, so I can't store which page a record wil…

Hi there I have a quick question regarding the final output of a summary zero result. I'm setting up a user import routine that needs to be very, very simple to use with checks and measures throughout. The data that's being imported, due to its nature, always comes back to a summary value of zero. This is the correct position. I'm currently at the testing phase where I'm summarising the values field to get a total and the result is always displayed as "-3.6e-11". Now, I'm happy that's zero, but I want to use this in a custom dialog box: "Your check digit is"&sCheckDigit Obviously I'd rather have "Your check digit is 0.00" displayed, rather than "Your check dig…

I've got a UI element that allows users to drop a file onto a container field on the layout. There's a button as well that lets them select a file using the `Insert File` script step. The options for the `Insert File` script step allow me to force the display of the file to be an icon with the filename. I want to do the same thing when the user drops the file, but as it currently stands, dropping a file shows a preview for a PDF, while using the script shows (on OS X) the Preview app's icon. Is it easily possible to have the dropped file behavior duplicate that of script step? I haven't seen anything in the info window or other areas that seem to change this behavior. I …

I have a tab control layout object with several tabs. I have a global field entitled "g.active tab". I want to set g.active tab to the name of the active tab of the tab control object. I hope this is clear. For example, my tab control object has Student Teachers as tab 1 and Methods Courses as tab 2. When the Student Teachers tab is active, I want to g.active tab to hold the value "Student Teachers" (the name of the tab). Thanks in advance... I hope this can be done!! Phil Feldman

if the log function returns the following results: Log(1) returns 0 Log(10) returns 1 Log(100) returns 2 Log(1000) returns 3 etc. What function/calculation can I use to get the following results? 0 returns 1 1 returns 10 2 returns 100 3 returns 1000 etc.

I am currently updating my database with some new features and have come across an issue that I cannot seem to solve. I am attempting to redesign how my database books rooms for our customers. I currently have 100 rooms and have unlimited customers. I am attempting to design this system to prevent Double Bookings during the same date and time as another customer. My old system just used check boxes for "booking rooms" which required me to look at all customers during that time frame manually. This is my current relationship including the Join Table. I have left out fields that do not have anything to do with this project. Customer Customer ID Name Booking ID Booking…

G'morning, all: I always flub the calculation for inserting a thousands separator in a text/number calculation. (Hey, at least I'm consistent--the hallmark of a good developer. How do I code the calc for this so the character count below has a comma in it? Case (IsEmpty(jobs__cover_letter); "0"; "(" & Length(jobs__cover_letter)) & " characters)" TIA for your help!

I have a records of an audit of a series of rooms which were visited dozens of times. I would like to report the audit via a variety of categories, such as size, use, day visited. In each of the subsummaries I want to see the number of rooms (in summaries). This table is also linked to a parent table which does list the rooms singularly.

Having a bit of a calculation or scripting question, and trying to determine if there's a fast way to do this.. The parent table Project (P1) has a child table of checklists (PC1), related by ProjectType. When I create a new record in P1, I choose a Project Type. This becomes useful if the correct project checklist records (list of related-by-type records from PC1) show up for me to view. The related PC1 checklists may have 1-300 records for any particular Project Type. The complication is that (while in P1), I need to 1) view a list of PC1 related records, and 2) enter 8 additional data points related to each of the records in that list. It was recommended to me, …

Hello I am working on a customization and have following issue: the Date Format is the US format (Month/Day/Year) and I need it to be the swiss/european format (day.month.year) The OS settings are correct, the FileMaker received the info to use the system settings, but there must be something in the file itself. Any help on that? Thank you for any input Cheers

I have a list of rooms. Physically some rooms can be opened up to make a larger room (by removing a dividing wall). The two individual rooms are called Lab1 and Lab2 but when opened up the name becomes Lab1-Lab2. In my list this is recorded as three rooms. I would like each other these rooms to be listed as Lab1-Lab2 so they are summaried correctly. A reconfigured room always follows this format with a hyphen separating the subrooms. Other rooms exist which cannot be reconfigured and have a single name such as Lab3. So this is what I think I need; Lab1=(new field)Lab1-Lab2, Lab2=Lab1-Lab2 Lab1-Lab2=Lab1-Lab2 Lab3=Lab3 In some instances there may be more than two…

I"m having some strange issues with the "Get ( ActiveModifierKey )" command in Filemaker Pro 11. Users are running on Macs, all either running 10.5 or 10.6 and running FMP 11.1-11.3. I have a couple of scripts set up to take advantage of shift+clicking to pop up new windows. Every once in a while the ability to shift+click disappears. It's an extremely intermittent issue but it's a show stopper as some key workflows are access solely via this modifier key. What's even more strange is that when I log on to the users desktop via ARD and try the command, the command works! I can verify that the shift key is working correctly when this issue presents itself as the user …

Hello there - Please help...... I would like to create a field(Day_Count) that determines the day number(in red font) of a Trip/Visit record. Given the following records for the trip identified as 14CHACAP00214: PLACE DATE_IN DATE_OUT NIGHTS DAY_Count Safari Camp Date in: 11.03.2014 Date Out: 13.03.2014 Nights: 2 DAY 1 Cost Dunes Date in: 13.03.2014 Date Out: 13.03.2014 Nights: 0 DAY 3 Mokuti Lodge Date in: 13.03.2014 Date Out: 14.03.2014 Nights: 1 DAY 3 Gross Barmen Date in: 14.03.2014 Date Out: 18.03.2014 Nights: 4 DAY 4 How do i im…

Hello, I have a database with that has a student schedule. The schedule consists of 8 different records related to the student ID. So there are 8 periods per student. Currently, I have the list that shows the student schedule from Period 1 to 8 and consists of The Period Number and Room Number. However, I only want the List to show Period number 7 so I can sort the records by the room number and not have to manually sort it myself when I print it out. I have it done by using a portal and filtering it by period = 7 but when I go to sort it does not sort out by room number.

I have imported a bunch of data from a third party that has all the timestamps formatted like this: 2014-01-17 17:08:54 How can I convert it into a timestamp that FIleMaker will recognize?

Hi folks. Can GetNthRecord be used from the context of a TO related to the one that it's doing the 'getting' in, or can it only do the getting from the context of the TO that it's doing the getting in? Thanks

I have a table that has a field called Customer Number, this field is what connects this table to my main database the Customer Number has a relationship with the field Customer Number in my main database. Within this table Each customer number can have up to 4 records displaying various data. I'm trying to create a new field within that table that automatically creates a number 1-4 based on the record number. So record 1 for customer number X, the field would have an auto calc value of 1 (Aircraft 1), for record 2 for customer x the value would be 2 (Aircraft 2), etc all the way to 4. Customer number Y, record 1 the value again would be 1, record 2, value would be 2.…

In a system that tracks the service records for automobiles there are two tables (Automobile and ServiceVisit). The operators of the system make notes about an automobile’s service visits each time a car is brought in for service. The layout that the operator is working on while making visit notes is contextually based on ServiceVisits. I’d like to be able to display the visit notes from the previous visit on the screen as the operator is making notes about the current visit. I’d like to show in one glance what was done last time so the notes on the current visit are more thoughtful. I guess I need a way to display the value of a field from the previous related rec…

I am trying to search for a simple text string within a List of text strings and return a boolean result if the search text string exists within the list. From what I understand, I can use FilterValues to accomplish this. FilterValues ( "mySearchString" ; $MY_LIST_OF_VALUES ) If 'mySearchString' exists within the variable list 'MY_LIST_OF_VALUES', it will return 'mySearchString'. I should then be able to use the GetAsBoolean function to return TRUE if the value is there and FALSE if the value is not found, correct? If not, what's the correct method to do this? Edit: Well from the Function reference for GetAsBoolean, I guess not. Although even when I say: If ( Fi…

I have attached a simplified file with a calculated field that does not work. The calculated field is: Case (LeftWords (DISPOSITION;1 = "DIED") or LeftWords (DISPOSITION;1 = "EUTHANIZED") and IsEmpty(DATE OF DEATH); "DATE OF DEATH NOT ENTERED") I want the warning displayed if the disposition is either "DIED" or "EUTHANIZED" and the DATE OF DEATH field is empty. Thanks in advance. Untitled.fp7.zip

I'm a novice trying to create a summary report of clusters of various (n) true/false as well as max/min runs of true/false in the dataset. I can do the job in Excel but would really appreciate any help to automate this solution in FM.

I have 9,000-record db with 147 records that have entries in the cells of one field that need to be "fixed". I created a calculated field that "fixes" the entries of each cell in the field. Can I now copy the contents of the fixed (Calculated) field for all 147 records to the original field? Thanks! Joe

Does anyone know how you store data in a Calculation field's "Repetitions"? This isn't about referencing a separate repeating field or a relationship. I want all the action to happen in this one calculation field, but in different repetitions of this calculation field. Example: Calculation field name "CalcyCalc" with "Number of repetitions" in Calculation Options changed from 1 to 3. My calculation would be something like Case(month( Get ( CurrentDate ) )=1, *** repetition 1 of this calculation field CalcyCalc = "Tom" *** ) ; Case(month( Get ( CurrentDate ) )=2, *** repetition 2 of this calculation field CalcyCalc = "Dick" *** ) ; Case(month( Get ( CurrentDate…

Hopefully I've posted this in the right place and someone can point me in the right direction here... I have staff and staffAllocations tables related by staffID. For each staff member I'm trying to visualise their events from the allocations table, displaying them in a row so that the user can easily see what events are allocated to them over the next 10 days. I'm using a repeating field (10 repetitions ) in the staff table with the following calculation: Let ( [ current_date = Extend ( gview_startDate ) + Get ( CalculationRepetitionNumber ) ] ; If ( current_date > Extend ( staffAllocations::dateStart ) and current_date < Extend ( staffAllocations::dateEnd ) +2 …

I have a portal that is displaying a list of items. Each item has a dollar amount and an additional tag on it. Is there a way to create a total of the dollar figure of only a certain selection of the portal records. In my example i have a table called services. It is linked to a table called assets and that table is linked to a table called inventory. My portal shows all the inventory related to the service based on the assets that are related to the service. See the relationship diagram in attachements. With this relationship i can view a portal into the inventory table that looks like the one in the attachement I want to add the total cost amounts seen in the …

I'm looking at doing a Calc Field I think to return based on other info. We have two tables.. Job and Part The Job table we have a layout that has a portal for the Part Table. We are keying of "JOB ID MATCH FIELD" We have a layout that on Job to show us the related Parts. Each part has a status... "In Progress" Or "Complete" I'm wanting a way to return at the Job table the Overall Status of all the Parts. So if all Parts are marked "In Progress" it returns "In Progress" If all the Parts are "Complete" it returns "Complete" but if some are "Complete" and others are "In Progress" I would like it to return "Partially Done" Any tips would be helpful.. I'm using Fil…

Newbie. OK, here goes... • When I make a new entry into a portal, the cursor automatically enters a text field. • This text field triggers a script. (or whenever I enter it) • I had hoped that script would set a variable called "$$todo", with the value being the record's Primary Key, which i had thought it would have grabbed with the following script... Set Variable [$$todo; Value:GetField ( ToDo_Pk )] • But I just get a "?" So in other words, I'm trying to set a variable called "$$todo"... of the record that holds within it a text field I had entered. Obviously there is something wrong with my script, but I haven't got a clue what I'm doing wrong... is there a…

Another question, So I have my inventory database working with an ability to create invoices for checking out equipment. However I need to add 2 things to the database. I need a field in the invoice table that has a status of either active or closed that auto updates in relation to the line items in the portal, when the quantity in equals the quantity out for the line items. Another part I need to add is a field (i'm guessing) on the inventory page that changes when the item has been added to an invoice, so that I know if an item is checked out, and then reverts back when the item is checked in, and make it dependent on the quantity since some items share a generic barco…

For a solution I need to horizontally flip or mirror text. I have a card printer on which I print transparent stickers. These stickers will be mounted on the inside of a window, so in order to be read from the outside the text needs to be mirrored (or flipped, whatever term you prefer). I thought of making a "Reverse" script and use a custom made font that has all characters flipped (eg. http://abc-font.com/font.php?font=Arial Mirror). But this seems a little complex...

At my cabinetshop we need to use a couple different systems for measurement. Some of our customers are engineer types and prefer to communicate in decimal units of measurement. Some of them, however, can't relate to decimals values but understand inches & fractions. Sometimes the fractional result needs to parse to a 1/16th inch of accuracy but other times it needs to be expressed in 32nds. So far nobody has asked about cubits but we do also need to make use of eigths, quarters & halves. Is there a tidy way to express decimal values with different fractional denominators? For example I would like to have 2.03125 display as 2-1/32 3.0625 display as 3 -1…

I've hit a snag using PatternCount to count values within a list for a summary chart and hope someone can point me in the direction of an alternative solution... Here's my problem: I have a summaryReferralReason Summary field which is a List of the referralReasons field, which can contain 5 different unique values selected from a drop-down list and are the subject for a simple bar chart in my summary layout. I couldn't understand why adding up the totals for each of these 5 values in my bar chart were different from the summarised total (which is simply a count of all values) and then it twigged... d'oh!! Here are the first three lines of the calculation I've used to g…

Post: Hi there I have a scenario where I want to be able to upload multiple PDFs against a single parent record and then select which one is viewed. I'm using a portal of the container table with the InsertFile script step to easily allow a user to add PDFs against a parent - that's not so much the issue. However, I'm struggling to come up with a way to view/(stream) a given PDF as obviously they will not view live from a portal, only from a field placed directly on the layout, but the user instinct and therefore best use case is to select from the file manager portal I've built. From the attached image, the right hand side shows container ID 11, which is document 1.…

Hello, I have an inventory system set up with portal relations to orders. Every item has a unique barcode assigned to it that is a string of 4 numbers (i.e. 0001, 0002, etc.). However certain items cannot be barcoded due to their size/shape/material. Also these items do not need to have unique codes. An example would be extension cords, I want that to be a generic id that can be added to a portal row, then adjusted for quantity. Currently if I type my id for extension cords, ACEX, into the portal row, the related record is not displayed. Another id with out a numeric barcode is CXLR10, for a 10' xlr cable. However the portal row reads only the 10 and displays the record …

Hello, I am new to filemaker and have been using it for about three weeks now. I have previously used it as an end user but this is my first time creating databases. I am currently creating a inventory database for film equipment to be check in and out of the office I work at. The inventory has all been logged in the database and that is all set up. My question comes from when I try to create a "Contract" table that will use a portal to line items, with reference to the inventory, it does not complete the rest of the fields in the portal. So I can type in the Barcode of an item, but the item name field remains blank, as well as the notes field. I believe the relationshi…

I'm looking into the possibility of using the FMP URL feature to run certain scripts/processes that can't be handled natively with FMServer. Like creating PDFs, for example. A method that I've used before is to create a simple launcher FMP file that opens the needed file and run the specific script. Is there any advantages/disadvantages in using the URL method over other ways to control a robot machine?

Hi, I have a simple correspondence database with three tables: Contacts (called PEO), Correspodence (called peo_peocoo_COO), and a joint table called peo_PEOCOO. In Contacts, one of the fields is Country. I need to give a single number of how many countries has received correspondence from my team, so I wanted to count number of countries (not the number of times each country has ocurred) in the joint table. I am however having difficulty at using the methods commonly posted in the forums and would really appreciate some assistance. As an example, I have China China Japan China US Japan UK UK The number I would expect from the above is 4 - Four countries …

I'm trying to setup a field where If Type = "Sale", then it shows me the sum of a certain field. My current formula is: If ((Type = "Sale") ; Sum ( Total Sale Price )) and while I don't get an error, I also don't get anything in my field, it's just blank. Thanks for any help.

Not sure where to put this question but if I delete a field, it says it is scanning for references and provides a skip button. And then when I click ok to leave field definitions, it says it is applying changes to layouts and seems to go through all layouts again with a skip button. What happens if I skip? I can't find anything about it. Can anyone provide me where I can find what it means? Does it mean the changes won't be applied to the layouts? Or refinances won't be changed if I skip? How hard would it be for them to put a single sentence below the skip button giving some indication of what it means? Well, I won't rag on about it but sometimes FMI is lazy i…

I would like to place text in a field called "textbegin" before the text that was already there. IN other words. How can I place text in a field before the existing text without losing the original text, If the field contains "mystery tour" I would like place "magical " before "mystery tour". Not sure how to calculate to make the cursor add my text before existing text in a field.

I have a field that typically has a customers first and last name together in this field. I'd like to display just the customers first name or the first word in the field. What is the best way to do this? Show 1st word <<customer_name>> Thanks