Ray-El

Thank you for the tip. Very nice sample. I'll have to dissect it and see if I can understand what's driving it.

I'm trying to come up with a way to allow users to create a list of cascading categories, which will eventually be used to help customers narrow down products on a website. The first two categories are entered for them in two fields in the same table. So they would see the following listed: Record 1: Garage Tools Record 2: Kitchen Silverware When they click on the record 1 ( Garage - Tools), a portal shows them all the subcategories for that record and allows them to add more as needed, such as: Hammers Drills Saws Crow Bars I added a second portal so that when they click on any of the subcategory records in the first portal, the second will show them only the records from a third table related to the ID of the record selected in the second portal. So you click on "Garage - Tools" and see "Hammers" in a portal and can then click on the "Hammers" record in the portal and you see types of hammers listed in a second portal. For the most part, it's working, except for the second portal. No matter what record I click in the first portal, the second portal will only show the records related to the first record listed in the first portal. So, using the example above, you click on "Hammers" and see the types of hammers listed on the second portal. But if you click on "Drills," you still only see the records related to hammers in the second portal. Is there a way to get a second portal to display only records related to the ACTIVE record of the first portal? I plan to add a 3rd portal, too. Thanks for any suggestions.

I'm about to perform a major overhaul on an existing database that is used to track customer orders. They want to add a billing feature that automatically calculates sales tax appropriate for each state/zip code on an item-by-item basis, since some of the line items being sold are taxable and some are not. So the person entering the job will have to determine the taxability of the item and then I can set up the database to apply the applicable sales tax. Or maybe I will set up a table of items with predefined tax statuses. Haven't decided yet. But before I go creating this myself, I'm wondering if anyone knows of an existing plug-in, or something, that would make calculating and updating state sales tax data easier. If not, I'm thinking the best way to approach this would definitely be to create a separate table for the sales tax data and set up a relationship between that and a customer info table by state or zip code. Then whenever a line item is entered, a field can be defined to use an IF statement to calculate whether the line item is taxable or not, and if it is, then it can pull a percentage from the tax table by state or zip and multiply it by whatever subtotal cost applies to that particular line item. One issue I already foresee is that the current database is set up so the customer's city, state and zip code info are all in the same field as the street address. It's entered as one long line of data. I'm going to try to link the database to an SQL server to pull in all the customer address data into separate fields, which I think will make the relationship part of this job work better. It sounds very easy in theory. Almost too easy! Has anybody ever done something like this before who can offer some suggestions before I dive in head first? Thanks for any tips you can offer!

You don't need to buy a full PC version, you just need an .exe installer. I would contact FileMaker and see if they will send you a link to download the PC installer. I needed both the Mac and PC installer to create Runtimes for both. Since I have FileMaker Maintenance, they send me a single e-mail with a link for both installers. Your installation code for the Mac also works for the PC installation.

I'm not sure if this is possible, but I was wondering if I can keep a user from entering ANY data into field1 if the value of field2 = "C". If the value is not "C," then they can enter anything they want into field1. I have a database that customers can use to generate product orders by finding items, entering a quantity and clicking an ADD button to add the item to their order. However, some of the items on the product list (which are pulled into a layout from a portal) can't be ordered by themselves because they are part of a larger kit, even though they have their own unique item number, product description and photo. A field called KITCODE will contain a "C" of the item is a kit item that can't be ordered by itself and there is another KITWARNING field that tells the user the item is a KIT ITEM. Right now, I have it set up so that when they enter a quantity and try to add a kit item, FileMaker will clear what they entered and tell them they can't order the item by itself and it asks them to look for the main item listed above the kit item and add that one instead. But it might be better if they just can't enter a quantity at all for those kit items. I was trying to either make the quantity field not editable for just those kit items, or maybe have the ADD button disappear for those items. Right now, the ADD button is a button, but conceivably, I could make it a calculation field that would say ADD if kit code does not equal C. That might work, but if I can somehow disable a field for just certain records, that might work better. But disappearing items are not an option for conditional formatting. Any ideas would be appreciated.

