Calculation Engine (Define Fields)

I have a DB of a few thousand contractor and trade licenses, all identified by type with a two or three letter code. I am trying to build an email system so we can mass mail various groups of these license holders. I have SMTP emailing working well on other solutions in office, so I think I'm fine on that end. I have been brainstorming for a while, and have come up with a couple of options, but not sure which is the best way to create this solution. 1. Script a multiple criteria find using a variable from a check box set of the license type abbreviations, then send to this found set. 2. Create a field that can be set to either 1 or 0, 1 representing "yes - em…

I need to create a calculation in which if possible i want to use a calculation field text part, e.g calculation field repeats twice, 1st repetition has this data: "030 Service Adjusted Parking Brake" and 2nd repeat has: "less 2,5 settlement discount" - Now, in my calculation i want to use data in the 1st repetition that is "030 Service Adjusted Parking Brake" BUT only taking the three characters in the beginning that is "030" thereby multiplying that value by 1000. How do i construct this calculation - and always i appreciate your help, Thanx

How does one go about creating a versioning mechanism for records in a table? For example: Invoices can have version, Estimates can have versions, Quotes can have versions, etc. While my particular case is not about any of those three types of entities, I do have a need that requires versioning. I thought about the serial increment, but am clueless on how to go about using it. My table is of SCHEMEs A SCHEME must record the person who created it; A SCHEME must have a SCHEME NAME - does not have to be unique because of the need to "version" it. A SCHEME should have a VERSION mechanism, like Version 1, Version 2, etc. The idea is that John Doe sho…

I'm looking for a way to convert text to and from Base64. Cant use a plugin and can't use a custom function (they are usually recursive and thus limited to processing string size) I could convert the Base64 encoded string to a data.txt file and export the file and read it but I can't export and read back in this application. Is there a way to read the contents of a container file without exporting it yet? ... Any amazing technique ideas would be much appreciated. Charles P.S. Technically it does not have to be Base64, just a format 100% web transportable and postable.

I am writing a script where I need to calculate the average of a bunch of numbers. There may be 1 number (where no averaging is calculated) or up to 10 or so. There may be gaps in the numbers from time to time as well. Set Variable [$CalculatedAvg; Value:Average($ListOfValues)] Doesn't work—no variable is created. Can anyone suggest something that could work? I was hoping to use the Average function because it automatically accounts for blank values. Not looking forward to having to eliminate blank values, adding up the remaining values and dividing by ValueCount(). Perhaps virtual list with an average summary field? I guess I'm just surprised some of th…

Not sure where to post this one - moderator please advise if there is a better location. Unfortunately the file I am working with was originally set up with a field that contains numbers as a text field. I have a feeling that this is why I'm having trouble. (BTW - I did not create this problem, but inherited it!) When searching for a number range that breaks across 1000, search returns no results. A search x - 999, and 999 +, both return many results. searching from 100...1500, nothing. Any ideas here?

Hi guys, I need help - again I have on one layout L2 a list of vehicle identified by their registration number(Fields: Vehicle and Reg.Nr) already populated with data, the other field Tour.Nr is not having data. So i have on another layout L1 the Tour.Nr field, Reg.Nr etc, all having data.........So what my challenge is to match if Reg.Nr on L1 matches Reg.Nr on L2, then add Tour.Nr to Tour.Nr field on L2. Also there is start date and end date on both layouts, and the start date and end date fields should match the range for particular week, so the L2 is displaying datainformation for a week range e.g 06.10.2014 - 12.10.2014....... I hope i am being clear, …

Hi, Â I've been troubleshooting an issue with a database that I made and am hoping to get some help. I'm self-taught and am putting together this database at work because there's no one else do it. Â I have a field that was originally set to auto-populate the word "Active." The user interacts with the field through a drop-down menu with the options: Active, Cancelled, and Complete. After some activity, the user selects "Complete." Â The field will show "Complete" for a short period of time and then change back to "Active," usually the following day. The day that the record shows as last modified also changes without anyone manually making any changes. Â Any id…

I am developing a database to help manage job costing for my cabinet shop. This database includes 6 fields: WORKER - CUSTOMER - START - STOP - ELAPSED TIME - TOTAL TIME. Start & Stop are timestamp fields. Elapsed Time calculates the number of minutes between start & stop. Total Time is a Summary field that totals all of the elapsed time results. If I perform a find on the customer field I can see how many minutes everybody has worked on this project. Similarly I can total minutes by worker. I would like to be able to sum individual contribution of all customers or all workers. In the attached example the Customer report would, for exampl…

Hi, In one table (jt_rank) I have a list of the top 20 jockeys and trainers: [j_name] [j_rank][t_name] [t_rank]. On my racing form layout I have fields from another table: [gate] [horse] [jockey] [jock_rank] [trainer] [train_rank] Could someone show me how to put the corresponding rankings where they need to go. Also, I was thinking about returning a value such as ‘nr’ for ‘not ranked’ if that were the case. I started by making the fields on the racing form calculations and then played around with Lookup(sourceField{;failExpression}) … I didn’t get the result I wanted — I wonder, would I be on the right path? Many thanks in advance

