Calculation Engine (Define Fields)

For retail point of sale solution, we want to be able to scan item after item without having to click 'New' to create a new record, and without having to input the quanity (quantity remains at one for every scan, which is easy to set). And if the same item gets scanned in again, the program can automatically group it together with the other item and increment the quantity however much. She also mentioned that Walmart has a cool system where if they have an item on sale for 2.95 each and 2 for 5.00, if one is scanned in, the price comes up as 2.95, but if a second one gets scanned in, the second price would come up as 2.05 (2.95 + 2.05 = 5.00). Can anybody give…

Each month I download my credit card transactions into a single table and would like to add the ability to categorize them in the most efficient way. For the time being, I have added a Category field to my import table and manually enter the categories through a value list based on entries to the Category field. This is not efficient, and I would appreciate suggestions on how to do it better. I have thought that a separate table for default categories for each Payee might be better, and I suppose I could relate them by the PayeeName field. But the same category wouldn't always apply to a particular name. Purchases at Office Depot might either be personal or…

Say you have a bunch of auto-enter calcs, some of which have cross-dependencies, such as: A = If(B>1, "YES", "NO") B = C + 1 C = 1 You can see that A can be either "YES" or "NO" depending on whether B is evaluated before or after A. Is there a way to know in which order these will be evaluated? I seem to remember reading somewhere that the field Creation order mattered, but can't find that thread now. The reason I ask is that I have a set of such fields, all of which reference a common "trigger" field. Sometimes, I have to set the trigger field twice to get all the fields to evaluate correctly.

I am trying to print a sales receipt. The header and body print out, but the footer part containing the date, time, and receipt number just won't print. Can anyone help me out here?

Thank you for reading my query. My database has two tables, one holds a string for each record and its attributes (font, pt size, etc). The other is "component" which holds all the parts in a reformatted function. Say I have 25 records that say "Helvetica" and 10 that say "Times New Roman", etc. In the component, I want to know what fonts are used in the text parts table. I don't want to know how many of each. If there are 35 records, then I want some way to know what fonts are needed for this component. I thought a summary would be good, but they all assume numbers and not strings. (all the string values are standardized, so no chance of "helvetika" and "h…

I have a loop function that has an "end loop if Afield =valueListItem ("student" ; "school location") but it seems not to work... it keeps on looping like the value is false.... any hints?

I have 5 markets that are summed. 4 of the 5 markets have data, one doesn't because of the store being closed for renovations. I want to get an average sale per market but not include markets that have 0 sales. The problem is when I use the average function it includes the market that has no sales. How do you get it to average a SUM field with greater than 0 sales? Thanks

Need help What I do Wrong??? GetNthRecord Thenk’s for Help Ino[color:red] :bang: GetNthRecord.zip

Can I get Filemaker to run my scanner to fill the conatiner fields from within FMP 8? I am using it to keep electronic copies of shipping docs.

Good morning all, Firstly, many thanks to all who have helped me to get my memebership database to this stage. I appreciate all your help. Secondly, I have a layout called 'Benefits' which allows the user to add/amend what benefits the member is currently receiving - Retirement, Unemployment etc. However, for some reason i can not get the 'Current Benefit' field in my main layout screens to display any data from the 'Benefits' layout. I've done the same with other layouts in the database (Contributions, Payment Types etc..), but i can not see what i'm doing wrong here. It's bound to be something silly/stupid that i've overlooked/incorrectly done…

hi im not sure which forum is the best to post this question,so here goes. given that its possible to create a table and store records in it without ever having a layout displaying the records, is it possible to view a table without creating a layout? [for example you can do this in ms access] cheers hairy

If you want an image field to be displayed at 100% in a layout, the size of the container must be at least 2 pixels larger than the actual size of the image. I discovered this when I changed a graphic in one of my layouts from being embedded (i.e., pasted into the layout) to being in a container field. I replaced the embedded 24 x 24 graphic with a container of the same size. When I noticed the graphic didn't look exactly the same, a little investigation revealed that the containter was scaling the graphic to 22 x 22. My experimenting so far has been limited to FM 8 on Tiger. -- Ward

This may be really simple, but I am having a problem getting it to work right as I am still new to FM. I am trying to import an iTunes exported text file into a FM database I made. iTunes apparently exports the track Time as seconds. When I import it into FM I cannot get it to convert to minutes:seconds. I have tried changing the Time format of the FM Time field, but that just changes the seconds into really weird numbers. Is there a calculation function I have to use? Ex: I want 136 seconds to read as 2:16. Thanks in advance.

Does anyone know the calculation for highlighting the text in a field?

is there any kinda of fucntion i can put in a calc feild that works by detecting if a feild's data changes like if( Changes(field); result 1; Result 2)

