1 2 Previous Next 16 Replies Latest reply on Jul 18, 2016 7:28 AM by dddan

    json file gets wrong (unstored) totals

    dddan

      Hi

      Just encountered a strange thing, was wondering if anyone else had seen this. I have a client (a shop) who has been using a FM system (v14 server plus 6 clients on Mac) for all his sales and purchases.  One of the things the system does is calculating (simplified explanation) the actual stock of each item by summarising all quantities that were purchased minus all quantities that were sold plus the opening stock which is manually entered.

      Because this is done through relationships (UUID of each item to the sales and purchase lines that have the same Item UUID) the current stock count is an unstored value.

       

      I am  working together with a web designer because the client wants to extend his webshop to display not only all products, but also their current stock count. The web designer uses PHP and has a script in place where he 'asks' FM Server for a json file of all current items. This works well. The json file contains the prices, brands, etc.

      (he has access to one layout, there is a script that he calls when logging in which takes him to that layout, it finds all the Items that should be exported to the web shop, and then he generates his json file based on the found set)

       

      But now I added the (unstored) stock counts to the layout/json file, and these counts in the json file are sometimes right, but more often wrong. It seems that if no sales or purchases were done with an item then it will grab the right opening stock (which is a stored value) but it seems that the more purchases and sales lines are associated with an item, the more the stock count is off.

      My impression is that the generation of the json file doesn't 'wait' until FM has summarised all lines in the unstored field.

       

      As a test I did an export to tab separated text and then I do get the right numbers.

       

      Does anybody else have experience with pulling data from FMS14 to a json file with unstored fields? Is this a known limitation? Or is there a way around this? (thing is, I am experienced in FM, but not on PHP, and for the webdesigner the opposite is true ;-)

       

      For now I created a work around where I run a script on the server every x hours, and this writes the current stock count into a number field (which is stored) and now it works ok. But we would like to have a 'live' connection.

       

      Any help is appreciated.

       

      Thanks, Daniel

        • 1. Re: json file gets wrong (unstored) totals
          wimdecorte

          Not enough information... how does the PHP ask FM for a JSON file?  JSON is not a native FM format so I imagine that PHP asks FM for a bunch of data (certain fields from certain records) and then constructs a JSOn file from that.

           

          When you see that

          dddan wrote:

          The web designer uses PHP and has a script in place where he 'asks' FM Server for a json file of all current items.

          Is that a FM script that the PHP code calls or is it a piece of PHP code itself that creates the JSON?

          Can you post more details about that?

          • 2. Re: json file gets wrong (unstored) totals
            beverly

            I don't rely on any calculation when web publishing, especially unstored. Can the raw data be gotten and then calculated (in the php) for displaying the results?

             

            beverly

            • 3. Re: json file gets wrong (unstored) totals
              dddan

              Hi Wim

              thanks for your fast reply. I guess part of the issue is that the web designer does part of it and me the other part.

              I just emailed him to send me his latest version of the script. For now I'll give you the one I sent to him which he used as a start point. (I got this from another web designer some years ago)

              I obviously changed the IP and login details. ItemsList WEB is the layout that contains the fields that he needs for the web.

              As explained, I am not a PHP person, but from what I understand it is the PHP code that generates the JSON file. I just direct him to the right layout, and the PHP codes grabs all the data of all the fields present on that layout.

              (I made a web direct access for him so he can verify his JSON file with the info he sees in web direct)

               

              Hope that this clarifies, as soon as the designer sends his current script I'll post it.

              Thanks, Daniel

               

              header('Cache-Control: no-cache, must-revalidate');

               

              header('Content-type: application/json');

               

              set_time_limit(0);

               

              error_reporting(0);

               

              require 'FileMaker.php';

               

              function errorCheck() {

               

              }

               

               

              $fm = new FileMaker();

              $fm->setProperty('database’,’SHOP’);

              $fm->setProperty('hostspec','http://111.111.111.111');

              $fm->setProperty('username’,’XXX’);

              $fm->setproperty('password’,’XXX’);

               

              $all = $fm->newFindCommand('ItemsList WEB');

              $all->addFindCriterion('WebPublishCB', '1');

              $result = $all->execute();

               

              if(FileMaker::isError($result)) {

               

                $error = $result->getMessage();

               

                echo $error;

               

                die;

               

              } else {

               

                $records = $result->getRecords();

               

                //echo 'result size' . count($records);

               

                $output=array();

               

                foreach($records as $record) {

               

                $product = array();

               

                foreach($record->getFields() as $field) {

               

                $product[$field] = $record->getField($field);

               

                }

               

                $output[] = $product;

               

                }

               

              }

               

              echo json_encode($output);

              • 4. Re: json file gets wrong (unstored) totals
                dddan

                Hi Beverly,

                Thanks for the suggestion. No, not really possible, they have around 8000 items, and some items have 100's of related sales and purchase lines. It would become too complex for the designer to do all calculations. 

                But I get your drift, so if we can't get this to work reliable I'll first store all current counts, and then use those for the json file creation.

                BTW, almost all other data in the json file is calculated, but stored, and we have never had a problem with that. (the shop has been online for years, we are now adding the stock counts to it)

                So I tend to say that you don't need to avoid calculation fields, as long as they are stored.

                 

                Daniel

                • 5. Re: json file gets wrong (unstored) totals
                  wimdecorte

                  dddan wrote:

                   

                  Hi Beverly,

                  Thanks for the suggestion. No, not really possible, they have around 8000 items, and some items have 100's of related sales and purchase lines. It would become too complex for the designer to do all calculations.

                   

                  Just as complex as doing it in FM, no?  Put potentially faster...

                   

                  dddan wrote:

                   

                  So I tend to say that you don't need to avoid calculation fields, as long as they are stored.

                   

                   

                  All calculation fields have a price.  Unstored ones: they need to be calculated every time you want to display or use the value.  Stored ones: they can slow down record creation by having to do the first calculation at that time.

                  • 6. Re: json file gets wrong (unstored) totals
                    beverly

                    Just as complex as doing it in FM, no?  Put potentially faster...

                     

                    Agreed. Sometimes it is.

                     

                    beverly

                    • 7. Re: json file gets wrong (unstored) totals
                      dddan

                      I understand your point, but there are a couple of reasons why I don't want to go down that road.

                      - the calcs are already there in FM, so it would be a lot of extra work to redo them again for the site in PHP.

                      - the FM system is not only used for the web, the main use is a real physical store with 6 sales people selling stuff in the store. The web shop is an extra add on. So in the real shop they need all the stock numbers anyway.

                      - the FM system doesn't only do purchases and sales, but also ordering, quotes, payments, financial reporting, vat returns, fiscal reports, etc. In the shop they use 8 different kinds of stock (free stock, stock in jobs, stock in orders, financial stock, etc.) so the logic in the system is quite complex.

                      - the table where stock changes are registered also holds all payments and for security reasons I prefer to keep remote access to that part of the system as limited as is possible. If the web guy would calculate the stock of each item, I would need to give him direct access to this table, which now is not web accessible directly.

                      - from a data integrity point of view I prefer to have one system (FM) calculate/generate all values, and the web system just using those. If my client in the future would decide that he would like to change the way the 'web available stock' is calculated then it is cumbersome and also prone to errors if two developers each have to implement those changes in the same way. Biggest nightmare is of course that the web has a different opinion about the availability of an item than the shop.

                       

                      As a workaround I now have a timed script running on the server that stores these unstored values into stored fields. I think that that in our case this ismore efficient, we just need to make sure that this script is executed before he requests the current stock situation.

                      This is also the reason I would prefer a 'live' connection, but from your reply I get the feeling this is not reliable.

                       

                      Hope this makes sense to you ;-)

                       

                      EDIT: if someone meanwhile can shine a light on the use of unstored fields in a JSON file I would appreciate that. If there is a reliable way to do this, it would be my first preference. Thanks.

                      • 8. Re: json file gets wrong (unstored) totals
                        wimdecorte

                        dddan wrote:

                         

                        EDIT: if someone meanwhile can shine a light on the use of unstored fields in a JSON file I would appreciate that. If there is a reliable way to do this, it would be my first preference. Thanks.

                         

                        There is no light to be shed unfortunately... the unstored calcs don't care whether they are used in a JSOn file.  The issue is entirely with the unstored calcs.  Perhaps there is something in the calc definition that does not produce the right result when called through the PHP API.  Do they rely on globals for instance, or relationships based on globals?

                         

                        When working with orders and inventory; a better approach is to not rely on unstored calculations to do tallying of order totals and inventory counts.  Instead use a fully transactional approach where the totals are updated on each order transaction, shipment transaction, inventory in/out transaction...

                        • 9. Re: json file gets wrong (unstored) totals
                          beverly

                          ditto. I use as few calculations (web or non-web) as possible. Scripted Set Field for anything that needs to be there and should only be updated if the related fields change (then retrigger the set).

                           

                          beverly

                          • 10. Re: json file gets wrong (unstored) totals
                            dddan

                            Hi Wim

                            Ok, that is a clear answer. No, the relationships should all be ok, and do not involved relationships to globals. The relationship itself is quite simple; ItemsTable::ItemUUID= TransactionsTable::ItemUUID. In the transactions table each line that involves an Item has calculations that check the kind of transaction, and what consequences this has for the different stock counts of that Item on the same line. (for instance -1 for financial stock, +1 for stock in jobs)

                            These calcs do not refer to any globals either.

                            In Items there are calculation fields that summarise these stock consequence fields through the above mentioned relationship and display the total.

                             

                            I hear you when it comes to updating and storing all totals after each transaction. Both systems have their own pro's and con's. Especially on a network with several users maybe accessing the same item/record it is quite a hassle to have a reliable system where all the different counts are written back to that record making sure that you have access to it. (I know it is possible, but not simple when you have a lot of different kinds of transactions)

                            I have made systems using that method too, but I am not sure what I prefer. I have quite some systems running which use a lot of stored calculations, and if needed unstored calculations, and up til now I am quite happy with the reliability, performance and ease of use. I avoid unstored as much as I can because of speed, but even those, when used carefully, work quite well, even on a server/client based system.

                            • 11. Re: json file gets wrong (unstored) totals
                              wimdecorte

                              dddan wrote:

                               

                              $all = $fm->newFindCommand('ItemsList WEB');

                              $all->addFindCriterion('WebPublishCB', '1');

                              $result = $all->execute();

                               

                               

                              What is the expectation of this find request?  How many records does it return and what entity do these records belong to?  How many unstored calcs per record?

                               

                              The results of the query go into a PHP Array and then later that Array is transformed into JSON.  So the question is: what does the PHP find request return?

                              • 12. Re: json file gets wrong (unstored) totals
                                dddan

                                Hi Wim

                                this request asks for all Items in the Items table (so nothing with related records) that have the checkbox publish to web on. (the store doesn't want to have all items online, so they tick a box (numeric, 1 for yes) for each item that should be available in the web shop) the find request returns around 5000 records at the moment.

                                For each record the layout from which the JSON is created contains: SRP, brand, item, item notes, features, vat code, product group, web search keywords and web sub groups 1-7, post-able. (these are all text or numeric input fields)

                                Then some (stored) calculated fields: our price, our discounted price (based on SRP minus percentage discount or discount in absolute value) and the ex vat equivalents. And then the two new dreaded fields, free web stock. (which is the unstored calculation mentioned earlier)

                                and TimeTillNextArrival which holds the shipping status of next order if any. (this field also indicates if an item is end of life, available on request only, etc.) this is also an unstored field because it is based on the same relationship but now looks at the date of any not received (same) items and their order date.

                                • 13. Re: json file gets wrong (unstored) totals
                                  dddan

                                  PS - Inspired by your comments I just did a test; I removed all (stored and unstored) calculation fields from the layout the PHP script accesses except the one unstored field with the stock count, and it still goes wrong.

                                  Just to see if it would differ depending on the amount of (unstored) data it needs to grab.

                                  • 14. Re: json file gets wrong (unstored) totals
                                    wimdecorte

                                    If you go to that layout in FM and scroll through the records, are the unstored calc numbers always correct?

                                    What about WebDirect?

                                     

                                    What if you do an XML query to do the same as the PHP query and look at the resulting XML; are the numbers correct there?

                                     

                                    Can you post a screenshot of the actual unstored calc and the relationship setup dialog?

                                    1 2 Previous Next