Hi, I have a customer that is asking to "comment" out next to each "List Item" that I generate base on the "selected" check boxes. So I placed the "field" that carries the check boxes ( value list ) in the "List" function; List ( Finishing_CheckList ) The above function works perfectly. All I am asking for, is there a way to provide my user with a way to comment out next to each item in the list ... Apple … comment 1 Pear … comment 2 Peach … comment 3 When I add a comment field, whether inside the function or outside the function ... List ( Finishing_CheckList )& " " & Finishing_Finishing Information The "comment" is listed at the bottom of …

I have a project where I need to make a value list of dates based on start date and end date. Once I have a value list I can then select a value position based on other criteria. a date range on the record may be: (start) 1/1/2014 to 4/15/2014 (end) The list created would always be for the first day of each month for the duration of the date range: 1/1/2014 2/1/2014 3/1/2014 4/1/2014 I'd like this to occur as a calc or CF, I'd rather not script this to create a variable because there may be a few hundred needed to generate. Thanks, David

I'm dealing with a rather large system and have written an automated way to build out lists of all the non-container, non-calculation, non-global, non-summary fields. Now, I need to also exclude lookups and auto-enter replaces existing value fields. Here's the calculation as it stands: not ( PatternCount ( $field_type; "StoredCalc" ) or PatternCount ( $field_type; "Summary" ) or PatternCount ( $field_type; "UnstoredCalc" ) or PatternCount ( $field_type; "Global" ) or PatternCount ( $field_type; "Container" ) ) Any ideas on how to obtain this information with a function? Thanks! I also attached the script I'm working on as a PDF though I doubt…

