mpau

Filemaker crashes on one of the computers in our office every time we run save as PDF. We've followed the troubleshooting steps on the FM website (throwing out the plist, a test user account). We've also reinstalled adobe. However, the problem remains. I'm wondering if any of you out there have any experience with this issue and have recommendations for a fix. We're using FM pro 12. The computer is a Macbook Pro mid 2009. OSX Mavericks 10.9.2 Thanks in advance for any help with this.

Is there a way to trigger a script after a (portal) field value is committed? All the script triggers seem to run before the value is committed and so any set fields or insert calculations steps refer to the prior field value, not the current, active value. For example, I have a standard invoice layout with line item records in a portal. On the invoice record is a subtotal field. It is not a calculation and so requires a script to update it. I'd like to update this field each time a unit price is changed. Right now, I have it updated by using an OnObjectValidate script with the step Set Field (Invoice::Subtotal; Sum (Lineitem::UnitPriceExtended). For the subtotal field to accurately update, I have to run a commit record script step first. This works, but it's slow. Is there another way to do this?

I've been developing a database for the past few years and been learning as I go. My abilities have been proficient enough to develop a database that does almost everything we want it to. We use ours to manage a retail, wholesale, manufacturing business so records deal with customers, vendors, orders, purchase order, products, parts, shipments, etc. However, there is an issue of speed. Locally hosted, it works fine. (Although now that we've integrated more CRM functionality - including a dashboard - it is sluggish on load.) But we rely on remote access for some of our users and it can get very slow on some layouts and pages like the dashboard are not usable. We do not have more than a few thousand records. I feel certain that the fault lies with my programming and the way the database was initially structured. I've spent the past several days going through and testing different optimizations - for example, replacing as many un-stored calculations as I could with auto-enters. Though, I am not seeing much improvement and to be honest, I'm not even sure that this was the right approach. I find myself wondering more about best practices (filtered portals vs table occurrences, indexes versus calculations, script triggers versus calculations, etc) and as much as I read the forums and do tutorials I can't come to solid answers or understanding. I'm wondering if the folks in the forum would pass along some of their knowledge about what best practices are in terms of the following: 1. Filtered Portals or Table Occurrences - Filtered portals make the relationship graph so much cleaner. But which is faster? 2. Un-stored Calculations versus auto-enter calculations. This one really stumps me. I've spent days eliminating as many un-stored calculations as I could - to the point where I felt it seemed like overkill because some fields just are by their nature calculations. Nonetheless, I don't see much of a boost in performance. Actually, in some instances I wonder if all the triggered auto-calcs (particularly those triggered by scripts) don't take longer to run than the straight calculation field. 3. Script triggered auto-enter calculations versus calculations 4. Memory Cache settings 5. Sorting - Sorted records seems so essential for the user but from what I read, it sounds like this has a major impact on performance speed. True? 6. What is the number one most important developmental aspect of an efficient database? Table relations? Stored data? 7. Other best practices that I'm missing or that everyone should know, please share. Thanks for any help or thoughts with this. Your expertise and knowledge is greatly appreciated.

I was under the impression that an auto-enter calculation only triggers on record creation. Is this correct? Or can it be re-triggered? Can you explain your example more to me? Ideally, I'd like to have three statuses: Paid, Partial, Unpaid What I meant about the work around, is that I'd prefer to have the status field live update while users are working in the record and not have to rely on the layout exit or enter script trigger to set the status field.

I'm working in an order record. I have a text field called 'Pay Status'. I have a calculation field called 'Amount Due'. When amount due equals 0 I'd like to run a script to set 'Pay Status' to 0. I have the script triggered by the 'Amount Paid' field, which is a number field. However, there are some instances when no pay is required for an order (comps, replacements, etc). In these instances, I don't want the 'Pay Status' to remain as 'Unpaid'. I would like to have the script triggered as well. However, since the 'Amount Paid' field is never activated, the script doesn't trigger. I'd like to avoid layout script trigger work arounds.

Is is possible to have a script trigger when the value of a calculation field changes?

Vaughan's solution worked. My error was that I set the cActiveCustomerName to return a number and not text. And that I continued to use the cActiveAccountID for the first field of the value list. For anyone who would like it, I have attached the working demo. It's important to note that with Vaughan's solution, the cActiveAccountID calculation is no longer needed. If you try to run the value list using this in the first field it won't work. For it to work, you need to have the first field set to CustomerID and the second field set to cActiveCustomerName. Sort by 2nd field and display 2nd field. Thank you, Vaughan! ActiveAccountWorking.fmp12.zip

I tried that solution and the value list field is now returning a "?" See the attached file ActiveAccount.fmp12.zip

The second value (i.e. names) are unique. Any other thoughts? Perhaps you can have a look at the file and let me know your proposed solution.

The calculation field is stored. And the value list works as long as it is sorting by the first field. The problem is that if I choose to sort by the second field, the value list no longer filters but rather now contains all values for the 2nd field. If you have a moment to check out my file, there's a working model there of the issue. The question is can a conditional value list work if it is sorting by or displaying only the 2nd field? If so, what am I doing wrong? Or by their nature, do conditional value lists only function with first field values?

