2 Replies Latest reply on May 21, 2012 12:42 AM by PeterThorton

    Find not finding what it's supposed to

    PeterThorton

      Title

      Find not finding what it's supposed to

      Post

      Hello,

      apologies for posting an oversized screenshot, I wanted all the pieces of the puzzle to be visible.

      In short, I'm trying to make a script, whose functionality depends on the Find step finding the appropriate record.

      In the screenshot you can see that my CustomPrice table contains a record with ProductID of 1 and BusinessPartnerID of 2. You can also see that the step right before the Find shows a custom dialog that displays the values of the two variables. The dialog box confirms that the value of $$ProductID is indeed 1 and $$BusinessPartnerID is set to 2. The screenshot does not show this, but I hope you'll take my word for it.

      Yet the search performed in the next step finds nothing. How is this possible? What is it that I'm missing? What other factors could affect the result of the search?

      failed_search.png

        • 1. Re: Find not finding what it's supposed to
          GuyStevens

          A normal search would be:

          Enter Find Mode  // clear pause box
          Set Field   -  Set the field you want to search to the value you want to search for
          Perform Find.

          But it seems to me you are setting quite a lot of fields. For instance you seem to be creating an orderId yourself?
          Isn't this an auto enter serial number?

          Also why do you set product item description in your orderline?
          Just the ProductId should be enough, And you should have a price lookup field in your orderline file to lookup the price at the moment of adding it. So that when there is a price change the price doesn't change on the old invoices.

          I'm not sure, because you have confusing names, orderline and orderitem. But normally speaking you would have this structure:

          Invoice
          Id
          Date
          CustomerIdFk
          ...

          LineItem
          Id
          InvoiceIdFk
          ProductIfFk
          Amount
          PriceLookup
          Total  -  Amount * Price Lookup
          s_Total - summary total of total.

           

          Then you would go into your invoice table, create a new record; Maybe set the customerId.
          Set the InvoiceId as a variable.

          Then you would go into your lineitems. Set the InvoiceId variable in the InvoiceIdFk field.
          Set a productId, an amount, and you're done.

          I never ever use find requests. I always use the three steps outlined above.

          I also find it odd that you link a customer directly to a product. What if one customer wants multiple products? Are those then all different orders?

          • 2. Re: Find not finding what it's supposed to
            PeterThorton

            I already solved this problem, by navigating to the appropriate layout before performing the search.

            The data structure is not the issue in this case, but since you've been wondering about it, and you took the time to suggest your version, I'll elaborate briefly.

            The "direct link" between the customer and the product is to set a custom price for a given customer and product (thererefore the table name "CustomPrice") Our products have a standard price (and there is a field for it in the product table) but we need to be able to say "this customer buys this product from us at this specific price". Apparently we do that a lot. Many of our customers are dealers who have negotiated bulk prices with us.

            I'm not creating an OrderID by myself. I just carry it over from a previous script in a global variable.

            I need both item code and item desription, because item code is something like "BLH223AH" while the item description would be "ACME Neutrino-Accelerated Intergalactic Vacuum Cleaner" and they both have to be on the order.

            The confusion with OrderItem and OrderLine takes a bit more effort to explain. See, before I joined the company, a FileMaker "Product" table containing all the product info was compiled by a guy who has no idea about how relational databases work. The table was a total mess and for all its usefullness for data storage it could have been created in a single Excel spreadsheet.

            But that's what I had to work with. I created the OrderItem table to serve as a join table between Product and Order. Then we had a meeting where we went through the table and all the data it contained. I identified several entities chucked into a single table and suggested the table be split.

            A lot of records listed as products were really packages containing other products.

            For example, product A is, say, a hammer. Product B is listed as a separate product, it has its own product code, but is in fact a box of 12 hammers, ie 12 times product A. This had to be broken down into multiple tables, otherwise it would be next to impossible to track how much stock we have.

            A shipment comes, it contains 3 pieces of product B - 36 hammers in 3 boxes. So you increase the amount of product B by 3. Then you sell 6 hammers. You now have 2.5 pieces of produc B in stock. Doesn't really work, does it?

            So when 3 pieces of product B arrive, knowing that product B is in fact a box of 12 products A, I increase the amount of product A by 36.

            However the packages are items that are bought and sold, with their own item codes, just like the products themselves. In general we order in boxes from our vendors, and sell by individual pieces, but we can also sell whole boxes and order individual pieces. So the system must be able to put both Product and Package records on the order. That's why I created the OrderLine table. It's sort of a "lookup" table, if you will. It stores the values of a single order line, with scripts filling in the values depending on whether they come from the Product or the Package table. The OrderItem no longer links Order with Product, instead it links Order with OrderLine. I know the naming is confusing, but I haven't thought of a better name yet, and I don't want to go through the ordeal of changing a table's name, and then finding out some of my scripts no longer work.