Calculation Engine (Define Fields)

I am trying to build a processing system I have 20 fields, depending on the the condition, field 1 and 3 and 6 may get populated or 1 2 and 4 .. no set pattern... I need to be able to copy those ones to another setup of 20 fields. but in order without leaving any blank fields... I can write a bunch of scripts to do this, but it seems their has to be an easier way... field1 = t1 field2 = field3 = tg field4 = field5 = tj field6 field7 etc... the second set of 20 field field1.1 = t1 field2.1 = tg field3.1 = tj field4.1 = field5.1 = field6.1 = field7.1 = etc..

We have a customer name field that is coloured red. We also have a creation timestamp field. We would like to be able to have conditional formatting set up so that if creation is before 10/07/2011 it displays as red if after it is blue. The timestamps appear in our layout in this format: "21/02/2011 10:11:36" I therefore assumed something like Table::CreationTimestamp ≥ "10/07/2011 10:11:36" Would do the job. That though just makes alerts from on or before the 10th of each month blue. How should I be going about this? I am aware that the timestamp is effectively a converted number, is there some way to do it using this number? Or is ther…

Greetings! I am new to this list. I am trying to build a report that utilizes several calculations and I seem to be going around in circles with If and Case statements. This is for a student tracking database. One student ID number can have many scholarship records (they are in 2 different tables). Values are hard-coded for testing; they will be user-selectable in the final UI. I am trying to report on the current semester (fall 2011) and the next semester (spring 2012) individually and then reporting the sum of those 2 amounts. The reporting of the amount is also dependent of the status of the student. In this example, I want to see the amou…

How I can deal with the serialization issues when delivering an update to a data file? Let me define the process and point out the issue: The user receives automated update for his file - records are imported into new file, old one renamed, moved to backups, etc. He/She opens the new file, all records are there, good. Creates a new record - fails, because FileMaker seems to pick-up on record serialization (Number Field "Record ID" using Auto Enter of Serial Number) from the NEW FILE, and not where the imported records left off... Let's say there's 100 records which where imported, it still starts over at "1", "2", etc. instead of "101". Is there a good way this…

Hello, I am trying to get the average cost of a product within a Year period I have a Parent-Child ralationship, where the parent is Products and the child is Orders. The child table contains all the information about the date of purchase, the cost of the product when bought, and the a calculation of the YearPeriod. I am displaying all the product information on a layout of the Parent table and on a portal the relating data of the child table. Not sure how to calculate the average cost for every Year period. I have attached an example of the FileMaker database that explains and displays the issue better. Many Thanks Alberto Products.zip

I'm entering data from 18th century accounts, which are in pounds, shillings (20 to a pound), pence (12 to a shilling) and farthings (4 to a penny). I need to add and subtract. I've seen a nice gadget for arithmetic with latitude and longitude ("sexagesimal evaluator" by Raybaudi, in Articles, Tips Techniques ...) and wonder how it works, how I might adapt it. All ideas gratefully received. jdjd

Hi folks, I have a projects table with a __kp_ProjectID field. I need to create quotes that are related to a project, but we need to have the project ID number as part of the quotes number. Now, this quote number is one that the user would see and will NOT be the primary key field that is auto generated whenever a new record is created in the quote table. I know not to mess with that. I need a calculation or something that auto generates the quote ID that the user sees. For example, I have a project ID number of 10000. When the user creates a new quote for that particular project, I need the first quote to have the number 10000.01, the second one generated woul…

Hi ! I have 2 tables: Main table and personnel table. In the main table I have a "Name search" -field. When I start writing name to the search field I get a drop down list via relation to personnel file with auto-complete on (Main::LastName = Personnel::LastName). Only last name is possible to use for searching and selecting a person. Is it possible to define this relation or field so that I can use either first name or last name for searching ? Hope you understand what I mean...

Hi All, I have a field called ClassID that holds four numbers i.e. 1786 What I would like to do is break the numbers down into 4 seperate fields. i.e. field 1 (BuildingName) would hold whatever the first number is (in this case 1) field 2 (EntranceNumber)would hold whatever the first number is (in this case 7) field 3 (Floor)would hold whatever the first number is (in this case 8) field 4 (RoomNumber)would hold whatever the first number is (in this case 6) I will then use calculations for the results to show which building, which entrance, which floor and which room number. I have already done the last bit. I cannot figure out how to break…

hello to everyone, Here I attach a sample database. I want to design a report to shows Total_weight of each month. any help? Production.zip