Apologies for the duplicate files. I've edited out the redundancies. Responding to RW, I am happy to sort by the second field. In fact, I'd prefer it. However, by selecting that option the value list populates with all of the values from the second field and not the filtered values established by the condition. So I get all customer records, rather than only the "active" records.

I have a conditional value list set up to show only values for active accounts. The value list is defined by the calculation field cActiveAccountID = If (CustomerStatus = "Active" ; CustomerID) The value list shows values both from the CustomerID field and from a second field: CustomerName The value list works perfectly if I sort by the first field. However, I would like both to sort by and display only the CustomerName. Meaning, I would like to sort by the second field and display only the second field. If I select either of these options the value list now returns all customer records, for both active and inactive accounts. Has anyone else run into this issue? Any solutions? I feel certain I'm missing something small. I've attached a demo file of the problem here. Thanks for your help ActiveAccount.fmp12.zip

I just wanted to follow up and say that everything worked out perfectly. It took me a little bit to figure out how to manage the lineitems so that I could still split the order contents across shipments but it is all working fine now. Thank you so much for the help.

Thanks, Win. I'll give it a shot. You're side note is correct. That is how I have it set up not as I previously wrote. I'll let you know how it goes.

Hi Wim, Thanks for the reply. If I set up a many-to-many relationship between orders and shipments, what is the breakdown for the primary and foreign key relationships? Right now, my set up is _pkOrderID => __fkShipmentID How do I alter the __fkShipmentID field so that can belong to multiple orders? Is it as simple as tagging the various orders with the shipping record id instead of the inverse? Or do I need to establish a new field? Thank you.

I'm working with a database that has a straightforward one to many relationship for customers, orders, line items and shipments. The structure in place is : Customers => Orders => Line item <= Shipments Shipments are created from the order records and tagged accordingly with the unique identifier for the order. Unshipped line items from that order are visible via portal on the shipment record. As they are added to that shipment they are in turn tagged with the unique identifier for the shipment. This works great and without issue. However, we have recently had several requests, from customers who have multiple open orders, to consolidate all the unshipped items across their various orders into one shipment. Is there a way to do this without also consolidating the order records into one? For record keeping purposes, I would prefer to keep the individual orders unique.

Thanks I'll check those out. Since our last exchange I had an odd and similar issue crop up in my database. For two years now I've successfully used a set up to create shipments that relies on multiple table occurrences of my Lineitem table, one for shipped items (Lineitem_ShipmentsSHIPPED) and one for unshipped (Lineitem_ShipmentsTOSHIP), each having a unique relationship to the Shipments table. For Shipped Items the relationship is as follows: Lineitem_ShipmentsSHIPPED::_fkShipmentID = Shipment::__pkShipmentID. For Unshipped Items the relationship is: Lineitem_ShipmentsTOSHIP::_fkOrderID = Shipments::_fkOrderID AND Lineitem_ShipmentsTOSHIP::FlagShipped ≠ Shipments::ConstantOne Key fields above - LineItem::Flagshipped (Calculation) = Case ( not IsEmpty (_fkShipmentID) ; 1; "") Shipments::ConstantOne (Calculation) = 1 On shipment layouts I have two portals one for Lineitem_ShipmentsTOSHIP and the other for Lineitem_ShipmentsSHIPPED. There are script trigger buttons in each that move the items from one window to the next by setting the values of either Flagshipped or _fkShipmentID. It's always worked beautifully. Until now. Oddly, the Lineitem_ShipmentsSHIPPED still works but the Lineitem_ShipmentsTOSHIP no longer shows anything. I haven't changed anything. I spent about an hour yesterday testing and checking the calculations to make sure everything was fine and couldn't find anything wrong. In the end, the failure seems linked to the AND criteria in the table relationships. By changing the Lineitem_ShipmentsTOSHIP portal to a filtered portal of the Lineitem table it worked. (filtering by the same calculation as the table occurrence (so Lineitem::_fkOrderID = Shipments::_fkOrderID AND Lineitem_::FlagShipped ≠ Shipments::ConstantOne) I'm beginning to wonder if there's a bug with the AND criteria in table occurrences. Has anyone had a similar experience? It would explain both of the issues I've been having. But the question is, why now? I can't remember doing any updates.

Hi Comment, How can I get in touch with you about doing some consulting work for me? Your posts are always the very helpful to me and I'd love to have you take a look at a database I've built. thanks, Michaele

The relationships is actually Orders - Lineitem - MasterProducts (base products)- Products (product styles/options) - Inventory Log - Parts Like you, I thought that the problem might be that Inventory Log was going through too many tables to properly calculate, so I created another table occurrence of Lineitem and directly related it to Inventory log but the results didn't change. It seems to be to have something to do with using a calculated date field as part of the calculation. Does that make sense to you? Unfortunately, as the Inventory Log doesn't create date based records, I don't know how to change this. The inventory log creates static records that are based on relationships between parts and products and they hold running and constantly changing tallies based on the draw from both sides. It works great as far as tracking inventory goes, but the failure is that I can't create any kind of report except for the current date. Maybe my entire structure is wrong. What do you think?