Thanks for all the tips. I went with a modified version of the first option with the layout switching because I just ran out of time to work on that particular issue. But I plan to explore the second option as well. The final result has created quite a stir, so I'm sure I'll have many more chances to play around with it. Also, the list of items had to appear on another layout/table because my order form took up most of the screen space and a different set of items would be available for each new order based on the manufacturer. Customers can create multiple orders, but because of how the discounts are applied, they have to submit a different order for each manufacturer. So the first step to creating an order is picking or entering the manufacturer number and then they can click a link to see a new window with all the products available for that order. That is where they get to add them. The modified version also had to take into account that some items that were part of a larger "kit" could not be ordered separately and the powers that be also wanted the customer to be able to input a quantity at the same time they were adding the item to their invoice. But they also needed to be able to add the item without a quantity. However, I didn't want any quantity the customer entered to stay in the item record and show up when the customer adds that same item to other invoices. So here's the final scripts that seems to be working: If ( Related Products 2::KITCODE = "C" ; 1 ; 0 ) Show Custom Dialog ["Kit Item"; "This item is part of a kit. Please select the Main Kit Item listed above it instead."] Clear [select; Related products 2::Quantity Products] Else Set variable [$itemnumber; Value:Related Products 2::Item Number] Set variable [$Quantity_Products; Value:Related Products 2::Quantity Products] Set variable [$invoiceID; Value:Orders::Order No] Show Custom Dialog ["Item Added To Your Order"; If ( IsEmpty ( Related Products 2::Quantity Products ) ; "The following item has been added to Order No. " & Orders::Order No & ":¶" & Related Products 2::Item Number & ¶ & "Go to your program order to delete this item or specify a quantity." ; "The following has been added to Order No " & Orders::Order No & ":¶" & $Quantity_Products & " of Item Number " & Related Products 2::Item Number & ¶ & "Go to your program order to delete this item or change quantities.") Clear [select; Related Products 2::Quantity Products] Freeze Wndow Go to Layout ["Line Items" (Line Items)] New Record/Request Set Field [LIne Items::Order No; $invoiceID] Set Field [LIne Items::Item Number; $itemnumber] Set Field [LIne Items::Quantity; $Quantity_Products] Go to Layout [original layout] Commit Record/Requests [] End If In a nutshell, the kit items are all labelled, so hopefully nobody will try to order them individually. But if they do, they get an error message saying they can't order the item by itself and FileMaker clears any quantity they entered and returns to the list of items. Otherwise, FileMaker will set variables for the invoice ID, the item number and the quantity they entered. Then it will clear the quantity entered and give the user a message that says the item was added at a certain quantity, or it will prompt them to go to the invoice to add a quantity. Then it goes to the Line Items layout and creates a new record with all the variable data. Then returns to the original layout. Once the item number is entered into the Line Items table, that triggers several other fields to pull in additional data from a products table that also populate on the order form. I plan to post this question separately under a different thread, but if anyone has any suggestions for how I can disable the quantity field for just "kit" items (any item where the value of the KITCODE field = "C") so the customer can't enter a quantity at all for just those items, that might be preferable to having them enter a value just to have it cleared.

Thanks! That set-up looks like it will work. I'll have to see if I can recreate it in my database without missing anything. I'll also keep my eye out for any other instances where I cheated and used Copy/Paste instead of Set Variable. It seemed like the easiest and quickest way to go, but even as I was setting it up, I had a feeling it wasn't the best way to go.

No. It's version 11 Advanced. I just haven't updated my profile in a really long time.

My database has two tables; one with product information and another that is an order form. I set it up so customers can browse items in one window and click an "ADD TO ORDER" button that copies the item number from the item database, then goes to the order form layout, pastes the item number into an item number field in a portal on the order form, and then returns to the list of items. After the item number is pasted, the order form populates with all the relevant pricing data for that item from the item database. The general idea is to have the order form act as if it were a shopping cart. The problem I'm having is that I can't get the item number to paste into the current record on the order form. FileMaker has options to go to the First, Last or Next record, but I want it to paste into the current active record. Does anyone have any suggestions for how I can do this? Perhaps some kind of calculation? I tried to omit all other records, but the data will still paste into a different record, even if it that record is not included in the found set. I need the customers to be able to add items to whatever order form they happen to be viewing. Each order form does have a unique record ID. Here is the script I'm using: Copy [select; Products::Item Number] Go to Layout ["Product Order" (Program Order)] // THIS IS WHERE I"M HAVING THE PROBLEM... Go to Record/Request/Page Go to Portal Row [select; last] Paste [select; Line Items::Item Number] Go to layout [original layout] Thanks for any suggestions.

Thanks for the suggestions. I think I will work on setting it up so that other tasks can repeat too. That's a good idea. For this immediate need, I noticed my checkbox was already a button that was running a script that would check off the box and get a separate field to indicate whether the job was open or closed based on that checkbox. So I created a new script that first compared the Due Date to the current date, so that if anyone accidentally checked off an old occurrence, it wouldn't duplicate the entry. I also had it check to make sure the Completed Date wasn't blank and the task was "Computer Backup." If all of those were true, then it duplicates the record and clears the Due Date and Completed Date fields. Then I used two Set Field commands, one to set the new Due Date to the current date +30 and another to set the checkbox value to 0, which essentially unchecks the checkbox. Then I added a Custom Dialog to let them know a duplicate record was being made and then inserted the entire script as the last step within the script the checkbox was already running. So far it appears to be working. But now that it is, I'm sure they will want other tasks to be able to do the same thing, so the idea of having a Repeat Task field is probably where I will have to go next. Thanks!

I have an idea that I could use some input on. I set up a departmental To Do list for a group of users in FileMaker 9. I'm going to have these same users perform a backup of their hard drives every 2 months. So I want to set up a record for that task within the To Do list. In order to mark that the task is complete and have it not show up on their To Do List for the next day, they have to check off a box indicating that the task was completed and enter the date it was completed. What I want to do now is set it up so that when they check off that they completed the desktop backup, FileMaker will automatically create a new record for that specific user with a due date of 2 months away within the database, so that they don't have to remember to do it themselves. I wouldn't mind if FileMaker did this with no alerts, but it would be nice it it gave some kind of notice. So basically, the database will tell User X that he has to complete the task "Desktop Backup" on June 2. When he checks off that the task is completed, I want FileMaker to create a new record for User X saying he has to complete the task "Desktop Backup" on August 2. Here's the catch, I ONLY want the database to do this if the task field says "Desktop Backup." So that way this automation will not interfere with one-time only tasks. But I want the trigger for the new record to be marking the previous one as having been completed. Anybody have any suggestions?

Hi everybody, Does anyone know if there is a version of FileMaker that I can upgrade to that will bypass the limitation that lets you only view 5 records at a time when you are viewing them thru a web browser from a locally web published FileMaker Pro 7 database? That 5 record limitation is killing all my web publishing plans. I'm about to put in a recommendation for some FileMaker Pro upgrades and if either Advanced or Server can do this, then I'll get one of those added on. I tried getting an answer from FileMaker, but they replied and asked me to call their technical support. I just don't have the time or the patience today to sit and listen to musak for 20 minutes. So if anybody knows...that would be sweet. Thanks!!!

I'm working on a database that tracks the ads placed in a publication. Some of the advertisers place ads in multiple editions, so I created an "edition" field to track which ones they appear in. But I would like all of the fields for listings that are older than 2 editions to appear in red so that I can easily distinguish at a glance which ones are old and which ones are still pending. This is the script that I wrote using the text color function. Then I created a button for each record and set it to run this script. I know I would have to add the other fields to it, but I can't even get it to work on the one field. If (Database::Edition ≠ "May" or "June" ; TextColor ( Database::Edition ; RGB ( 255 ; 0 ; 0 ) ) ; TextColor ( Database::Edition ; RGB ( 255 ; 255 ; 255 ) )) I've never worked with the text color function, so I'm sure I'm using it wrong. Anyone have any ideas on how I can make this work, or some other way I can automatically distingush the old records from the new ones? I'm open to suggestions. As an alternative, I can have a new field that just calls them OLD, but I would prefer to have the listing change to a different color altogether. Thanks.

Thanks for the suggestions. They got me going in the right direction. I had buttons in the database that each user of the todo list would use to view their own projects. Instead of having that button just perform a find, I made it perform a script with the same find criteria and I just stuck in a step where it inserted the current date into the global "Today's Date" field. It doesn't keep the "Today's Date" field updated all the time, but it's a perfect work-around since the first thing anyone does when they go into the database is click on the button with their name on it.

I set up a project todo list with a container field that shows a blinking gif that says "URGENT" anytime I missed a deadline on a project. The container field is set to compare a field with the current date and another field with a project deadline. If the dealine date passes (as compared to the current date) and the project is still listed as incomplete, then the container field will show the blinking "URGENT" gif. I got it all working great, except I can't seem to get the current date to ever stay current on its own. I found a way to refresh it using a script and a button, but I need it to refresh on its own on a daily basis without me having to close the database and run a script when the document is open. I tried to write an Applescript that would close the document at night and then open it in the morning. That works when I test it, but it errors when I try to schedule the script to run by itself. I alternated between auto-entering the current date and using a calculation field for today's date, but neither worked. The date is automatically entered...but it doesn't change the following day. I'm sure there is a simple way to do this...but I can't seem to figure it out. Any suggestions would be appreciated.