Hey all, Ok so here is what I am trying to figure out. I have a layout with 7 fields in it. they are [machine name, model, serial#, specs, equiped with, price, and FOB] what I want is to gather the data from all 7 fields and place it into one large field for copy pasting into an email. but I need to insert the field names into the data also. for example say I have a record like this machine name: mill model: a1 serial#: 1234 specs: 9x32 table 2hp motor... may be anywhere from 1-15 lines of text in this equiped with:again could be several lines here Price: $1,000.00 FOB: somecity, USA I want to copy all…

I am currently running FileMaker 8.5 and I'm trying to restrict a certain field to only allow 9 characters. I work for a university and I'm currently using filemaker as a check-in for students and the problem is if a student has had more than one ID the scanner reads that extra number and we do not need it to do so because it will not properly let them check-in. Does anyone know how I can make this field only take the first nine characters?

Howdy, howdy... Okay, what am I doing wrong: I'm trying to set up a calc in a Cartesian join and just can't get the X of Y record calc to work. Here's what I have so far: Case( Get( RecordNumber ) = 0; ""; Get( RecordNumber ) & " of " & Get( FoundCount ) ) ...so with six records in the found set the calc shows "6 of 6" for all six records. D'oh! I know I'm going to kick myself when one of you wisened (or wise) guys tell me where I messed up. Good thing I'm not wearing pointed-toe boots. TIA for your help!

I need help writing a calculation. I have a table with these 4 fields in it. Field1-Empty_Boxes Field2-Empty_Box_Running_Total Field3-Empty_Box_Points Field4-Date Members receive 1 pt for every empty box brought in up to a max total of 10 boxes. If a member brings in less than 10 boxes they get 1 point for each box. (Note: They can bring in 1 box each day until they amass a total of 10) If a member brings in more than 10 boxes they get 0 points for those boxes. I still wish to count the total number of boxes brought in although they get no points for it. 1. I can't even get Field2 to work properly. Here's what I have: Empty_Box_Running…

I know that it is easy to note when a record was modified and who modified it, but I would like to do it at the field level. For example, I have a field called "NextRentBump" and I want to make a field "NextRentBumpMod" that would calculate the last date that the field "NextRentBump" was modified. I have a lot of fields I want to track, so creating related records for each field seems like a poor way to accomplish my goal. I have been experimenting with this custom function: Let ( trigger = fieldName ; Get ( CurrentDate ) ) But it will enter the current date in all "NextRentBumpMod" fields that are referencing a "NextRentBump" that has data …

I have been digging through help and cannot find the answer to this - When using the Get(SystemNICAddress) calc, what happens if the machine has TWO network cards, or one hardwire and one wireless? Suppose both are active? or the machine is a laptop and sometimes uses the hardwire, and sometimes the wireless? IS there a way to specifiy WHICH nic to get, or somehow get ALL nic addresses?

I am a bit stumped. In table A, each record represents one medication for one patient. Within the table, a patient can have many records (1 for each medication that the patient is on). There are also many patients in Table A. Table B is related to Table A by a patient identifier. I have a checkbox field in Table B populated by a value list of all the medications of interest. For a given patient in table B, I want every matched medication for that patient in Table A to autopopulate the checkbox field in Table B. So multiple medications in Table A can show up in one checkbox field in a record in Table B, even though in Table A these would represent individual record…

Hi, can anyone help me to format a URL from addresses in Filemaker to show multiple Google Maps points (will pay)?

Using FM10. Have a Purchase Order file, Line Items for it, and a Work Order File. PO file has portal from line items. Customer wants Purchase items to appear on Work Order. Problem is, his PO numbers and WO numbers are calculations involving unit numbers. I have a portal in WO, linked to PO line items, and if I manually type in the WO number in a relation field tied to line items, it works. I need to convert the calculation for WO to a text or number field, or find a better way.

All wise Filemaker Experts! I've added a container field to hold a .pdf report file associated with each record in an existing database. (these pdf files are about 2 to 15 pages long each with several thousand records in each database - so space is an issue) I originally wanted to embed the .pdf into the database so it will be part of the database. but, these 3 seem to be the only options I can find - which should I do? 1st. when I tried to import the .pdf file into the container field as a file it won't let me view it by double clicking without exporting it out again and saving it some where else before I can open it? or, 2nd. If I import each .pd…

Hi All, bit of a novice here. I have a table that defines the start and end dates for football seasons. In my table Matches, I want my 'Season_ID' field to automatically show the 'SeasonDescription' for which ever season it is when I insert the 'MatchDate'. Can anyone advise me how to do this? Don't imagine it can be that difficult but I cant find anything using google. Table: Seasons Season_ID SeasonDescription [iE: SEASON 2011/12] SeasonStartDate [iE: 01/JUN/2011] SeasonEndDate [iE: 30/MAY/2012] Table: Matches Match_ID MatchDate Season_ID

Hi there! I'm a staffing agency owner and I'm writing an email script that will confirm to my clients the last day of an employee's temp-to-hire term. A temp-to-hire (TTH) assignment is roughly 90 days from the employee's start date. My TTH end-date field currently calculates the start date + 90 (date_start + 90), but I'd like it to round down to the nearest Friday. So, if the calculation lands on a Sunday, I'd like it to round to the previous Friday. I could also use a calculation that rounds to the nearest weekday. Anyone? Thank you. My developer is on vacation. :(

Hi All, I'm trying to create a calculation that calculates matching records based on a date criteria and have been unable to do it. I successfully use the following calculation below to track current inventory levels: Field is "OnOrder"::Case(LineItem::_fkProductID = ProductStyles::_pkProduct;Last (LineItem::OpenTotal) * PartQtyNeeded;0) What I'd like to do is create a calculation that sees the inventory levels for one week and then two weeks prior. I thought I could simply add a second criteria to the calculation above to get the result. So for the week prior the calculation would look like: "OnOrderWeekPrior"::Case(LineItem::_fkProductID = ProductStyles::…

Hello, I am VERY new to FMP. This is the first real task I've ever performed with it. Any help would be greatly appreciated. I need to take two separate fields and combine them. First, I need to take a field called CLIP NAME with this format: X59B-1 MOS And change it to: X59B_01 I only need to add the zero if the number in the clip name is less than 10. In other words, I need to make sure it is always a two digit number. Then I need to take a field called Roll Camera with format: B049 and pull just the B out of it and append it to the end of the string I need and then, finally, I need to add ".mov" to the end. So, for …

Hi All, I know you shouldn't be using repeating fields for data since it complicates things but I'm guessing it's okay to use them in user interface aids. What I have right now is a layout that somewhat resembles a webpage. As you can see in the top half of the attachment, there are 12 product images numbered 1-12. The upper right of the image shows that you are seeing page 1 out of 26. Originally, the client was happy to have these arbitrary 1-12 numbers as visual aids for a customer to call out "I like #3 and #9" and then those products could be added to their 'like list' that you see on the right. After reviewing the first page, you can go to the next pa…

Does anyone know of a way to calculate the attribute of active field position (Top, right), without using objects name or having to do different objects name for each field. Thanks Caps

I have a textfield containing really weired formated text, like: [T] Hello I am a header This is a [ci]paragraph[/c] with some [cb]fomated[/c] words! I am list item I am list item to. [TS] A smaller header And here there is some more text. That I need to get into a HTML accepable format like: <H3>Hello I am a header</H3> <p>This is a <i>paragraph</i> with some <b>fomated</b> words!</p> <ul><li>I am list item</li> <li>I am list item to.</li></ul> <h4>A smaller header</h4> <p>And here there is some more text.</p> My idea was t…

I have looked around and seen plenty of solutions for filtering text lists but what I am trying to do is basically very simple but I dont seem to find it Field A contins multiple numeric values per record . What I want to do is find a calculation that I can use to filter these values in each record to be within a specific range of numbers and discard any that fall outside the range For example Field A contains values of 187561, 188221, 196774, 215545, 216887, 223349 and I want to filter them to values that are >190582 and <217349 so the result would be a reduced value list of 196774, 215545, 216887 Its easy enough to do in a portal with filterin…

I am using the free business solution Big Mountain Bike. I have changed many of the company info, but I am stuck at the invoicing layout. Is there a way to add different NOTES on the invoice based on what customer gets invoiced? Any help is very much appreciated.

Short version: Where EXACTLY does Filemaker pull the system temporary path from in Windows 7? A preference file stashed away somewhere? Registry entry? Problem: SOME users cannot save .PDF files to disk either in a script or manually. We get the error "... could not be created on this disk. use a different name, make more room on the disk, unlock it or use a different disk." It appears that even if you're not saving a .PDF in a script (File -> Save/Send Records As -> PDF) it still uses the temporary path used in get(temporarypath) to save the file to first, that's all I can guess. For the users that it doesn't work, their path is resolving to /C:…

OK, so here's the mandate from the client for invoices for inpatient and outpatient accounts for outpatients if the admit date is between 7/1/2010 and 6/30/2011, (amount recovered-.07*amount recovered)*contingency percent. Otherwise it's just straight up Amount Recovered*percentage for outpatients. For inpatients, it's a bit more complicated... For admit dates between 7/1/2010 and 6/30/2011, it's (Amount recovered- .52*amount recovered)*contingenty percent. BUT, if the admit date is before 7/1/2011 and discharge date is after 6/30/2011 it's (Amount recovered- .26*amount recovered)*contingenty percent. Otherwise it's Amount Recovered*contingency percent f…

Good Morning, I'm trying to develop a Filemaker solution and have reached a bit of a road block and I'm hoping I can get some help from this great community. Ihave a filemaker database that is taking information on a film set. It records information from the camera. Now that most cameras are digital I need to associate file names to the records that I am creating. Since I can't access the files until after the shoot, also the camera generates random characters to prevent over writing of files I need to do this as a multi-step process. Here is the process I have in mind. The film shoot is broken into days. Each day has a hard drive associated with it. …

Hey guys, I'm super new to FMP. The studio I work at has me creating labels for a bunch of tapes. Anyway, the current way I'm inputting new media into FMP is by creating media, entering information in the pertinent fields then using "Dupe Media" to create new media. My problem is that when I have to create multiple entries for a particular range (i.e ABC001 - ABC500) I can't seem to find a method other than using "Dupe Media" and entering the next number in the range then repeating that until I've completed a set. Is there a way to have FMP create/duplicate new entries for a range instead of having to enter each one manually? Thanks in advance, 00000000 …

Hi everyone. I am in the process of creating a file for all of our customer contracts and I need a field that will calculate what we call the "Action Date". The action date is supposed to be 3 months before the contract expires. It sounds simple enough but I am having a complete brain malfunction. Basically what I need the field to do is automatically calculate what this date should be based on my "Contract Expiration Date" field. So if the contract expires on 12/31/11 I want the Action Date field to automatically be filled by the date 9/31/11. I did have a calculation that did this but I accidentally deleted it. The answer I was given on another forum was Month (Contract…

I'm assisting a local bicycle organization with a database that cyclists use to track their total mileage. Im having some troubles summarizing the mileage. Everyday, the rider enters information into 2 key fields: Miles = total number of miles for the day RideType = type of ride they did (Commute, Trail Riding, Off Road, etc...) I would like to have the ability to break down the total miles of each ride type. Currently i have one summary field that gives total miles no matter what type it is but would like a field for: TotalCommuteMiles TotalTrailMiles and so on.... Would i set the field as a calculation and perform an IF statement b…

Hello Guys, I have a problem with setting the summary report. I attach a sample file which contains a report using summary field. The problem is I can't multiply a summary field's value to another field's value and get a correct result. Any help would be appreciated in advance. Kind regards Product.fp7.zip

Okay, I'm stuck. I want to prevent users from inserting formatted text (boldface, italics, color, etc.) into my solution so I thought of using the calculation, TextFormatRemove(Self), on all the user-available fields using the AutoEnter Calculation function...but to do that for all the fields--100+ of them--seems like a klunky way of doing things. Is there a more elegant solution to accomplish the same goal? As always, TIA for your help! Rich

Hey all, I'm working on my second database and coming off a filemaker bender of twenty five consecutive 15-hour days. My mind is fried and I'm missing a very simple concept that's causing this problem, I'm sure. I have learned an important word this month, though: featurecreep. Based on the attached picture, I'm hoping someone can guide me through the correct way to set up a quote based on a quantity of products a customer has chosen. My trial and error approach to fix this failed. I'm going to try to sleep and hope not to see the relationship graph in my dreams...again. If anyone can help, they might just save my sanity.

I have a report that calculates the number of units per hour by dept. The report is sorted by dept. I have a summary field for each dept that calculates total hours worked in the dept. QTY is the number of units that will be divided. It does not change (not a global field however) The calculation is simple: QTY divided by that summary field. Problem is: Calculation will always put the result of QTY/ total hours worked by all dept together and show the same number everywhere. The result only makes sense for report total.

Hello I would like to see if there is a way to get rid of the scrolling tools in the webviewer window. Is there any calc to do this. I am pulling in pictures to the webviewer and I would like to ee just the picture and not the scrollbar. Thanks for any answers here.

To simplify my question: Is there any way to do this summary faster?? It takes about 4 hours on a win 7 machine in FMP 11 ADV to do 10,000 records. I have a field I'll call Widget. I have a summary field WidgetCount (count Widgets). I have a calculation field WidgetCalc with the formula: (getsummary(widgetcount; widget)/widgetcount) which gives me a percentage of each widget to the total number of widgets. I was asked to give a report with it sorted by most popular widget. The full report, I gave up on waiting for it to do the sort on WidgetCalc for over 300,000 records. I just turned it in sorted by widget and let them suss it out. I narrowed …

I have radio buttons in my database, one of which being SOLD. Every now and then I do a bulk delete to clear old records or duplicates. But my Fear is that I may accidentally delete a SOLD record in a purge. Is there anyway to set a permission to where anything marked SOLD can't be deleted in a bulk found records? I try to remember to OMIT SOLD in searches, but I would like to fool proof this!

I know this is probably a really simple question but...How do I remove a decimal from a number. I am exporting numerical data to an old legacy system and need to remove the decimal point from the number. Any help on this would be great. TIA Bob Moran

I have to do updates once a week on a company program. My question is I am trying to track what to update from my Filemaker Database. For example if i created a file on 4/1/11 and never modified it, there would be no need to update it. However if I created a file on 4/1/11 and then modified the file on 4/2/11 (or whenever) it would need to be updated in my company program. So how do i perform a search between the two fields "Date Created" and "Date Modified" yielding only results modified AFTER creation Date and time. I add records daily so a general modified search yields unmodified records. Please Help

Hi, How to overcome the problem “The calculation cannot be store or indexed because if reference a related field, a summary field, an unstored calculation field ..”? I created an application, where the frontend user (kiosk mode) can choose which records he can see (config). He will be able to choose the data (begin and end), and if the record have yes in one field. I have done this with a script (goto). In order to do statistics i created another field (x_select) to perform stats only related with special criteria, showed in config layout (these fields are global). It isn't necessary to have multiple fields in the records to do statistics, because most users…

I am programming a billing DB in FM 9 Adv. I now bill 30 different counties and to make a long story short, I have a billing layout for each. Naturally, each county dictates the billing form they will accept so I can't use just one... but I digress, there are simply 30 different billing forms and each client is associated from one county. The data entry layout has the billing info: the client, hours, date, etc. and WHICH county to bill, and the 30 are on a drop down list. I would like to have a function that will take me from the data entry screen to the corresponding billing layout for the given county. I suppose I could put 30 tiny buttons on the data entry …

I am using FM 10 on a Mac OS. I inherited a database that has been very useful to us. So useful that the powers that be want to use it for another one of our entities. There are 2 headers (one on the top left and one on the top right) throughout this database. On the left is the name of the database and on the right is the name of our organization. The person that designed this database has a layout which contains all of the images that are used throughout the database. The headers are also on this layout. But this is what is confusing me....the top left header is a container field; the top right is listed as a text field. There are at least 50-60 layou…

Hi all I am trying to create a function similar to Proper but the difference being if a space is followed by an apostrophe then a character, I do not wish the character to be changed to uppercase. I am trying to avoid recursion but everything I have tried has a 'knock-on effect' when the apostrophe is used in a different way. Any ideas? Thanks in advance Lee

hey folks, quick question, I'm working on two databases, on Database B, my field "SS" grabs info from Database A, I need both fields to have no access, which is easy enough, but I want to be able to create new records in Database A with functionality to add information in the "SS" field. When I create a new record I still don't have access to that field, I'd like to enter content and then have it go to no access. Any ideas? thanks in advance hank

Hi, Can anyone help with a date calculation please. I have an auto entered date field on my database showing the creation date of the record,. I would like to have another date field showing the (creation date of the record + 30 days) but can't figure what calculation to specify. If anyone can advise I would be most grateful. Many Thanks

I need to extract the ZIP code from an address. I'm having no luck figuring this out on my own. The addresses are all contained in one field, and sometimes they contain 3 lines, sometimes 4. For example: Absolute Transcription 87 Village Lane Canton, CT 06019 or J&J Court Transcribers, Inc. 268 Evergreen Avenue P.O. Box 3211 Hamilton, NJ 08619 I need the ZIP codes so I can make a list of addresses according to area. Thanks for any help you can provide. Kevin

I'm stuck. I am trying to parse part of a field into another field via calculation. I used Position() which gave me the position of first blank which is correct, but now I want to place everything after that into my second field. First field "Product_CodeDesc" contains - "MCD01234 some description" The first 7 chars will never change in length. Second field is my calc field that should hold the description part. What function do I put the Position() statement in to retrieve the description section. Thanks Michael

hi all i´m an autodidact, and i am really new to filemaker, so i really do not have any background related to databases. my profession is architecture. never the less i ´m trying to develop a database to calculate "estimated costs" for a building. i just want to tell something about the thing i´m working on. the basic structure of the database: [Position] -working category (concrete work) [under-Position] -create concrete wall, thickness 15cm -create concrete wall, thickness 20cm -create concrete wall C25 thickness 20cm -create concrete wall C35, waterproof, thickness 25cm and so on for all working categories for th…

This should be easy, and maybe it's because they are timestamps, but all I want to do is have a field that shows the number of days since a record has been modified... Can someone shed some light on this? Thanks!

The following 3-step sequence is from a script that had worked for months. It simply populates a repeating field that holds working parameters. But this week I was blindsided by a change in its behaviour that took me hours to isolate and rectify. Even though I've restored the script’s operation, I can find no clear (i.e., documented) explanation of the root cause. Set Field [ Table::Param ; Base::FieldA ] Set Field [ Table::Param[2] ; Base::FieldB ] Set Field [ Table::Param[3] ; Base::FieldC ] But then I changed Init::FieldC to be a repeating field of 3 occurences (a push-down stack, actually -- used to hold prior values). A major runtime process started…

I am a newbie to filemaker pro 9 and have been thrown into making some changes to our existing database and would so appreciate any help before my hair turns any more grey. The existing database is used for issuing building permits for one location with each permit having its own reference number by using (automatic enter/serial number). For example 2011-01. We will be issuing permits for another entity that needs its own reference numbering system, but need to keep these two entities tied together. Is it better to have one database or two separate? If its one database how do make two numbering systems? If its two databases how do I tie them together.

I thought I was smart. I am using a virtual table and I wrote $$dummy[iD] and there were 3,000 records. Now if I run it and want only 100 records, the remaining 2,900 variables stay there and it breaks. I tried clearing them all but I can't figure. Please tell me I don't have to write a script to clear 2,900 different variables. Nope, I tried Case ( not IsEmpty ( FilterValues ( Get ( CalculationRepetitionNumber ) ; $$dummy ) ) ; Extend ( "" ) ) but Extend will only take a field. I'm done for.

I am attempting to refine how my project calculates a report price. In the past I was just using a Case statement with a series of possible "matches" and then letting the end user deal with entering a custom price if there wasn't a match. However, there are too MANY unmatched so when we need to change pricing we have to change too many strings . . . so we want to use a calc to just add all the parts together. I was hoping to combine a Let Function and a Case Calc to do this and the early parts worked but I've hit a wall in trying to get the parts to add as well as how to handle more than one instance of each variable. What I have so far is this: Let ( [ T = 10 ; …

I know the @@ properly designates the right portal row number whenever there is content in the row. Question, how do I take those @@ numbers and put themt into a field in order to put it into a script. The @@ does exactly what I need to do the drill. Any help on this would be great. TIA remoran

EDIT: Figured this out. So simple. Rather than use a field calculation in the table, I just calc it in the same script that enters the ClockOut timestamp. In a timeclock program, a timestamp is entered into a ClockIn field first, then when the employee leaves a timestamp is entered into a ClockOut field. A calculation field shows how many hours between the two. But it gives an error number (-176221) when only the clockIn field is filled. How can I suppress that (for aesthetics mostly) until the 2nd field is filled? thanks

Hi All, I have two related tables with various subsummary parts and I am trying to get a calculation that combines the summary fields across the two tables. In theLine_Items table I have a Period_Vat summary field which is defined as : (GetSummary ( VAT_Sum; D_Month )-GetSummary ( Out_VAT_Sum ; D_Month ))-GetSummary ( OrderLines::Period_VAT ; OrderLines::Month) The period report script performs a find in the Order_Lines table and sorts the records by month and then performs the same find in the Line_Items table and sorts by D_Month but the calculation is not bringing in the summary from the OrderLines table. Any ideas please

I need to make a report with a count of how many unique companies are in each summary field. Here is a simple example. Any ideas as to how I can get FM to do this? Sales Product 1 Number of Companies: 2 company a person 1 company a person 2 company b person 3 Sales Product 2 Number of Companies: 1 company a person 1 company a person 4

Sorry I am sure this is here somewhere but I have not been able to find a post.. trying to do conditional formatting: stuck on the calculation to highlight if the date is GREATER than 80 days but LESS than 90 days I want two conditional formats. 1. If it's more than 90 days since this field's date and (FIELD B ) is blank, highlight in RED. simple enough. (Get(CurrentDate) - GetAsDate(Self)) > 90 and FIELD_B = "" 2. If it's equal or greater than 80 days but less than 90 days since this field's date, and (FIELD B ) is blank, highlight in BLUE. here is where I'm stuck. thank you!

Hi everyone, I have a form that holds 5 fields: Activity, Hazards, Class Risk, Control Measure, Class Risk2. There will be 5 to 10 lines of each. Each field sits next to the other and is the same height (approx 250 px high) much the same as if they were in a portal. I have included a snapshot of this in browse and print preview. What I am trying to acheive is making the finished height of each field the same as the Control Measures field as this will hold the most information, thus the most characters. I thought I would acheive it thusly.... Count the text characters in field ContolMeasures, count the text characters in Activity, Calcu…

I am try to simply get the path of a file that is stored in a container field as a reference only. However I would only like to receive the path, not the filename and filemac. The example below is acquired as GetAsText. I would like to see everything after filemac in the example below. Seems like there should be a Get(ContainerPath) option? file:stories_02.mp4 filemac:/ZoeSnow/Users/david/Documents/susan_reeltoreel/stories_02.mp4

I have tried various way to try and get this to work, but with no luck, so any help would be greatly appreciated What I am trying to do is automate pricing options in the table 'Products' in connection with each Company using a price code when filling in the Invoice/Purchase Orders. So every time a company is set up in the table 'Customer Detail' the operator has the choice of giving each company a code which refers to different price options, or left blank for RRP. When opening a new Purchase Order / Invoice you have to fill in the Company Details from a draw down list which includes the 'Price Code' - A,B,C,D or blank. From there on any items added via the portal shou…

I am trying to use virtual method but regardless it is repetition calculations which hangs me up. I have a monthly chart of repetitions field so it can show the lines down the body in list view. I have array $$array with numbers (first value is ID1 etc) and I understand how to use calculation with GetValue($$array;ID) but it won’t work for this need. file attached with sample. The numbers in the array mean that I want to put an ‘A’ in the repetition of that number. It is only a visual indicator. And yes, this is simple example and there is a lot more to the report but truly this is enough for you all to understand what I need I think. Is it possible to do this? …

I am trying to define a field that would give me sequential line numbers in a summary report. A little lost. Using FM Advanced v10 Mac/Win Thank you for any suggestions Anthony Summary Line Numbers.fp7.zip

Here is what I need to do. Customers are going to be submitting documents that we are going to store in a FMP database. What we need to know at a glance is, if the client has submitted all the requested documents. We are planning to place said documents in separate container fields. Now before anyone asks, the database will not be outward facing so that internal users will have to load documents into the FMP database. What I'm wondering is: Is there a way to determine via calculation if a container field has something in it or not? Also is there a better way to do this? Thanks. Paul Update: I found that the IsEmpty() Function/Calculation will work…

Show total record(s) / found record(s) / position of record in the layout This is possible with the tool bar, but i dont know how to show this at layout screen Please share the solution from anyone having this experience before. please refer the sample image

OK, I know that the better solution would be to have the data entered without a dollar sign and then use formatting... But, I've a client that insists on entering prices with dollar signs. So when I need to do logic using the data from that field the price value ($10.00) is seen as a variable and that breaks the logic when part of the sequence is to check for empty fields. Is there a way to "escape" out the dollar sign. I've tried the GetAsText function but that doesn't do it. Thanks

Greetings; I am trying to add a calculation to the database that will automatically locate the lingerie that has a plus size. Now to give an example: Normal Size Wholesaler code might be: 5032 Plus Size Wholesaler Code might be: 5032X I also have a field "Plus size Model number" that will set the field to "5032X" on the Normal Lingerie. What i would like it to do, is normal lingerie record Compare "Plus Size Model Number" to records Wholesaler ID and see if that model number is located in there. So it would search Wholesaler ID to see if there is a record 5032X if so "Yes" otherwise "No". Any Ideas ?

Helpful People, I am looking for a way to do a calculation to compare a portal row date field with the previous portal rows date field. Each portal row has an expiration date, I want to do a calculation to make sure that the current row does not expire before the previous row does. The records in the row are not necessarily entered sequentially so their record ID's are not in order. Any thoughts? Peter Lehrack

Hi everyone, I have a report based on job data, but sometimes we have more than 1 invoice for a particular job. Is there a calculation I could use that would add the invoice numbers together with a comma and a space between, knowing that the information is coming from seperate rows in a portal? Any help would be appreciated. Milton.

I have created a simple cover sheet for faxes. I would like to automatically calculate how many pages the fax will be and insert this calculation onto the fax cover sheet. Is there any easy way around this other than manually going into print preview and scrolling through the pages until you find the page numbers and then manually entering this value? Thanks in advance Milton.

I am using the get as text calc field to convert a date filed to text. I can do the order part without a problem but don't know how to put the leading character 0 in for single digit months and days. The date and date as text order is CCYYMMDD but the get as text returns, for example, 201144 while the custom set up for date does 20110404, the format I need to dump into a legacy system. Any help would be great. Bob Moran

I'm obviously brain dead today (and the past week). This has got to be a very simple calculation, but I just can't see it at the moment. On each record I have: a field "OnTime" that displays either "Yes" or "No" On the summary in preview after sorting by customer: a summary field that counts the number of "OnTime" fields and displays in the customer summary as "Flights" Here is my problem: I want a field that simply calculates the number of "Flights" that were "OnTime" and display as a percentage. Answer=On Time Flights/Total Number of Flights. If I had 9 flights total and 4 were ontime, it would be x=4/9 or x=.44 I'm trying to w…

My requirement is to change the file path character (from forward slash to backward slash)... but below function could not support. Pleas suggest me to achieve my objective Substitute ($path ; "/"; "\")

Hi, Here's the premise: I want to generate a report of projects for a specific date range. I want to be able to count the number of unique professors who had projects during that date range. The total number of projects will be greater than the total number of unique professors, ie, there will be mutiple projects to one professor. Currently, I import the data into an empty table and use the following calculation that I got from the FM site to tag records as having a unique professor or not. I created a clone of the table and self join relationship as instructed on the FM site http://help.filemaker.com/app/answers/detail/a_id/3423/~/counting-the-number-o…

Hi everyone. I am new to posting so hopefully I will be able to get my issue accross effectively. I have a portal that gives me a group of all open invoices. One of the fields in the portal is a follow up date (NextCall). I have two global fields in the header. One is a start date and one is an end date (GlobalStartDate and GlobalEndDate). My calculation for the filter is NextCall >= GlobalStartDate AND NextCall <= GlobalEndDate. This works fine when I switch from layout view to browse view however when I change the global dates, the portal records won't refresh. But if I go to layout mode then back to browse mode, the records are refreshed. I hav…

Hi everyone, I'm somewhat new to FMP and was hoping I could get some help with my problem. So: I have a field called "Products". Users can choose a product from a drop down value list. The value list of the Products are: Pamphlets Kiosk Poster Document Banner So, in my report, I'd like to be able to have something to show the amount of each product that was chosen. For example, if I had 27 records, it would look something like this; Pamphlets: 5 Kiosk: 10 Poster: 2 Document: 4 Banner: 6 I'm not sure the best way to go about doing this... Any help would be really appreciated! Thanks Chris

I've created a simple database to inventory our home belongings for our move. The fields are Item, Description, and Value. I'd like to create a calculated field for the "Total Value", adding together the individual values of all the records. How do I do that?

I've been modifying John Mark Osborne's invoicing solution found at vtc.com I run a swimming pool company and what I want to do is modify the price of a swimming pool opening based on the total square footage of the pool in the customers record. I've tried making "swimming pool opening" as regular product with a base price and then made another field called "price_modifier" with a case statement that said if the product seleted was for the pool opening and if the pool was a certain size to return a number into the price modifier field. so for example if a pool was 500 square feet then it would return "10" into the price modifier field and then on the invoice …

Hi All, Very new to Filemaker and IF statements. I need to print invoices based on the contents of a Drop Down field. I have a Drop Down that gives the user the ability to Choose Yes or No. If the user has paid then print the invoice if NO then don't. I could really use some help on this. Here is what I have: Go to Layout["Invoice"(Buyers)] IF [(Buyers:B_ccInVault = "Yes")] Print [] Else Go to Layout ["Buyers Menu"(Buyers)] End If Thank you in advance for anyone that can help. MG

Hi all yet again. I have a requirement to differentiate the VAT elements of a product based on whether the stock was purchased for retail or is held as consignment stock. At the moment I can think of no way of doing this apart from giving the consignment stock and retail stock different part numbers which would double the part numbers and introduce a large chance of input error by the operator selecting the wrong part number for the same product. When a purchase order is produced it is either for consignment or for retail, if it is for retail the VAT is due in the period in which it is purchased, if it is for consignment stock, the VAT only becomes due in the period in …

I think I found an answer posted by Mr. Lee Smith to my question below. I took his example and had to rework it a little but I think it is doing what I need. The end result I came up with is ( Month(Get(CurrentDate)) & "/" & 1 & "/" & Year(Get(CurrentDate))) Will this search for the first day of the current month of the current year? Thanks. B.H. How do I format a calculation which will search for the first day of the current month and the current year. I have an attendance card for all of our scouts and they have a start date field, which of course is the first day of the current month and the current year. …

Here's the scenario. I have an item database that has the tables ITEM and POTENTIALITEMS. There is a button on the POTENTIALITEMS layout that will turn a potential item into an item. This isn't so much a problem as it is an annoyance. The fields from POTENTIALITEMS need to be copies to ITEMS, but the layouts are not identical. This prevents me from using the "Go to Next Field" script step. Also, I'd like to avoid having a column of 40 - 50 "Set Field" steps on there. What I really want to do is have a simple variable in my script with a Carriage-return seperated list of the fields that need to be filled, in order (e.g. itemnum¶description¶upc¶weight, etc...…

Hello, Running balances... I've looked at several good threads on the subject, and also downloaded a couple of good example files. However, I'm having a difficult time wrapping my head around how to apply those techniques to my particular solution. Goal: Show a running balance inside of a portal. Setup, as simple as possible: -I've got an Invoice table and a layout based upon it. -On that layout, I've got a portal, which shows records from its child table called Payments. The relationship is InvoiceID = InvoiceID between the Invoice and Payments tables. -The Payments table has the following fields: Date, Amount, PaymentID, InvoiceID. That works great fo…

Hello, I am trying to figure out how to display what seems to be something simple. I have a database that tracks the hours that volunteers work for our organization, and I'm trying to make a calculation field that displays the total amount of hours that a volunteer has worked in that particular year. I can get it to sum up the hours for each individual volunteer easily enough, but I have no idea how to only sum up the hours for one particular year. Can someone kindly help point me in the right direction? Thanks

mods if this is in the wrong forum please feel free to move. working on a monthly attendance tracking solution and i was thingking the easiet way might be to make 31 feilds for everyday of the month and just enetering a 0 or 1 to mark attaendace. this would need a related database so it can sum up all the days attended in a total. plus it would have to do a sum for all the students for every classroom for a total for each class room. i already have 1 database for the necessary student info. plan on making a portal so i can track each students monthly attendance totals in a generated report. plus also have a global attendance report for all classrooms. …

Hello everyone, I've got a problem for which I've not been able to find any answer. I'd like to take the output from TableNames(Get(FileName)) and exclude any table names that have a particular keyword at the beginning of their name (e.g., "Hidden"). Then, I'd like to take the output from FieldNames(Get([the table name that a user selected from TableNames(Get(FileName)), passed to a variable]) and only display the fields that have a particular prefix (e.g., "Show"). I can figure out how, using Substitute, to search for and erase the TableName keywords themselves, but I can't figure out whether there's a way to have FileMaker search for the keyword and delete…

I have an application where I calculate the difference between 2 dates using the calculations:- Today = Get ( CurrentDate ) Round ((Today - Date_Joined)/365 ; 1 ) and the resulting figure is 1 year out, ie it will tell me 2 years instead of 3 I know that this worked initially but for some reason that I cannot fathom out it has decided to drop a year. I would be very grateful if some kind person can shed some light on this as I have checked date formats and everything and drawn a blank Many thanks in anticipation Alan

Hello all. I have been trying to find something similar for the past couple of hours and have come up empty handed.. Here is what I am trying to do: I am working with 1 table 2 layouts. say the record date is 4/11/2011 - field is "TomoWakeUpCall" (text field) entered as 4:30 am on 4/12/2011 I would like the feild "WakeUpCall" to auto enter 4:30 am (and viceversa would be excellent) - if I entered it on the 12th instead of the 11th. I do have a calc field - DayAdd1(date+1) that works but can't put my finger on how to do the same with the WakeUpCall field(s) I'm basically trying to save myself a few key strokes and page flips. I do have a…

Hey guys trying to make a multi purpose hybrid field that changes based on the layout. ( There is 5 different fields that are spread over 10 layouts which would bring 50 calc fields to 5 ) So layout name would dictate what field to show up... Weird thing is that a simple if statement is working perfectly... But there is ten different layouts. When I try n put it in a case it doesn't work. I've even tried simple math logic Sample Case( If (Get ( LayoutName ) = "AprilInvoice" ; 1 ; 0 )=1 ;april text; If (Get ( LayoutName ) = "MayInvoice" ; 1 ; 0 ) = 1;may text; ) Maybe I'm overlooking something simple, I've been working at this f…

I'm trying to use text from a second related field to define the calculation that determines if the data entered into the first field is valid. For some reason I can't get the text to correctly evaluate as an actual calculation. It just seems to show up as text and not evaluate as a calculation....I know this is super simple, but I'm totally stumped at the moment and I'm hoping to figure this out ASAP. Does anyone have any suggestions on how to get this to work? I've tried using evaluate() but I think I'm setting the syntax wrong. I currently have the "validate by calculation" box checked and the calculation looks like this:If(Evaluate(Substitute ( YY…

Ok, this might be straightforward, I have been beating my head against my desk for a couple hours now... Here's the situation: I have a database which I am making to house our company's systems information. In this database is a text field called "IP" this field contains an IP address for each system in the format 1.2.3.4. (Imported, that's why it's text) I then have 4 other calculation fields (result number) that are meant to separate these so they can be searched / exported. (IP1, IP2, IP3, IP4) Here is my problem, the IP varies in size, for example, each octet between the "."'s could be anywhere from 1 to 3 numbers. (111.222.333.444 or 11.222.33.444 an…

Hi All, Perhaps I am missing something. I have a Formula that was working fine and now the result is "?", except when the 1st If statement is true. I cannot see anything that could have changed in the data schema that would effect this calculation. Let ( [ Cost = ITM_Item::Cost ; Fr = "Cost ≥ g_SlideScaleFrom_" ; To = "Cost ≤ g_SlideScaleTo_" ; result = "Cost / (1- g_SlideScale%_" ; % = Cost / ( 1- ITM_Item::gMarkUp ) ] ; Round ( If ( not IsEmpty ( ITM_Item::MarkUp_@_RecordLevel ) ; Cost / ( 1 - ITM_Item::MarkUp_@_RecordLevel ) ; …

I kind of can't believe I don't know this / have never had to do this before, but there you go. What syntax do I use in a Case calculation to indicate that I want a positive return if it contains (but is not limited to) my specified block of text? It's not " = ". In other words, I want to write a Case calc that, in my brain, functions like this: Case ( PeriodicalName CONTAINS "New York"; "Yes"; "No") and returns The New Yorker - Yes The New York Times - Yes Reader's Digest - No Time Out New York - Yes New Breeds of Yorkshire Terriers - No York News - No The New York Post - Yes Rolling Stone - No What structural element stands…

I have 3 records that each contain a field I will call "Filter". All of the child records have the same parent record that contains a field I will call "Calc". I want to be able to return a result so I can filter the child records given the following: Parent Record (Calc = 1) Record A Filter = 1 Record B Filter = 1, 2 Record C Filter = 2 Expected Result = Record A filtered Parent Record (Calc = 2) Record A Filter = 1 Record B Filter = 1, 2 Record C Filter = 2 Expected Result = Record B filtered Parent Record (Calc = 1, 2) Record A Filter = 1 Record B Filter = 1, 2 Record C Filter = 2 Expected Result = Al…

My situation is: I have blank form, and I need to put each letter from Last Name or other to each cell in a blank form. So, FM11 doesn't have text spacing. How I can do this? Maybe I need to make another fields with calculation and paste spaces between letters? If it's the best way, please say how to this calculation. And another way that I think is make fields for each cell and make calculation for these fields equally each letter of LastName. So and I have dates that i need to put it over cels like -date dd month mm year yyyy. How make it? I really need it. If you have another advices I'll be glad see it. Please, help. P.S. I put blank form, i…