I have a field in a portal named "Assistance | Amount" in a table named "Assistance" that I want to summarize when "Assistance | Church" is "fbcec" and the entries are during a specified date range. I have a count of the occurrences of such a record using the COUNT(*) function, but when I modify the code to give me a sum of the dollar amounts, the field populates with a question mark. Granted, I am not extremely proficient with SQL, but I have looked over this a few times and compared it to the working code I have and the proper syntax for SUM() and I am not sure where my error is. Does anyone more familiar with SQL possibly see where I messed up? Let ( [ ~…

Hi Board! I have a User Interface that allows scanning and registering and validation of loads of information. The last part of the script scoops up all the inputted data and then sets it as variables and then writes to a table. The last step is: Set Field [Job_Logging_Home::Timestamp_Scanned; Get (CurrentTime)] It has been working fine for weeks and then has stopped working. I've checked through and the data is being written, but the Timestamp is returning as 01/01/0001 01:01:01. The Client machine that is running the script has the data correctly set. If I add a 'Set Field [Job_lo.....::Timestamp; 22], for example, it writes to the table as 22. …

I need a way to create 2000 unique 5 digit random numbers that do not being with a zero. I am trying to generate them with a looping script but I am running into two problems. The first problem is that I keep getting duplicates which throws up and error because of the unique validation that I have set for the field. The second problem is that the 5 digit number cannot begin with a zero. This is my current calculation: SerialIncrement ( "00000" ; Int ( Random * 99999 ) ) Ideally I would like the script to automatically recreate another number if it encounters a duplicate instead of throwing up an error. Any help would be greatly appreciated. Thanks.

Hi all I'm attempting to have a traditional time field (ttimein & ttimeout) hh:mm converted to a decimal figure and have the total hours calculated in the decimal format. ttimein would be converted into field dtimein, ttimeout = dtimeout, and ttimetotal = dtimetotal. I'm struggling with the math that would create this conversion (no doubt in my mind it's more simple than I'm thinking). For those unfamiliar: 20:10 would be 20.17 9:22 would be 9.37 23:57 would be 23.95 etc. Some hours would extend past midnight (i.e. start at 16:00 and end at 02:00). I would also be interested in how this could be reversed -- decimal time reverted to traditiona…

I'm attempting to create a calculated field that displays the last date a customer received maintenance. This is the calculation I'm using: Case ( GetNthRecord ( Agreements::PM Type ; Agreements::Agreement count ) = "GPM" or "GPMA-FULL" or "GPMA-HEAT" ; GetAsDate ( GetNthRecord ( Agreements::six mo date ; Agreements::Agreement count ) ) ; GetNthRecord ( Agreements::PM Type ; Agreements::Agreement count ) = "SPM" or "BPM" or "STU" or "BTU" ; GetAsDate (GetNthRecord ( Agreements::Purchase Date ; Agreements::Agreement count ) ) ; "NO PM" ) I thought that this would work, grabbing the last created record, but I am realizing that some of these agreements were inputted…

I am complete Filemaker beginner (self practice), i am working on something that i am struggling with big time I need to sort two seasons(High and Low) number of days depending on a given duration between two dates(start & end date). Each season has the following months, Low Season(Dec, Jan, Feb, Mar, May, June, July) the other months are for High Season. I want the following order such that if i enter a start date of 27/07/2015 and End date of 08/08/2015, i must be able to get for Low season 5 days and for High Season 8 days. Do you have any idea, sample script steps, calculation anything to go about on this problem, please help, i am a complete struggle, i ca…

Hello, I need to create a report similar to the attached PDF ( or Correct Record Number link). In the report I don't want to use the Body part but the Sub-summary; However, when I use the {{RecordNumber}} Symbol in the Sub-summary part, it does not give the consecutive record number (see the Wrong Record Number link). Is there any way to use {{RecordNumber}} Symbol in the Sub-summary part of the report and behave in same way as in body. Correct Record Number https://www.dropbox.com/s/hdaddpi9zdw72ol/CORRECT%20RECORD%20NUMBER.pdf?dl=0 Wrong Record Number https://www.dropbox.com/s/bb3e5ihip88yheb/WRONG%20RECORD%20NUMBER%20.pdf?dl=0 I appreciate any s…

Hello there, I’m relatively new to filemaker and this forum, apologies if I'm posting in the wrong section or haven't searched older posts thoroughly enough. I modified the personnel records database from a starter solution to track out payroll needs, and I’m having a difficult time getting a summary of hours over a current payroll period to generate in a PDF. I’ve tried creating a number field that I just manually enter for each record as a quick fix, and even that won’t generate on the PDF. Ideally this is a function that calculates employee hours over a time frame chosen by the user. Any help would be greatly appreciated.

Hi folks, I've got a register portal with options to mark pupils as Present, Absent, Late, Very Late, Excused. I had a calculation field for each registration that marks how many pupils were present with this - ValueCount ( FilterValues ( List ( JPupilClassAssess_forSelectedAssessment::Attendance ) ; "Present") ) Unfortunately, this counts pupils who were late or very late as absent, when in fact they were present for at least some of the lesson. This is especially important when viewing the data from the pupils own relationship, as kids whose bus is always late could have 0% attendance despite not missing a lesson! Therefore, I'd like to add "Lates" &am…

Hi there... if I have a calculated field that is based on information in another table, is there any way to check to see if that calculated field has been modified?

Hi all I've edited a wonderful example, previously shared by @comment It basically has a list of orders which shows the date the order was made and the price (£) of the order. If you head to the "Chart" Layout, it shows the number of orders by a certain month, and the value (£) of the orders. Can somebody please how I add a total for each year, so this would show Total Revenue and Total number of orders for each year? Any help massively appreciated. Cheers Adam For some reason it won't let me upload my sample file, but you can access it here: https://www.dropbox.com/s/ywea05bmx1h5q3f/ComparisonChartSlots.fp7?dl=0 This was based on 'Compari…

I am challenged by rounding here too - i would like to round up whole number values to nearest ten, e.g 23124 should be rounded up to be 23130.......How do i get around with that? Thanx for assisting.......

Can someone help me with a calculation? Looking to have calc present only the last four of full acct. number. for example: 123456789 to 6789 Any help is much appreciated, thanks for your time!

Less than a year ago I created a revenue table to keep track of projects and revenue. It mirrors the way our financial officer likes to view excel sheets. So this is essentially a flat, non-relational aspect of our database that the FO can export to excel and chart things the way he is used to. The caveat here is that I have to keep creating fields for every month and altering a calculation for every field. <figure 1> | ProjName | FY14_Sep | FY14_Oct | FY14_Nov ... | ProjA | $x | $y | $z | ProjB | $x | $y | $z However, I understand that the proper way to set this up is to have a table for Projects with a one-to-many related tab…

Hi there, I've spent some time researching this and also trying different combinations, but just can't seem to work out how to do this easily. I have a table with around 93 different fields. I need to be able set a flag on a record when any fields in that record are modified. ie have a IsModified field. Then I need to be able to reset that field once the data has been exported. The obvious issue is that simply resetting the IsModified field is a modification so I can never seem reset it. I have looked at the option of using the trigger function in a calculation - That works BUT I'm concerned that as we add further fields in the future it would be q…

Having problem using GetAsBoolean.  My understanding is a text field will evaluate to "0" if the field is empty or the value is 0, and evaluate to "1" if the field contains any other value.  All fields I am trying to evaluate are text fields on the same layout with the correct table occurrence. The results as shown in the dataviewer do not evaluate as I would expect. The company name, streetaddress2, city and state all evaluate as if the field was empty, only the customerid and streetaddress2 appear to evaluate correctly.  Any thoughts on why these fields would evaluate as empty?    Â

Hi there, I have a contact database table as part of a larger solution that I am building and am trying to achieve the following: I have a dropdown box with "Business" and "Individual" and a field called "display name". I am trying to make a calculation that pulls either the business name or an individuals name based on what is selected in the dropdown box. Does anyone have any ideas? I have tried a few variations to no avail. Best regards, Tim

Hello I have a file included here that has 2 layouts Consolidate records and Actual records I would like to make a button to randomize the records which are questions in the consolidate records layout. BUT i would like to use the WEIGHTED randomization from the second layout in the background called Actual records. If you try my example file you will see there are only 5 records and 5 questions in the consolidate records layout. By pressing the button background you will go to the layout Actual records. I f you keep pressing the random button you will easily see the randomization is of the number of records but it reflects the number of times I du…

I want to retain all numbers, hyphens, and decimal points. Thanks.

I would like to find out if it is possible to delete one record less than the found set / in this case my 'found set' records are identical- i have a need to delete all found records except one. Any suggestions? thanks

Hi In the scene details layout, under the stand ins/doubles/stunts tab, I have a portal, when I put anything into the stunts section and complete that row it then removes it from that portal and puts it into the characters tab. It does not do this with the standin or doubles portal and I have checked all the field set up and calculations and cannot see where it is different or why it would be doing this. thanks sarah fully working 2.fmp12.zip

Hello All: I have a text field that I'd like to filter on taking only characters A-Z, 0-9 and some special characters like "@#$%, carriage returns and tabs. My problem is I don't know how to specify the Special Characters, Carriage Return and Tab in the Filter script. Anyone? Dom

Low I am trying to export PDF from FileMaker but I would like to have a workable link URL in it. I am using an exported PDF because I need the exact layout and I need A url L in it Is there any way to export a live URL within a PDF out of filemaker? Thank uou

I can't get this simple calculation to work. Field names; Barcode (item barcode) Exact Import Gadgets Gewicht (imported weight from sql) Gewicht (manual entered weight) Barcode | Exact Import Gadgets Gewicht | Gewicht | RESULT 99 | (any) | (any) | "WEIGHT" (used for csv export) any but 99 | 0 or empty | 0 or empty | "UNKNOWN" any but 99 | any | any | Exact Import Gadgets Gewicht any but 99 | 0 or empty | any | Gewicht any but 99 | any | 0 or empty | Exact Import Gadgets Gewicht Could anyone please help me out ? This is how far I got: Case ( Barcode = "000000099";"Weight"; IsEmpty (Exact Import Gadgets Gewicht…

Hello, I am working on a database that has a checkbox field "Ethnicity" that has the following options: Hispanic or Latino N.A. or Alaskan Asian A.A. or Black White N. Hawaiian or P.I. This works fine for our staff entering data needing to identify students. However, when we prepare a report for the government each year each checkbox needs to reported separately as a 1 or 0 (for yes or no). Here's an example: Hispanic or Latino if selected needs to report "1" in the Ethnic field. If not selected it needs to report a "0" in the Ethnic field. Similarly, if N.A. or Alaskan is selected it needs to report "1" in the Ethnic2 field or a "0" if not selected. This co…

If i have a calculation field called "UNIQUE" which calculates text from two other fields --- can I direct filemaker to find that content example field unique content is "red23" Is there a way to tell filemaker to find the content of the field "unique"?

Is it possible to use Get functions within execute sql statements such as... ExecuteSql(" SELECT dept_t FROM employees WHERE employee_id = ?" ; ""; ""; Get(AccountName)) I have tried this statement and I can't get it to evaluate in the data viewer.

Some years ago I took a standard inventory file and have used it since. It is very good. You enter the date purchased and one of the fields calculates the age in years. It is not working and the formula is beyond my expertise. This is the formula; If( GetAsText( Year(Get(CurrentDate)) - Year(Date Purchased) ) = "1"; GetAsText( Year(Get(CurrentDate)) - Year(Date Purchased) ) & " Year"; GetAsText( Year(Get(CurrentDate)) - Year(Date Purchased) ) ) I would be grateful if anyone could advise what changes I should make to get the field to work properly. Thanks

Hi everyone, am new to filemaker but am quickly learning as I read through posts and comments on this forum. Am embarking on a test project to create a simple database for student records which will display either (FAILED or PASSED) from the total score of the students performance. please how do i go about this?

I am trying to calculate how many minutes pass between when I start a task and when I complete it. I have two timestamp fields: start & stop. They are populated with a script that inserts current time into each field upon clicking the start-stop button. The calculation is as follows: Case ( not IsEmpty ( Stop Time );(Stop Time - Start Time);"" )/60 This works fine as long as all of the hours occur within the same day. As soon as the clock hits midnight, however, the elapsed time becomes a negative value. How should I express the formula?

Hi there I am on the hunt for a function or calculation that will operate like the native Extend () function but I need it to grab the data from another related REPEATING field instead for a non-repeating field which is the limit of the Extend () function. To give a bit of background on what I am doing, I have built a responsive image grid and a responsive notes grid very akin to the Facebook interface. I am at the finish line. The last thing I need to do is add "Edit" and "Delete" options to the bottom of each repetition in the portal (like a Facebook post). All other items in the grid are populated using the extend function and it works as it should of course…

Hello, What i want to do is : i have a portal and want to do a calculation on some records (selection). Tabel 1 : Customer Tabel 2 (portal rows): Article Numbers : 1,2,3,4,5,6 I would like a summary field wich count 1,2,6 and summarize that.

Been a long long time (pre filemaker 7) since ive used filemaker and am trying to make a fairly simple database. I am trying to calculate a number after verifying 2 fields. Basically if A and B match relational database A and B, then I want it to take C divided by relational C multiplied by relational D. The calculation I used is not working, I know I must be missing something stupid and obvious, but would appreciate some help. the calculation I have is If ( A = Relational::A and B = Relational: ; C / Relational::C * Relational:) I tried " " around the answer, and also the case function, but neither worked. What am I missing? Thank you

Hello, I have been searching the internet the whole time but I cannot find the right solution for me. I am trying to create a new database for a networking company. My boss wants me to program it in a way that when a certain member recruits another person into the company, that recruiter will automatically accumulate a certain number of points into his/her account. This is also the same general concept when a registered member places a purchase transaction with the company. For example, for every 1,000 worth of items purchased, he/she will accumulate 5 points into her account. I hope you can help me with this. Thanks!

Hi all, Hopefully I 've posted this in the right place I'm new to filemaker and have been developing a maintenance inventory database. I have a 'stock transactions' portal where I can add and remove stock. I am having trouble figuring out how to put a condition on the amount in stock where there can't be a negative amount. So if there is a quantity of 2 items in stock, only 2 can be removed. (if someone trys to take 3 out of stock at the moment, the stock amount would go to -1 for example). Thanks in advance for any help/advice on this issue, I'm still learning so any feedback is greatly appreciated. Thanks Neil

Hi there, I am extremely new to filemaker but trying to learn on my own. I am creating a betting pool with my friends for this upcoming football season. I want to calcuate how many picks a person got right based on calculating their weekly record and then calculating their overall record. I know this is simple but I can't seem to figure it out. The fields I want to evaluate in my layout "week 1" are: Winner - Team that covered the spread Scott - What team I picked that week The first thing I did was create a calculation field(Scott Weekly TrueorFals) with If(Week 1::Winner = Week 1::Scott; 1; 0). This told me if I picked the correct team I got a 1 an…

I have managed to create a small database for a few number of students to manage their class grades, now i want to add a logo (definitely Group logo image) that should appear on every layout. Please how do i go about this?

Hello i have a problem when calculating date. I want to format the date automatically when i type in 01/23/43 it formats to 01/23/2043 it should be 01/23/1943. Is there a way to calculate this?

While practicing with my first solution, I created a new layout for my fields. these fields where containing student informations from year one to year five. So using the TAB TOOL, i assebled the fields to be placed on TABS (created four 5 tabs in total for each year) Now, I mistakenly moved all fields in the first tab (ie YEAR ONE) , and now i cant grab or move out the fields for the other TABS... all are stock inside YEAR ONE TAB... Each time i tried to highlight the fields to copy, the whole TAB seems to be highlighted, PLEASE HOW DO I UNTAB the fields or move some fields out of a TAB?

OK, so I've been asked to resolve an issue whereby a calculation on a field does not appear to be working as expected. We have a field called 'Activity', and based on what item inthe pick list is chosen, the appropriate description field is displayed (it is something different for each activity). The logic is identical for each field, and the logic is: We have three levels of user - user, office admin and system admin. All users (any level) must add a description, and the user should not be able to save the record if the description is empty. If five minutes since the record has been created has passed, a user cannot modify the description. Office and …

I would like to change the color of a rectangular object in my file with different buttons. How can I make this happen? I would like to make a color change script . I don't know how to script that kind of change.

Hiya -- I need to create a simple calculation that concatenates data from a few fields into a text block that I will then paste into a Google map. Annoyingly, the "new" Google map label feature doesn't recognize/allow simple line-breaks, but does accept a CMD-Return (Ctrl-Enter on PC) to achieve them. Is there a character, code or technique I can use in the Filemaker Calculation engine to modify or replace the Line-Break (paragraph) symbol so that I can get a line break in this way? Thanks!

Hello can someone please tell me how I can return a value that is the Kilometres between two addresses. The addresses are from my database are Starting Address and Destination address. I don't need travelling directions, just the calculated distance. Preferably using Google maps Australia I have FM8

I would like to find out something important for my project... I have 100 records and 44 of those records contain in a field called "division" the number '4" 33 of those records contain in the devision field the number "3" So to find out the percentage of those answers it would be respectively 44% and 33% I would get that by a calculation dividing the number of listing of that number by 100 How do I get a count of all the records that contain in the field division the number "4" ? I am stuck. Thanks

I am trying to make a quizzing system which makes a troublesome area of questions appear more or less depending upon the amount of times they succeed or fail at that question within a record. Each record has a question. I understand how to go to a random record by calculation But I am looking to (by calculation) change the possibility of a record being viewed. I tried adding or subtracting duplicates of a given record but then I have the problem of removing an original record which I don't want to do. My difficulty and need here is to find out how to simply increase the possibility of a record being viewed more often by script or as it is mastered (the answer)…

I have one table with sales. The person who did the selling, the item and date. While they are entering sales I want a summary of how much they have sold in the past month and past year. How would I get a calculation to find the sales persons records and filter them by the date range and then add up the amount field?

Hello, all. I hope you're enjoying your summer. I'm trying to add trailing subtotal average fields to my wife's insulin database (she has fields for breakfast, lunch, dinner and bedtime), but sometimes she doesn't make a record of her blood sugar reading. How can i create a calculation that will average a field without counting blank fields, which would of course skew the average downward? Thanks, and have a great day. Kevin

Trying to filter the number shown on my portal by this calculation Case ( Get (AccountName) = "Matt" ; TDL 2::Producer Code = 3; Get (AccountName) = "Kim" ; TDL 2::Producer Code = 2; Get (AccountName) = "Bill" ; TDL 2::Producer Code = 9; else, show all. ) But its not working Thoughts?

Hi, I would like to put the next serial increment value into a message box but I get no value when I use …. "Your next value is " & GetNextSerialValue ( Get ( FileName ) ; User::UserID ) I don't want to use it, just reference it in a message box. Since the above function yields no value in a message box, is there another way ? Thank you. Tom

Yup, stuck again. First Robin Williams, then Lauren Bacall, now FileMaker vexing me; I don't know how much more I can take. :SÂ Â Please refer to the attached screenshot of the solution I'm building. Â What I'm trying to do is count the number of times a value is repeated under each heading. For example, under Educational Facilitator, I can see there are four 0s, three 1s, three 2s, three 3s, and two 4s; I need to have FileMaker count, then summarize those counts for each heading. Â Now, the particulars: Â This is all in one table (online_evaluations). It was suggested to me this morning that I should create a child table which would record only the number (v…

Hello, I need some help on making a form like this one http://i.imgur.com/wCpcHmc.png?1. I have a database which includes the names and addresses of all the people in my community. I have a separate table just for this form and is linked to the "contacts" table by their address. Each record in the neighborhood watch table has the address of each house. Which then shows the name of the people living there by using a portal. However, here is my problem. The form has two separate fields for Adults and Children. Each person has their own record but is not organized well because not everyone wanted to include their age. So they were given these options: Infant/Toddler PreK-…

Hi there, I need to create a database for printing labels. The actual data stored is minimal (only 3 x fields). The tricky part (for me) is working out how I can select the number of times that a particular record is printed. Example: I want to print a sheet of labels that has record one printed fifteen times on the sheet, then record two printed eight times and so on. I essentially need some way of putting a number in each record that represents the number of times that it will occur in the 'label' report. Any help would be REALLY superb. Thanks people!

Hi everybody, Â I am new to both filemaker and this forum, so i hope i have posted this topic in the right place. Â I am building my first ERP system, and things are going well, but now i have reached a point where i need some help. Â The task for my system: I have 2 'independent' tables: Orders & BankTransfers. And i would like to assign the bank transfers which have the same payment amount as the orders, so that i can see the differences between the bank account transfers and the data in my ERP system. Â Â So I would like to assign / connect orders which have certain criterias, with bank transfers that matches these criterias. Theese criterias would b…

I am trying to find out if there is a way to instantly notify the user if they have an update to their software.

Hello forum. I have the following calculation that always seems to be evaluating to false. It's worked fine for about 2 years, now the script returns 'no records found'. Using the script debugger, and putting this script step/calculation into the data view, always returns the false parameter: If ( Get ( CurrentDate ) < "8/31/" & Year ( Get ( CurrentDate ) ) ; Get ( CurrentDate ) ; "8/31/" & ( Year ( Get ( CurrentDate ) ) - 1 ) ) Basically it's going to create a variable for a global end date used in a search. This script step for the global start date works fine: If ( Get ( CurrentDate ) < "9/1/" & Year ( Get ( CurrentDat…

I have a field that is supposed to change based on the current date, but it does not. The calculation is as follows: Case (CAT'S AGE < 152; "KITTEN"; "ADULT") with the result being TEXT, and CAT'S AGE is defined as (Get(CurrentDate) - DATE OF BIRTH) with the result being a NUMBER When I enter a new record, and a date of birth, the calculation inserts the proper result. But what I want it to do is automatically update based on the current date whenever I open the file. In other words when the current date is 152 days or later from the date of birth the field should change from KITTEN to ADULT automatically. The way it is working now the calculation works pr…

So I want to export a text field to a website. The text field has many, many tab characters to set up alignment of lists, indented paragraphs, etc. However, it appears as though GetAsCSS() completely ignores them. However, I know GetAsCSS() sees them, because when I tried to Substitute() them with an html entity, the text becomes full of "&tab;" or "&nbsp;&nbsp;..." or whatever. Does anyone have any solutions about how to handle this? I haven't been able to find anything. Any ideas are appreciated.

I need to compare three text strings which hold software versions. The checkVersion field must be >= the minimumVersion field and < the currentVersion field and if so, script will continue with an update. The patterns There is consistency in entering the strings as ##0.###.### ### but some managers pre-pad with zeros and some don't. And some use 'build' and some use 'v' or 'ver' so running comparisons is difficult. What I need to solve At this point, I want to just solve it via script and not deal with the hard-coded versions within each of the various files. Please see attached fp7 file for examples of each type of possible combination. Neither do I want…

Hello All, Â My requirement is base on conditions, below is the scenario. Â Â 1) Count total number of Receipt base on The Order Number. Â 2) Count Total Number of Product Qty in the Receipt base on The Ordered Qty. ( Ex : Order Qty is 10, Receipt made 5, Per receipt 2 Qty which come Ordered Qty. ) Â I have tried many was like CASE or IF but cannot get the count/sum, please help me with this. Â Â I have added 2 img with this note. please let me know to go thru. Â Hemen Babla.

Hi I have a 0+ sum calculation in six of my fields. I have used this script on another database with exactly the same formula and the database is pretty much the same, but for some reason the script is not working and not showing zero when it is blank. I cannot see why it is not working, I have even rewritten the calculation from scratch to make sure. Any help welcomed. Thanks Sarah I forgot to say it is when you click the Unit Day report button in Scene Details layout..... Crowd Breakdown step 2 adding in zeros.fmp12.zip

Hey! I'm somewhat of a novice at Filemaker so I'm wondering if this requirement is possible. I have a table that stores events, an event's date doesn't necessarily have to be unique but it would be nice to alert the user if two dates are the same. Would some form of validation or script offer a solution? For the logic, I'm thinking: on input -> if date is equal to existing date in the table -> notify user (e.g. text turns a different colour/ dialog box) -> open matched records in a new window If anyone could advise on how to assemble this then that would be great!

I have a date field and I'm trying to sort/filter my records to only show records with dates of the current month (July) through the next 3 months (October) however id also like to show records from 2013 and 2012 of these months in particular. Example July 2014 ... ... ... 2013 ... ... ... 2012 ... ... ... Aug 2014 ... ... ... 2013 Not showing any records before July or After Month of October Please help, thanks

Hey. I have an work order app that much employees enter work reports into. They will record items that I will invoice out. These jobs could be a month long so the amount of items can be long and man duplicated items. Is there a way to sort through and combine the duplicate items and accumulate the quantity's of eat record.

Been searching but can't find an answer to my particular problem. Have imported a large number of dates with the formats of "01-12-2014" and "1-12-2014". I'm after a Auto Enter Calculation that will automatically add the "0" to the day, such as "01-12-2014" and not "1-12-2014" With Thanks

Please help me with my calculation below, what I'm trying to say is: Show records in the portal (filtered) if the following conditions are met: LSP::ACCT CODE 1 = "q" LSP::QUOTED STATUS = 0 or " " and get user name, if user name is Matt then show records with LSP::PRODUCER CODE = 3 if user name is Kim, then show records with LSP::PRODUCER CODE = 2 etc etc. Here is what I have so far: LSP::ACCT CODE 1 = "q" and (LSP::QUOTED STATUS = 0 or " ") and Case ( Get(UserName) = "Matt"; 3; Get(UserName) = "Kim"; 2; Get(UserName) = "Bill"; 9; ) LSP::PRODUCER CODE = 3 ) Thoughts?

I have a db with over 20k records and I'm introducing a new field to my child table. Each Parent cant have many childs and for each child I would like to assign a unique number starting with 1. the goal is to see all my child items the following way. Parent, child ID, ActivePortalRowNumber 6294 14785236 1 6294 14785237 2 6295 14785238 1 6296 14785239 1 6296 14785240 2 6296 14785241 3 My current challenge is that I can create a calc to get the active portal row number, but its only going forward. how can I go back and set the rest of the 20k. Thanks.

I have multiple layouts set to sort differently on enter. One layout, called Pipeline, sorts and shows all records only if they have been quoted. I'd like to display the number (135) of all quoted records on all of my layouts regardless of how they are sorted. I've tried using a Count Field and when I'm in the Pipeline layout it shows correctly 135 but once I enter a different layout thats sorted differently it displays the count of records with that layout. Any ideas? Thanks

This one has proven too many for me. My Mac's clock is set correctly. The time zone is correctly set to PDT. FMPA12's Get ( CurrentTimeStamp ) returns a time 7 hours in the future (as does Insert Current Time). No FM Server is involved. Any ideas?

I have a field named "time_status" and want this to change what it displays based on another field "status". I want "time_status" to equal 'delayed' if "status" equals delayed. I want "time_status" to equal 'departed' if "status" equals departed. The last IF would be if "status" is empty I want "time_status" to equal another field named "time". This is my first IF calculation and have read some online examples but they don't seem to be working. Could someone guide me through this? Thanks, Adam

I'm getting the error "Specified Table cannot be found" In a field script trigger, I seem to be incorrectly targeting a table even though I am double clicking it out of the field list The script is: If[not IsEmpty(Servetrack2014::QuickFind)] / /prevents searching for a null value// Perform Quick Find(Servetrack2014::QuickFind End If I do have two tables being used in the database. Does that cause problems?

I want to be able to calculate a payroll check. I have done the calculation in Excel. I was able to replicate the calculations in Filemaker, but I am not sure it is the best way. I used a CASE statement to handle the various level of pay to get a proper withholding rate. But each time the tax tables change, the case statements have to be changed. Is there a better way? A better way to store the tax information?

Hi folks, Is there anything wrong with the calculation below (swiped from Filemaker's own website if I remember correctly) correct? It does give ages, but they're wrong & I can't work out what it's doing. GetAsText ( Year ( Get ( CurrentDate ) ) - Year ( DateOfBirth ) - If ( Get ( CurrentDate ) < Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( DateOfBirth ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( DateOfBirth ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( DateOfBirth ) + …

I know there must be a simple way to do this, but I'm just not seeing it and I was wondering if anyone could help me. I have a source field that will contain known values in any combination. Example: VANS = "VAN,53VN,VANL,VANV". Then I have a checkbox field that I'm attempting to match against the first, containing all the various types of vans (VanTypes). I need to get an exact match of one of the values without matching partials. For instance, if VANS = "VAN,53VN, 48VN, REFR" and VanTypes has "REFR" checked, then I expect to get a match count. If VanTypes has "VANL" checked, then no match and no partial match to "VAN". The source (VANS) field is imported and can…

Hello, I have a FileMaker file that connects to an Oracle database via ODBC to display some data. I have a global field set up for searching out serial numbers. I had defined an auto-enter calculation that replaces the existing value in the field, using the Upper() function. This works just fine. However, someone discovered this morning that if they hit the Return key after entering the serial number (thus adding a newline (carriage return) into the field), they would get a different result from the database. So, I went back and changed my calculation to Upper (Trim(SerialSearch)) . This doesn't remove any hard-returns from the end of the input. I have also tri…

In 13, we can custom sort using a field to hold the sort number and then text calculation like this: If ( not Sort ; Name ; Substitute ( 10^Sort - 1 ; 9 ; Char ( 65279 ) ) ) Then we can set the calc field to Unicode (in storage), we can sort the field according to this sort order and if we set the value list to Reorder by Unicode, it too will sort by this Sort order. We have a file which was created in 12 and this technique does not work in it. I also tried Char ( 8203 ) but it does not even move the entry over at all so it simply does not seem to identify them, whether sorting the field (set to unicode) nor displaying the value list (set to unicode). Does a…

Hi I am trying to work out how to get my report to display a zero in the totals at the bottom of the full breakdown report if I don't have any data for that. Say for example I have no chaperones in for that day and unit, it will just leave it blank, but what I would love is a 0 to display at the totals at the end. Does this make sense? I have attached the file. thanks Sarah Crowd (9th edit) 10 backup copy.fmp12.zip

This is doing my headache now.. When paying £££ for FM and reading all this stuff on FM website 'how simple to use it is' I was under impression I'll have this pretty much sorted out in no time, especially that I am not trying to design anything fancy here, just stuff which I was able to do in Excel.. All I want to do is this: My table (GeneralLedgerTable) have i.e the following Fields: Unique ID Invoice Date Total Value Supplier Name Expense Type 00001 1 Jan 2013 $100 xxx Materials 00002 15 Feb 2013 $150 yyyy Tools 00003 18 Feb 2013 $…

Assuming there are fields such as FirstName, Age, and State. Here's all the records in a table 6 records all John 19 CA Andy 27 TX John 34 NY Jake 21 WA John 19 OR Andy 17 TX If I want to group them by FirstName and Age, I would make a calculation field that is 'FirstName & Age' as text. Then make this as unique key then I'd try to create records to other table so that I can make these grouped by combination of 2 fields. But it takes just a bit time to do so because I have to run script of Loop and set field. As the larger number of records, it tales longer.... Is there any way to make these records grouped real quick just like MS Access…

I've been doing some work with the GetThumbnail function in FileMaker recently and I've found that GetThumbnail often creates files larger than the original, even though their pixel dimensions and dpi are lower. For Example, My original file has the following spec: 300dpi 1000 x 1000 pixels 76,490 bytes Using GetThumbnail ( Image; 750; 750 ) gives me an image that is 72dpi 750 x 750 pixels 85,579 bytes So, a file that is a little over half the pixel area and less than a third of the dpi is over 10% larger than the original file. That's not very good, is it? Anyone care to suggest an explanation for this? Could it be because FileMaker is decompressing the…

I thought I was getting the hang of this stuff but obviously not file attached. trying to remove spaces and they will not remove. I know they are spaces because I can backspace one by one and they disappear. But this is from clipboards sent in by my people which I cannot control. I do not want to remove each space manually through 85 records. I even unstored the calculations but it does not help. I am losing my mind. I tried both version 11 and 13 and neither will work. Oh and I used script at first but same results. I want to do this in script. Untitled.fp7.zip

Hi, using a "count of id" summary field in the enclosing layout/table I can get the current total number of rows in the portal based on the portal relationship. But I have three or four filters defined in the portal so that the portal only shows a subset of this total. How can I possibly get the correct number of portal rows that are displayed at any time — outside of the portal on the enclosing layout? Preferably in a global field? I can insert a Record Number Symbol on each portal row that displays the current record number down to the last number. That works fine. But I can't get this value into the enclosing layout/table, whatever means I try. I could …

Working on a POS System and wondering how the calculation on amount due can auto enter into whatever choice of payment is selected. ex. User clicks on Amex and amount due auto loads.

I’ve been trying to figure this out and not ending up where I want to be. Here’s my dilemma. I need to set up a depreciation schedule based on known percentages that look like this. YEAR = Entry field of year MSRP = Entry field of $ MSRP (this field and all following fields are $ amounts) SDISC = This is a 35% automatic discount off of MSRP YR1 = This is an 18% depreciation the 1st year YR2 = This is a 10% depreciation the 2nd year YR3 = This is a 7% depreciation the 3rd year YR4 = This is a 6% depreciation the 4th year YR5 = This is a 6% depreciation the 5th year YR6 = This is a 5% depreciation the 6th year YR7 = This is a 5% depreciation the 7th…

Hi All, I have this fairly simple field, it's a calculation that returns a list, and it works well: If ( IsEmpty ( IP Addresses::IPv4 Address ); "No Static IP"; List ( IP Addresses::IPv4 Address ) ) However, in the resulting list that displays, I'd like to substitute the carriage returns with commas, and I've come up with this bit of code to make the substitution: Substitute ( IP Address Display; "¶" ; ", " ) Is this correct? If so, where do I place the substitute statement? Will it go into the same calculation I use to define the field, or do I have to put this into a brand new field of its own? Thanks in advance, C

Hi, I'm looking to calculate the min value from 4 fields in a record but want to exclude zero values except where the fields are all zero values. I'm very confused and would appreciate any pointers on this. Best regards, Simon

Hello Is there a way to get the last record when i use perform find, Let say the is three record what i want is get the last record infromation

I have 58,000 emails to blast out, but the mail service (mandrill), would like gradual batching, which helps with account rating etc. - I am planning a batch every hour over 48 hours - I would like batch to increase gradually every hour (a curve) Curvev2.zip