Makes perfect sense. Unfortunately, doesn't seem to work. I'm unclear as to why I'm able to properly calculate based on productID match criteria but not based on date criteria. Oddly, I am able to get the proper date related Lineitem::OpenTotal value via filtered portals but can't pull the portal field data from the portal to use in a calculation. The calculation is created within the context of the Inventory Log which is a join table between Products and Parts that keeps a running log of the totals of each and their respective draws on one another. The Lineitem table is a join table between Orders and Products.

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::_pkProduct and LineItem::SaleDate = WeekOfYear(Get(CurrentDate)) - 1;Last (LineItem::OpenTotal) * PartQtyNeeded;0) But when I add a second match criteria to the calculation it fails, and returns a "0" value. When I run the calculation as follows it also fails, "OnOrderWeekPrior"::Case(LineItem::SaleDate = WeekOfYear(Get(CurrentDate)) - 1;Last (LineItem::OpenTotal) * PartQtyNeeded;0) So I'm presuming that the issue has to do with calculating based on a calculated date parameter. Any thoughts out there? thanks!

I have a database I developed a ways back with tables ORDERS, LINEITEM, PRODUCT, and PRODUCTGROUP. Each Product belongs to a Product Group. Groups are essentially master products and the products are style variants of the master product or product group they belong to. So for example, master product Chair and product is Chair Red (style), Oak (material). The reason for breaking the products down this way is because of a draw on an inventory parts list that is different for each product variant. Till now everything has worked beautifully. I create LINEITEM records via a portal on the order page and currently have it working via a relationship between _kProduct(Product) and _fkProduct(Lineitem). I select the products from a dropdown list populated by product names. But recently, the list of Products has gotten so long that the drop-down Product Value list is getting too long. So, I'd like to narrow down the list for the users starting with ProductGroup (only 12 compared to the 60+ list of Products). Ideally, I'd like the user to be able to make selections in the portal starting with Product Group and then moving through Style and Material (populated by conditional value lists sorted by Product or Product Group). My question is: is it possible to set up a portal where the user is able to select products based on criteria like product group, style and material rather than having to select the end product itself? Can filemaker find the appropriate end product?

I'm building a somewhat complex inventory tracking database. The database tracks customers, vendors, orders, purchase orders and inventory. Inventory is built or depleted based on orders (deplete inventory) and purchase orders (increase inventory). I have built a successful model using the following tables (--||> signifies the direction of the one to many relationship in the tables): Customer --||> Order--||> LineItems<\\--Inventory--||>PurchaseOrderLineItems<||--PurchaseOrder<||--Vendors In the system I've built, PurchaseOrderLineItems properly increase inventory and LineItems properly decrease. Everything works on a one to one basis. The product bought is the same as the product sold. The problem I'm having I need to build a model where NOT all inventory is bought and sold on a one to one basis. Instead some products that are bought for inventory aren't the end product sold to the customer. For example, boxes. Boxes are bought but not sold directly. Instead, each time a particular product is sold a box is used. So, I'm trying to tell the DB to deplete the inventoryStock of a box whenever a particular product is sold. I have figured out how to do this on the LineItem record but can't figure out how to get the Inventory Item record for boxes to see the result. Same is true for products that are sold that rely on and draw from several inventory parts. Any thoughts or ideas? I'm stumped and open to any ideas. I've thought about nesting products, about using category "stamps", about conditional value lists, and script triggers but can't figure out what to do. Right now, the best I've come up with is to Trigger a Script where each time a LineItem is created via a portal in an order record another LineItem is created for a Box with the same quantity. This works and I have the Script running on modification of quantity as well. But there are too many variables here. If the product LineItem is deleted the box line item needs to be as well, and I can't figure this one out. It also relies on a Next Portal Row script step which seems less than ideal for me. I'm happy to email anyone the current solution if they'd like to mess around with it or have a closer look. I've attached a screenshot of the table view and the inventory view

Hi All, I'm creating a project management database where the relationship structure is Category (parent), Project (child), ToDo (grandchild). Each category is a record, each project a record, etc. For sake of navigation, I'd like to create a grid layout where users see what is essentially a "home page" grid of categories (categoryName fields are all that need to be shown), 6 across and an infinite number of rows down. Users click on a category and are taken to another grid of projects that belong to that category. They can then click on a project to see details and todos etc. I can make the navigation work if I forget about the grid and use the list layout. But I would like to do this as a grid. I'm stumped. It seems like a grid layout should be an option. I'm fine with portals, merge fields, anything that works to get the records to show up as a grid on one page. Anyone out there have any thoughts or suggestions? Thanks!!!

Figured everything out. I was able to easily adapt your demo to fit my DB and it works! Thank you so much bcooney! I would definitely not have figured this out on my own.