Is there a formula I can use that when run will calculate a date, but if the date falls on a weekend it will oush the result to the next weekday. Example I want to set an "Order_due" field to be current_date+7. If I create that record today order will be marked as due next sunday (a non work day) but would like the result to show as Monday the 26th? Thanks in advance for any help. Steve PS I love the calendar function in the new version!

Hi Does anybody know how to get image properties. I need width, height, and any other that is possible (resolution,etc) If you look at my post http://www.fmforums.com/forum/showtopic.php?tid/172397/ you will see I am displaying linked alias files on web, but it is not showing proportionately. I need to redirect to an appropriately sized layout Thanks

I have a problem where I am decoding and translating a specifically formatted text string into english Basically the original string is composed of "tokens" .... each token contains information for an airline flight segment and follows specific rules. Breaking a single token down into english is not a problem.... but each string can consist of many tokens. I have a calculatin where I break the string into tokens and then translate each... but after 11 I run out of space in the define calculation box (FM says 3000 characcters is the maximum allowed for a calculation) The way I wrote it is to break the string (the "_" character is the token separator), and …

I have set my retail point of sale solution to print a receipt for the customer. But I want to save the date, receipt number and the total to another file, to log the amount for each transaction and to keep a record of the sales for each day. I currently have a relationship via the "date" field, but only the first transaction in that date gets copied over. I am thinking about setting a portal on the other file but can't figure out what relationship to use to map both files. Can anybody give me some pointers on how to do this?

i have a db that stores pdfs of the original document with each record in a container field. i would like to rename the pdf with the record number when it is inserted into the container. is there a way to automate this? thanks!

Hi, I spent about 2-3 hours puzzling out a calculation to take the "From:" header of an email message imported into Filemaker and clean it up so that ONLY the email address remains. It's useful when you need to make a relation that matches based on email address. Here it is. Comments or suggestions welcomed. ------Start Calculation--------- /* This calculation examines the From: header of an email message and cleans it, yielding JUST the email address. It removes full names with or without quotation marks and spaces in them, before or after the email address, and also removes quote marks and < > symbols. My purpose for the calculation is to facilitate clea…

I have an auto-calc field with: Case ( playlist_show = "BFC"; "4p"; playlist_show = "HWD"; "4.5p"; playlist_show = "HHS"; "5p"; playlist_show = "ROS"; "6p"; playlist_show = "BLA"; "7p"; playlist_show = "ROS"; "7.5p"; playlist_show = "ROS"; "8"; playlist_show = "HWD"; "8.5p"; playlist_show = "PTI"; "9p"; playlist_show = "ROS"; "9.5p"; playlist_show = "WLD"; "10p"; playlist_show = "BDB"; "10p"; playlist_show = "ROS"; "10.5p"; playlist_show = "ROS"; "11p"; playlist_show = "JCR"; "11.5p"; ) In the case of ROS, the show can be at more than one time; in the calc I have provided above, those possibilities are not available -- fu…

I am relatively new to database development and find that I have dived into it head first with a full major project in the works already. While I've learned a huge amount in only 13 months, I discover almost daily that I am lacking some critical understanding required to make further progress and refinements. Recently I discovered the power of recursive calculations, however, I am struggling with the logic required to actually write them. Is there somewhere I can get a sound and easy to understand tutorial on writing recursive calcs? Or, maybe, I'm even lacking in an inderstanding of what a recursive calc actually IS... Thanks in advance! Nathan

I have a calculation field: lastname_comma_firstname my problem is that names that have apostrophes are showing like this: John O'shea when it should be John O'Shea can anyone help me to write a calculation that will prevent this from happening ? the letter following the apostrophe will never be a lower case letter.. as far as I can foretell

Is it possible to lock a field size so that a user cannot type beyond its boundries? I have a narrative field that I would like restricted to a certain size.

Hi there! In our school we do all of our GPA calculations for transcripts within FileMaker. I imported all of the marks from Excel. The calculation is something like: If(GetAsNumber(Marks S1) < 40; "0"; If(GetAsNumber(Marks S1) >= 40 and GetAsNumber(Marks S1) <= 53; "0.25"; If(GetAsNumber(Marks S1) >= 54 and GetAsNumber(Marks S1) <= 56; "1.00"; If(GetAsNumber(Marks S1) >= 57 ... etc. I've noticed however that it is not calculating properly and the only way it does calculate properly is if there aren't any decimal places. Why would having decimal places effect the calculation? 24.3 is less than 50 whether it be 24 or 24.3…

Hello, all! Newbie here again, beginning to love these forums. : In my Orders table, I've got a portal to the Line Items table which shows the following fields: ItemID (drop down menu, text field) ItemCost (looked-up) Quantity (number field) TotalItemCost ("ItemCost" * "Quantity") Back on the Orders table, there's a GrandTotal field that sums all the TotalItemCost fields that for that particular order number, and everything works fine. So here's my question. I have an Inventory table which contains all items, our cost for each, and the quantity we have in stock. Somehow I want to automatically subtract from that stock when an order is placed on t…

I need to convert a serial number (01,02,03...) to an Alpha? i.e. 01=A, 02=B,...10=J,...26=Z. I have a portal set up which creates new line items for a specific Job# (related by Job#). I have a couple of calculation fields in the related file that give each related entry a unique serial number (-001, -002,...). In my master file I want a "Line Item" field. It is simple enough to have a calculation using the Right function to get a serial (01,02etc) and I can convert this to Alpha using a longwinded Case statement. Is there a simpler way of achieving this

I want all records which have a creation or modification timestamp greater than 12/12/2005 11:59 PM. I want a 1 boolean. I've tried (calculation number): CreateTimeStamp > Timestamp(12/12/2005; 11:59:00 ) or ModifTimeStamp > Timestamp(12/12/2005; 11:59:00 ) ... it says ':59:00' field can't be found CreateTimeStamp > Timestamp(12/12/2005; 11 PM) or ModifTimeStamp > Timestamp(12/12/2005; 11 PM) ... it says the PM field can't be found CreateTimeStamp > 12/12/2005 11:59:00 or ModifTimeStamp > 12/12/2005 11:59:00 ... everything from first :59:00 on is highlighted needing operator. Geez. I've tried many other things but I"ll spar…

This is something of a technical question, and rather complicated, but here goes: I have a solution in which I have a central Globals table in my interface file. Most of the solution actively uses this globals file for storing and calculating various results. The Globals table has one "real" data field, and all the rest are globals or calculations. I set up each global field to auto-enter a default calculation always. I also installed a startup script that deletes all records from the table and then creates a new single record. The combined effect of the default values and the startup script is to initialize every value at startup. This helps me cut down on scri…

I have a solution to print receipts for goods sold at point of sale counter. I want the current date and current time (down to the second) to be printed at the time the receipt gets printed out. I have placed a time field and a date field in the footer part of my receipt layout, but it just won't refresh. Can anyone tell me how I can go about fixing this problem?

I have two separate tables, Expenses and Deposits, that are not related. How can I create a report to calculate their balance?

Ihave a table with two containers. In one i put a jpg file by dragging and dropping. In the other I add a jpg by clicking on the field and then adding the file. I have done this with about 200 pictures. In one case I can double left click the container and the jpg opens up in my default graphics program. In the other case I have to right click – save the picture – and then open. An interesting inconsistency I do not understand.

I need to calculate the ratio between two numbers, A and B, for each record and the average for multiple records. The ratio calculation is R = A / B. I created a summary field to get the average ratio and weighted it by the B column. I also created summary fields to get the totals for A and B. In the example below I have three records showing the values for each column, the last record has no value for field B. A, B, R 30, 5, 6 15, 2, 7.5 15, 0, ? The summary fields in FM shows. 60, 7, 6.43 What can I do to get FM to consider that last record that has no value for B and give an average ratio of 8.57 ( 60 / 7 = 8.57 )on the summary line? Tha…

I have a problem trying to use a number that needs to be formatted as currency in a new calculated text field. The new field I'm creating will be a Text field containing the values of the Account, Date, Amount, Memo. The Amount is in a field which is defined as a number (i.e. 1000 or 10.64). In the new calculated field, I need to add the currency sign, the commas and the decimal points. I can't figure out how to do that. Whan I create my calculation in the new field, it formats the field just as it is stored. My calc is: Donor & "|" & Date & " - " & Amt & " - " & Memo. Any help would be appricaited. Thanks - ke…

I've created a problem that I am hoping someone can give me a simple solution for. I have a database with several tables. I have a session table that is created/deleted everytime a user logs in/out. All of my other tables are related to the sessions table and the records are viewed through portal windows. Each related table has a calcualtion/container field within it. This field is used to create a highlight. The idea is that when I "select" a record in the portal window, the session xd field is set with that records id. The highlight field contains a calculation that basically says when the id field = the session xd field get the light green object in my g…

But im beating my against a wall trying to figure this out. I have a table in which I would like to show a portal of all incomplete records only. The record is completed simply by checking a (check box) field. In the portal I would like to see only the records that are incomplete, so that once the record is checked it will disappear from the portal. Displayed in the portal would be the person's name that the record belongs to as well as a case number. Any suggestions would be greatly appreciated.

Hello, I'm currently creating a membership database. When updating a data for a member, the database creates a scrollable 'history' of all the changes that have taken place for that member. This is like a snapshot of what the member's info was when info was updated/changed. (This needs to be like this for the purposes of the database - the users need to have this history data available). I also have an auto-generating membership number, for when a new member is added. However, the problem i am experiencing, is when i update a member's info and the database creates a new 'history snapshot' - it gives the new history the next membership number, rather th…

I want to present the user with some information in a scrolling text field that includes a small bitmap graphic. Is this possible?

Sorry if this is in the wrong place. I've got 2 different fields that hold the same type of data. i want a report that has boththose fields in the same column. IOW I've got a form that takes info about classroom bookings. I needed one for the am and one for the pm. So i have amSchool and pmSchool fields, and the same for number of kids coming to each. Now i want a report that shows all the schools and how many kids came for each, with no diffrence between am and pm. Any suggestions would be great!!

Hi all, I would like to create a serial nnumber for invoices purposes. I would like to have so that the first 3 digits are letter, then month and year, then a serial number. The problem that I am having is that I would like the date to appear as 1205 for december 2005, but all the time the year appears as 4 digits (122005). Any tips? Thanks Nik.Be

Is there any way of inserting a tab character into a field by calculation (similar to the way a ¶ can be added) for formatting? The problem is I export large amounts of data in tab separated text files. Much of this is from related tables in large chunks. eg a typical export might have two fields from the source table, 25 fields from related table A , another couple from the original table and thirty from table A etc etc What I was wanting to try was combining each chunk of fields from the related table into single calculated fields and thereby reduce the numberof fields in the export dramatically. In fact in the main export it would actually red…

Well I use this type of text calc all the time (please see attachment)! But this time it won't work!! If ( IsEmpty ( ShortcutCode ) ; TextSize ( Blue ( "Click to Assign Shortcut" ) ; 12 ) ; ShortcutCode ) Blue() is a simple CF of: TextColor(text; (220)) I have NEVER had a problem with TextSize or my CF colors working together and I use them a lot! But if you delete entry in ShortcutCode, you will see the "Click to Assign Shortcut" should change to 12pt. But it remains at 15. There must be a formatting rule (or principle) I'm missing here, but I can't see it. Can you? LaRetta FontsColors.zip

I'm doing an expense report that resembles an Excel spreadsheet, in that there are vertical columns and horizontal rows. The layout consists mainly of repeating calculating fields and the columns must have a vertical total on the bottom and the rows a horizontal total for each row. This part works fine. The report is not tied down to a particular time period other than it cannot have records more than 60 days old, so one report could encompass up to 60 days of data AND each record data must be independent of the other records. Each entry is tied to an Accounting Code (1 thru 9). Example: 11/5/05 may be tied to Accounting Code 3 and 11/12/05 may be tied to Accounting …

Previously in FM5 I used a series of case statements to transform numbers to words for numbers up to 9999. Is there an easier way in FM7 with the Let function? I guess I'm a bit burned out today and hope that I don't have to reinvent the wheel, if someone has already done it. Thanks

I have a real mind bender for me...and hopefully someone can help. This question might be more appropriate in the "scripting" forum but I thought I would start here. I have a DB I am developing where I have to import thousands of records from an Excel spread sheet. The data looks (kind of!)looks like this from excel: Top row = header info. mach ejid loca NEED# 123A 9876 MACH 00001 123A 9876 MACH 00015 123A 9876 CARO 00013 122B 1234 MACH NOCHG 123A 5678 CARO NOCHG 122B 7654 CARO 00003 Looking at the data the first three lines all have the same EJID. This is as it should be. However, when imported into file maker each line will become its own…

I found my own answer! I know I usually turn those off after I really know an application, but at first they can be very helpful. Thanks for any help. Steve

I have a simple FM6 database that includes a text field "Activity" and a calculation field "Group". I have set up a calculation based on the data in "Activity" to give a result in "Group" eg If(Activity="SRC","School Representation", If(Activity="Sports Council","School Representation", "" )) etc. But I have a range of data in "Activity" such as Merit - Science Merit - English heaps of etc. What I want to do is to identify the text "Merit" within each record to end up with a calculation that produces the data "Academic Award" in the "Group" field because I don;t want to write a heap of If(Activity=) for each possibility. Is there a…

I can't get anything but a ? to appear in a text field called "Status" with this simple autocalc lookup: Evaluate("Updated";triggerfield) When I enter something into triggerfield a "?" appears in the field called "Status". When I change the calculation to say 1+1 I get a 2. Status is definitely a text field expecting a text result. What am I doing wrong? Thanks in advance

I'm a new FM and need help. I have a client who has a database with a text field where users enter normally 10 integers. Now I want to: 1) test for 10 integers, and if less, prompt the user and yet still allow them to enter 1-10 integars. 2) make a test to force users to enter integers only becuase sometimes they mistakenly enter/mix letters with the integers. 3) I cannot lose old data from this field. Thanks, Denis

Hi, is there way that I can find the first five characters in a field that matches WITHOUT putting in any words or letters. I was think something like wildcards. So in the example below, I would get all Disable and all Molly records because they match. Example: Name Disable smiles Disable me Jack Daniels Joe Moe Molly Brown Molly Black

Is there any way to add Creation Date and Modification Date fields to a table and have them take on the correct values, months after I've created the record?

I want to put word documents and pdf files into a container field and be able to view them from inside the database. I use the insert file to get them into the container field, I am not sure how to view them in the DB or have word or acrobat open them automatically to view them. Than you very much.

Hi everybody, I have a scheduling database for my school guidance office. At the momment, it has no calendar feature...yet. In the meantime, I have a giant list of dates all in the system that users just click on. However, this list is extremely large and I need a way to mark the first day of a month (there are entries for each schoolday of the month in the system). Is it possible to do a calculattion that looks at my field called Date. I think I need an if statments that says if the Date is equal to the first day of the month, write whatever that month's name is. Any thoughts...I'm so lost.

I am using a calc ( field1 & field2 ) in a portal. I would like field1 and field2 to line up one under each other like columns; however, the charcater count for field1 varies ... Is there a way to define the length of field1 so that if there are say, five characters, the field length remains the equivalency of say fifteen characters, so that the values for filed2 always start in the same place in the portal layout? I have tried several calc definitions like Length, etc. and there doesnot seem to be something like characterCount = 15. Is this the best way to accomplish this or it there some other approach that would work better?

I would like to know if it is possible to have a field prevent modification once data is entered into it. If this is possible can you please tell me how to do this. Also is there was way to work that into a privlege set so that only certain individulas have access to change the field that have administrative privleges. I know how to limit editing on the privlege set but i want the rest of the record to be modifiable, just the one field to be not modifialble after data is entered into it. Thanks, Ron

See attached file for model. HELLO! Here is a fun calc problem for you math men/women! Goal is to provide user with maximized plate count for desired weight. User enters desired weight, then enters the weight of the bar that will be used for exercise. Field clc_plate_weight then subtracts the bar weight from desired weight to give a plate weight total. This total now has to be divided most efficiently in divisions of 2 in the following increments: 45, 35, 25, 10, 5, 2.5. EI. For a desired weight of 145 lbs. and a bar weight of 45 we would need to plate 100 lbs. 100 lbs. plate break down would result in: 2-45s, 2-5s. THANKS for any help! Plate…

Is it possible to have a global field in a portal? For example, I want to be to log the progress of my project, with the progression bar I created as a global field. I want my users to be able to log the date and notate that 50% of the project is complete, with the progression bar at 50%. Then another record in that portal will show a different date with the progression bar being at 75%.

I am designing a project management database and for each project I have start date and end date. I want to make a view in filemaker pro which would be something similar to a standard project management tasklist; to show a colored bar staring from start date of the project till end date (or week) with weeks as columns and projects as rows. Any suggestions, how can I do that? Thanks in advance, Aditya

I'm trying to write a calculation that determines, out of 10 fields in linear order, which is the first one to have a valid entry that is not empty. I also need, by the way, to know which is the last one to have a valid entry. Here's the scenario: Fields 1-10, plus First Entry and Last Entry If all the fields are populated, I want First Entry = Field1 and Last Entry = Field10 If only Field10 is empty, I want Last Entry = Field9 If only Field1 and Field2 are empty: First Entry=Field3, Last Entry=Field10 If 1 is empty, 2 is populated, 3 and 4 are empty, 5 is populated, 6 is empty and 7 is populated: First Entry=Field2, Last Entry=Field7 I'…

I'm sure that one of the biggest uses of the GetAsText function is to extract the filename/path from a container field that references to a file. Which works fine for me, but it only returns the file name, but not the path. I'm using this function in a calculated field GetAsText (containerfield), with results as Text. Upon reading the help section, it states it'll return the "external path information". When I read that, I think the path and filename. Am I wrong in assuming this?? Or is there something I'm not doing right?

Hello, I'm currently creating a membership database. When updating a data for a member, the database creates a scrollable 'history' of all the changes that have taken place for that member. This is like a snapshot of what the member's info was when info was updated/changed. (This needs to be like this for the purposes of the database - the users need to have this history data available). I also have an auto-generating membership number, for when a new member is added. However, the problem i am experiencing, is when i update a member's info and the database creates a new 'history snapshot' - it gives the new history the next membership number, rather t…

In our database, each record requires varying amounts of image containers. The images need to be large to show the detail required. Since we need to print reports (and save PDF's) of these records- and the reports can be many pages- what is the best way to be able to only have the number of large image containers necessary for each record. We want to embed and not link the files. Can we have a... add image container button (where the user can add additional containers), therefore only displaying the required amounts of images. suppress the reports from printing/ saving to PDF image containers that are empty? some other solution? thanks so much for yo…

I have a number field formatted as a decimal with 2 fixed decimal digits. So if I type 100 into the field, it displays as 100.00 Is there a way to get the displayed value out of that field (100.00 as opposed to the actual field value of 100) to display in a text field? Or, do I have to monkey with the number by adding decimal digits (via a calc) if they weren't manually typed?

Hello, I'm sure this is simple to most of you, but could you please tell me the best way to do this: I want to type one of three abbreviations (that I define) into a field, and have the second field automatically put in data according to which abbreviation I type in the first field. For Example: Lets say I have Field A and Field B. In field A, I would enter abbreviations I have created, either xe, eps, or cc. If I entered "xe" in Field A, I want it to automatically enter $.80 in Field B. If I enter "eps" in Field A I want it to automatically enter $1.80 in Field B. If I enter "cc" in Field A I want it to automatically enter $.75 in Field B. And so on.... Tha…

I am building a classic school database application for our Horseback riding school. My wife needs me to sort the "[color:blue]CLASS records" by days of week in a predefined (numerical) order - with each class displaying a list of students who ride on a particular day: ===================================== Monday Class-1 Enrollments: - Allen - Betty - Jan - etc..... Tueday Class-1 Enrollments: - Tim - Willie - Sally - etc... Tueday Class-2 Enrollments: - Stacey - Sharon - Betty - etc... ======================================= To accomplish this, I created a separate TABLE called "[color:blue]DAYSOFWEEK" wi…

I have two fields I wish to sort in ascending order. LastName and TypeOfAd LastName contains a list of surnames and TypeOfAd contains either Black and White or Color. I view this with View as List. Is it possible (after the sort is done) to have a field that will automatically contain a sequential number starting at 1.

Here is my set up. I have clients that insist on going with IWP. And, rather than use FM's built in field validation, I was just wanting red text to appear next to things that get entered wrong. Rather than have 1 "Error" field for every field, I thought I'd use a Repeating Field. After Reading up a bit on this: http://fmforums.com/forum/showtopic.php?tid/121153/post/121517/hl/%22Repeating+Field+Calculation%22/#121517 I got the fields set the way I wanted them. It works great, However, I cannot get any reps besides the first one to update. I've tried numerous things, (Evaluate in the Choose function, putting the If statments in the Choose functio…

I am having weird results in what should be a simple calculation. I have a start time field which is a simple edit box time field. The end time field is a calculation the result of start time + default appointment time (a related time field based on the name of the appointment in another table) + adjust time, a field to allow for appointments which are different from the default. Some records calculate properly and some do not. It seems independent of the appointment selected, for instance tomorrow I have a 3 to 3:45 appointment which properly calculates. But the 4 to 4:45 appointment calculates as 4 to 5. Some 20 minute appointments calculate as an hour also. A 2.5 hour …

Hello ppl Can someone tell me, how can I get the total result from a search made earlier. Ex.. I preform a find to see all the sales made in 2005, than I want to know how much was sold in that year.... Thanks

I am making a Filemaker program that will be used for my work. What we do is set appointments for home finial advisors to attend. There is 1 field that is the date called "Apptdate", then there is another 3 fields for Rescheduals. These are called "Apptdate2", "Apptdate3", "Apptdate4". I have made another field called "latestappt" and what i want to do is to get Filemaker to search the 4 boxes. If "Apptdate4" is empty, copy "Apptdate3" to "latestappt", unless "Apptdate3" is empty, then copy "Apptdate2" to "latestappt" unless that is empty, then copy "Apptdate" to "latestappt". Now i was going to make "latestappt" a calculation, however i wasnt having much luck w…

This is probably a really stupid question and I'm not sure if it would be done as a script or defining the field, but here goes... Does anyone know how to ensure that a field will have a value if and only if another field isn't empty? I have a "Measurement" field (text) and if it's not empty, then the numeric "Rating" field must also have a value. If "Measurement" IS empty, then "Rating" must be empty. Also, in case it matters, in the define field options, "Rating" is validated by RANGE (must be 1 to 5) and MAXIMUM (3 characters). Since this is a web database, validation occurs when they Submit the record. The Range and Maximum validations work, bu…

This DB that I am working on requires that raw data from an excel spread sheet be imported to FMP. For some reason in a text field I call "machine" The Excel Cell has a "space" after the last charecter that carries into the import...so the import result = 114A (space) I have a seperate table that has machine properties based on machine number, like 114A. In the table in question I am doing a lookup to fill to fields based on the relationship between the identical machine numbers. The problem is, since Excle is putting this ******* space after the machine number, the look-ups won;t work. I know this is the problem, because if I manually enter t…

Happy Saturday everyone! Unfortunately, my "raw" data looks like this: 9/1/2005 6:05:11 In one cell in an Excel spreadsheet. Can I parse this data on import so I can get date and time stamp in seperate fields? I have to admit, I don't know how to parse, and am trying to learn. Any help? Steve

I have a Timestamp field defined to Auto-Enter the Creation Timestamp (Date & Time). When I manually create a new record, the field is filled in as expected. But when a new record is created by Importing an Image, the Timestamp field is left empty. (I've not tested what happens when Import modifies an existing record because my current database always starts with an empty table.) While it's possible for my import script to fill in this missing value in each record, it's a hassle and a performance hit. -- Ward P.S. I've submitted this as a formal FileMaker Problem Report.

I have a few related records of text in a LineItem table for each recond in the Main table. All fields are text fields. The user would not be entering data here, simplying reading it. I want to display the records in the line times as one text field along side the fields in the Main table. So far the only solution I have come up with (still stuck in FM5 mind set) is to have a portal displaying the related line items and a script that goes from first portal row to the last row copying (using set field) the contents of each portal row text field to a Temp field in Main table record. Thus every time I would go to that Main table layout and show the record, the scrip…

I have a Date field that I have named "DoItBefore". And when the date I have put there is history then I want it to show in red. Now, it seems that isn't possible unless I do a trick. I show another field which is a calculation field instead, with this: If ( DoItBefore < Get ( CurrentDate ) and Status = "Open";TextColor ( DoItBefore ; RGB ( 250 ; 0 ; 0 ) ); TextColor ( DoItBefore ; RGB ( 0 ; 0 ; 0 ) )) Anybody knows if it can be done directly on the Date field? :shower:

Hello, I would like to know how a user can go to a layout and not see the previous entry into that layout. When a user enters the layout they see the previous entry which they can edit. Only when new record is selected will all of the fields clear and then a new record can be added. I would like it to be clear upon entry to the layout. Your help is appreciated

I have 50 records in File A. Each record has a number of fields. (Call them Field X, Field Y, Field Z, etc.) I am attempting to build File B that will contain a single field (Field XX) that will contain the information from multiple fields in File A. However, I am trying to do this in this manner. File B - Field XX: 1st 50 records = info from File A - Field X 2nd 50 records = info from File A - Field Y 3rd 50 records = info from File A - Field Z. Is this possible? Should I repost in another area? Thank you.

I have many records in a table and wish to give them all a serial number, How can I select them all, give them all a sequential number,say starting with DN0001 then continue on as normal with the next number etc? Any ideas?

Hi, i'm using the "set field" function in scriptmaker and i was wondering if it's possible to change some parameters for the margin (in the text format, the "indent" attribute) in the script. I have a field wich contains some data, i want to add new data but i want it to be 1cm from the left. I'd like to do it without changing the margin for the text already there.... is it possible? What is the function i must use to do this? Am I clear about what i'm trying to do? Thanks

I have attached a scan of my script. Basically it takes stock from my tock table, based upon the qty of a order. The problem is, I have to press it twice for the script to work. Is it because of the halt script step? If so, any ideas on how to overcome? also flashing occurs with this script.I have tried freezing window etc

I want to enforce a field, IB_filename, only when another field, cat_calc, has a specific value, in this case: "Info Board". When I use this calc for field validation (following the correct naming convention) and cat_calc is not "Info Board", the enforcement is still applied-- any clues would be greatly appreciated:) If( cat_key; "Info Board"; Left ( IB_Filename ; 3 ) = "IB_" and Length (Filter (Middle (IB_Filename; 4 ; 99) ; "0123456789" )) = 3 and Length (IB_Filename) = 6)

Hello, I would like to create a layout that basically summarizes information from all of the records. For instance, when an individual inquires about the school we type in an "inquiry date" on their profile page. ie. Inquiry Date: 8/16/2005 Is it possible for me to get a summary of how many inquiries happened per month to conduct some sort of average inquiries per month calculation? Is it possible for me to make a calculation that is based on all records? I would like to extend this to multiple aspects of the database, ie. Inquiry Method is a Value List (Email, Mail, In Person, Phone) and I would like to summarize the average Inquiry Methods... …

I am experiencing some very annoying flashing when I press buttons. (when a script works). Basically, I feel that if the script has a few steps within it, say subscripts, the problem gets worse. Flash flash flash. Is there anyway to stop this happening? Is it caused by scripts or is there something I should put in my scripts to stop it? Its really annoying!

Hello Everyone, I have to create a project management database. Within this database, I have to create 3 complicated fields. First - I need to create a field that will have a drop box with color templates. For example, the drop box will have a blue square and next to it says ongoing project. Second - I need to create a field that is similar to a campaign fundraising bar that gets filled in as the capaign gets closer to the goal. This one would represent a project's progress. Lastly - I need to create a field that will only be seen by the users with full access. Are these fields feasible? And if so does anyone have an example? Thanks, Maria :smile2:

I have a field called ports that is a numeric field. Lets say I have 5 records and the ports field is filled in like this: Ports 24 21 234 1096 400 I would like to creat a calculation field that assigns 1 to the lowest number in the ports field, 2 to the next lowest etc. In other words, if my field were called Sort it would look like this Ports Sort 24 2 21 1 234 3 1096 5 400 4 I would like the sort field dynamic so when I add another record, lets say port 100, sort for that record becomes 3 and the rest of them follow suit. Is this even possible?

I have a field that represents a Pose Number. When I print I have it set to sort the records by this number. However, I get things like: 102 11 121 27 4 616 63 So I was thinking I would make an additional calculation field that took the number and added zeros in front and sort by this. I assume this is possible? How do I go about writing such a calculation?

Hi List, Honest...I have searched the archives but could not find an answer! I am in need of help in a calcualtion that correctly provides for elapsed time when the start/and end time cross midnight, displays results in "number" of minutes, and rounds to the closest minute. My fields are Start_Time (time field) End_Time (time field) Elapsed_Time (number field) I am currently using: Round((End_Time-Start_Time)/60;0) result as a number The cross over from morning to afternoon works OK,but I know this calculation is in correct when the start and end time time crosses midnight. Thanks in advance for any help. Steve

I am confused when defining a field whether to use a regular field with an auto-enter calculation OR a calculation field. The field does not need to be changed by the user directly. The field needs to be indexed for searches. I have found a way (via the forum) to auto-refresh an auto-enter calculation (using Left(GetAsText(modificationTime);0)). Are there situations when I should stay away from auto-enter? I have managed so far not really knowing but if you have opinions on this, please let me know. Thanks!

Hello, I've solved a problem here before, and it helped my learning, so here goes: I want to validate a field called "Title" so as to prevent duplicates of that field unless a particular value in another field called "Actions" is set to value 'Out' I have a vague sense of the syntax IF "Actions ≠ Out" AND "Title = !!" THEN "Duplicates are Not Allowed." I also want some way to prevent the creation of these duplicates. Any help would be much appreciated.

Hi all I know how calculate Age. But I can't calculate how many days, months and years a worker worked, given a startDate and an EndDate (may be I have a black hole in my mind) So if the worker worked: from 01/01/2001 to 01/01/2001 he had worked 1 day from 01/01/2001 to 31/12/2001 he had worked 1 year from 01/01/2001 to 01/01/2002 he had worked 1 year and 1 day and so on... Can someone give me an idea ?

Hi all: How to prevent entry in a field under certain conditions I have 2 fields: Status and Commissions. How can I prevent the user from entering the Commissions field if the Status field contains the code F. I used a validation rule for the Commissions field but users can still force data into it. I want same effect as if the field was formated not to be entered in Browse mode. Any suggestion will be highly appreciated.

I want to substitute for all the "" characters in a text string. I tried doing it like this: Substitute(text; ""; "~") That doesn't work because FileMaker sees """ as a literal quote mark and won't accept "". Anyone?

I'm working on a solution that tracks student scores on various tests across 6 years. I want to get the current gain for a given test and am having trouble with the calculation. Here's the scenario: The XYZ Reading Test is given in the Fall, Winter, and Spring of each grade, from K through 6th grade. I want the "XYZ Reading Gain" field to display the student's gain on that test at the present time. For example, if the student has been enrolled since Kindergarten and is now in 4th grade, I want the gain to show her most recent XYZ Reading score (Fall of 4th grade), minus her first XYZ Reading score (Fall of Kindergarten). I know the basic formula is XY…

Please, help me! I'm just starting working with FM. I would like to make an inventory of scientific articles. I need to give a unique number sequentially for every article of every subtopic of a certain topic. Examples: Topic 1/Subtopic 1/ Article number (1, 2, etc.) Topic 3/Subtopic 2/ Article number (1, 2, etc.) If you can help me, I would be very grateful. Many thanks, Gabriel

Newbie here: I have a db of newspaper delivery routes. One field is "Carrier" which consists of dozens of last names. Another field is "CopyNumber" which is the number of newspapers that get dropped off at each drop site. ie: Johnson drops 100 papers at convience store 1, 275 papers at store 2, etc. I need to be able to get the the sum of the field CopyNumber for all records for Johnson, all records for Smith, etc. Right now I can only get the final total for all carriers combined. I appreciate your input. Thanks, Kevin

I have a calculation field that has this calculation on it. (At0 + At1 + At2 + At3 + At4 + At5 + At6 + At7 + At8 + At9 + At10 + At11 + At12 + At13 + At14 + At15 + At16) / Length The problem is, is that I frequently get decimals with this function. Is there a way to add a round function to this? Every time I try FileMaker tells me that its looking for a field not a calculation. Any help you can give is much appreciated.

Does anyone know of a function that will count the lines of text in a field? I'm talking about lines of text that wrap without any hard returns(¶).

I have been struggling with this one for about 8 hours! Please help... I have a container field that is on a portal row. There is a field on the portal row that has a transaction date. These dates are not always the 1st of the month but the 1st of each month is when tuition is due. I think I need to round each date entry to the first of the month for the calculation for starters. The first line of the script below seems to work. Also I have named the date field "Month" I hope that is not a problem. What I need is a calculation that will show a global container field when today’s date is 10 days away from the end of the month. To better describe the sce…