14 Replies Latest reply on Jun 25, 2010 4:27 PM by philmodjunk

    Filemaker  Files / tables or portals (Design considerations)

    yandg

      Title

      Filemaker  Files / tables or portals (Design considerations)

      Post

      I have a new task which i thought would be the easiest but i'm not sure how to actually set up in Filemaker as i keep coming across problems in the way it needs to work. 

       

      The set up is fairly simple but i'm not sure on best practice in setting this one up.

       

      We have a STOCK file holding probably 10,000 records, at the minute when an item is sold we fill in the sale details on each iem sold (All these items are serialnumbered and so are all different records) So if Mr Smith buys 2 items. each record is filled in with his details.

       

      At the year end process all the SOLD records are copied to a sold back up file and deleted of the live STOCK file. Sold file approx  5000 - 6000 records

       

      • Option A; The Filemaker design i am setting up now will show the sold lines on a SALES file either in a portal (LIne items) or in repeating fields.(Max no approx 8 required)
      • The STOCK file is marked as sold and a SalesID.

       

      • Option B: Or could a  complete record of STOCK be copied to a SOLD file as soon as item is sold which is then the portal showing line items on the sales record.  This way ALL Stock items are on the STOCK file and ALL sold items are on the SOLD file. which would make it easier to run sales and stock reports.

      Any items returned can be copied back from SOLD to STOCK as required.

       

      Any ideas on other options or on processes for option B appreciated

       

      This would also mean the STOCK file doesnt keep growing year after year.

       

      Any pointers at all would be helpfull.

       

      Mike

        • 1. Re: Filemaker  Files / tables or portals (Design considerations)
          philmodjunk

          Instead of having two files/tables to separate sold and unsold items, keep all the records in one table and just add a status field that marks an individual record as either sold or unsold.

           

          When you want a report listing sold items search for records in the table where those items marked "sold". For a report listing items available for sale do a find for items marked "unsold".

           

          Note that portals listing these records can have a filter expression (filemaker 11) or a filtering pair of fields (older versions) that limits all the items shown to be either sold or unsold.

          • 2. Re: Filemaker  Files / tables or portals (Design considerations)
            yandg

            Thanks for your reply, 

             

            I started to set up the file as you say, buy having one stock file that when a record(Item) is sold it becomes marked as such with the salesid so it

            can be linked to the portal in the sales file.

             

            • I was unsure about the size this file will become after a while as all new stock is added at the rate of probably 10 - 15 items a day sometimes more.
            • With this file holding all stock and sales for a few months/years i  wondered if performance would be affected.
            • If i use this method would  i be able to archive records after a while (say yearly) as would this cause problems for the portal that is referencing the Stock file.

             

            This option would be easier to work with as far as when stock is returned from sales to stock for whatever reason.

             

            To recap i think this would be the better solution but just unsure as to how i can setup the portal on the Sales file to reference the stock file and also keep this info if and when we archive records. (If i need to actually do this)

             

            thanks again for your help.

             

            Mike

             

            • 3. Re: Filemaker  Files / tables or portals (Design considerations)
              philmodjunk
            • I was unsure about the size this file will become after a while as all new stock is added at the rate of probably 10 - 15 items a day sometimes more.
            • With this file holding all stock and sales for a few months/years i  wondered if performance would be affected.
            • If i use this method would  i be able to archive records after a while (say yearly) as would this cause problems for the portal that is referencing the Stock file.
            •  

              One of my tables now has more than 3 million records in it. We add records to it at the rate of several thousand per day. So filemaker can handle it. However, size does affect performance especially if you find you are needing to run summary computations on large numbers of records, find records with criteria in an unstored /unindexed field or sorting with a sort order that includes such a field. We do use a script that moves the records into an archive file so that recent data resides in a different, smaller file that is more responsive. When we need to check up on historical data, we switch to the archive file and use it. Our script runs automatically every evening after close of business. In your case I'd set up such a script, but I wouldn't use it until I see performance is lagging--even then I'd analyze my database design and try to make for a more effiecient design before resorting to an archive method. 10 - 15 items a day sounds like a miniscule growth rate so you may find you don't need to archive more than once every few years if then.

              • 4. Re: Filemaker  Files / tables or portals (Design considerations)
                yandg

                Further to previous posts of Info........

                 

                I'm erring towards the 'Stock' file with fields for sold information to identify stock from sold items but when our staff sell ether one or more items off the stock file what would be the best way to actually update this. 

                 

                 Originally the customer file would open up the sales screen that would have customer details prefilled and then enter items to a portal (Line items) or in to repeatingfields (Max lines would be 10). Another portal of items would be shown below for 'Non stock' goods or accessories, which can be entered as required manually.

                 

                The 'Stock item portal' could just be linked to the Stock/Sold table to show items with the same sold_id to be shown.

                 

                 But how do I easily get the sold info on the Stock file records to identify the sale.(Without having to go to that table manually entering data)  more than once

                if multiple items are sold.

                 

                Still not 100% sure how is best to set this up as each option has its downsides in userability. It needs to be easy for staff topick a customer or enter a new one, then add serial numbered stock items to a list for printing an invoice (along with non stock items ie accessory parts)  The Stock items need updating as to being Sold and with the customer id & invoice id.

                 

                It seems just to be the problem of updating the Stock table with the Sold info that is making me unsure of this option.

                 

                any ideas ???

                 

                 

                Thanks Mike

                 

                 

                 

                • 5. Re: Filemaker  Files / tables or portals (Design considerations)
                  philmodjunk

                  Thanks for the info that each item has a serial number. I was wondering why you needed one stock record for each item sold.

                   

                  With the right relationship, you shouldn't need to update data in the stock table at all. A link from stock to your table where you log each item sold will match to a record if the item is sold and will be empty if it is still in stock.

                   

                  A simple unstored calculation field like this will show a different text label based on such a relationship:

                   

                  If ( IsEmpty ( SalesTable::ItemID ) ; "In Stock" ; "Sold" )

                   

                  Alternatively, since there could be a performance hit due to this being an unstored calculation, you may find it useful to write a script that finds the newly sold items and marks them sold when your sales clerk clicks a button that marks your sales invoice as completed (Or you might use a script trigger if this is not a web published user interface). This can happen automatically and invisibly to your users.

                  • 6. Re: Filemaker  Files / tables or portals (Design considerations)
                    yandg

                    Thanks again for the information.

                     

                    i can set a script to update the required fields as you suggest and this works fine. I can get the Stock file updated with 'SOLD' and sales id etc as required.

                     

                    I'm still 'going in circles' re the layout as each option ive tried causes problems in one way or another. 

                     

                     My first attempt of the Sales Table was to have customer info filled in and  a portal to show the 'STOCK' items listed for this sale.  If i get the portal to look at the Stock table i cant seem to get it to look up by serialID as the Stock file needs to have a relationship with the Sales table. (which it hasnt yet) Unless we goto the STOCK file and find the serial numbered stock manually and then click (run a scrit) that copies the stock data to the Sales table ready for customer info to be entered.?   This way though means switching screens a lot if there is more that 1 STOCK item to be sold on the same sale.

                     

                    Not too sure how to get this way to work. 

                     

                    The Sales table would just be used for information and printing sales invoice / delivery docs and history data etc. The main STOCK file would be used for reporting sold and stock items.

                     

                    any ideas appreciated.

                     

                    Mike

                     

                     

                     

                     

                    • 7. Re: Filemaker  Files / tables or portals (Design considerations)
                      philmodjunk

                      There's more than one way to do this.

                       

                      One option would be to display a portal of all unsold stock records. Put a button in the portal row that you click to select that item for the current sales record. Your button's script would then enter the SalesID value from the current sales record into a field in the stock table.

                       

                      You can then either define a second relationship linking Sales to Stock by this SalesID or (in Filemaker 11) you can set up a filtered portal that filters out all stock records except those with a matching SalesID. You'd then place a 2nd portal based on this new relationship (or the filtered portal) on a different layout if you need to see just the items on a given sales record.

                       

                      Using the new relationship just mentioned, you can also create a report on a stock table layout that refers to the sales record fields placed in a header and you can then print out this sales report after performing a find to find only those stock records with a specified SalesID.

                       

                      Note: to create different relationships between the same two tables, you make new table occurrences in Manage | Database | Relationships that point to the same data source table. (Data source tables are the tables listed on the tables tab and Table occurrences are the "boxes" on your relationships tab.)

                      • 8. Re: Filemaker  Files / tables or portals (Design considerations)
                        yandg

                        Once again many thanks for the ideas,

                         

                        I have tried to set up a portal showing the Unsold items from STOCK, with a button to run the script to update required fields, but i am not sure how to get the portal to display the Unsold records from stock as far as relationship / connections go. How do i link the two tables to show all stock items ?

                         

                        Would i be able to have this portal as well as a portal showing the items clicked on (ie sold) So when an item is found and clicked it appears on the other portal (of sold items this invoice)  The link here would be soldID.

                         

                        Thanks again for your time and information.

                         

                        Mike

                         

                         

                        • 9. Re: Filemaker  Files / tables or portals (Design considerations)
                          yandg

                          NEWS........

                           

                          sorry i think ive got it working now.....

                           

                          Have linked the main portal to STOCK by filter only Stock items and where salesID is not equal to STOCK:salesID (this shows all stock)

                           

                          If i click the script button it marks STOCK with the salesID and Updates Status to "SOLD"  this then removes it from the portal

                           

                          The second portal of 'Items' for this sale is linked by Sales2 table to STOCK with STOCK:SalesID=Sales:SalesID

                           

                          It seems to work ok

                           

                          The other question being can i filter the main STOCK portal by say entering data into a field to limit the items showing in the portal.

                           

                          EG  Type Blue into a field so the portal will show all Stock items that are not sold but are  BLUE. ?

                           

                          Just trying ot make it as easy as possible to use.

                           

                          Once again many thanks

                           

                          Mike

                          • 10. Re: Filemaker  Files / tables or portals (Design considerations)
                            philmodjunk

                            Yes, and I figured that might be the next question :smileywink:

                             

                            Are you using filemaker 11 or an earlier version? The implementation details can vary quite a bit here.

                             

                            One trick that works on both 11 and older versions is to include a global text field that matches to a keyword list field in your relationship. (A keyword list is a list of keywords in one field separated by carriage returns.) You can create such lists automatically by defining a calculation field that subsitutes carriage returns (the paragraph symbol "Pilcrow") for spaces in your description field.

                             

                            You can then define a value list and apply it to your global text field as check boxes. When this is used, "or" logic is implemented and you can check multiple values in the check box field and the portal will display any matching records that match any one of the selected values.

                             

                            If that's too fancy for what you need, you can always add a drop down or pop up list formatted field and use it's value to filter the portal and in filemaker 11, it can just be a text box where you enter partial text strings to try to match to different groups of records that contain such a text pattern.

                            • 11. Re: Filemaker  Files / tables or portals (Design considerations)
                              yandg

                              Thanks once again for you reply.

                               

                              Have managed to ge this to work with a 'Sort field' and value list of the data required although i notice the portal doesnt always sort from the very top down.

                               

                              Have tried a couple of different ways and they all, 'sometimes' sort for the item entered from about 4 lines down in the portal. Its not a problem but cant see why it would do this.  All the items, prob about 4 or 5, before the item sorted by, begin with a number or an 'A' which suggests its how the sort is processsed.??

                               

                              Unless i have set up the field wrong?  I have the 'Sort by' field as text but notice the sort will only find the correct spelling. IE it wont sort 'KDL32'  as the field it looks at is 'KDL32EX'  You have to put in the full text.  I am tyring a drop doen box with the value list of the models required so this could well sort that problem.( No pun intended).

                               

                              Mike

                               

                               

                              • 12. Re: Filemaker  Files / tables or portals (Design considerations)
                                philmodjunk

                                To repeat: what verision of filemaker are you using?

                                 

                                I didn't suggest a sort field as this still leaves all the records in your portal. A filter on either the portal or the relationship will leave all records out of the portal except those permitted by the "filter" values.

                                 

                                It sounds like you are sorting a field that contains text. Is it of type text? You can store text in a number field but then it won't sort as expected and you''ll also have trouble when you perform a find and enter critieria in this field.

                                 

                                If this is a text field, keep in mind that sorting a text field sorts the values in alphabetical order, not numerical order. Thus,  if you sort in ascending order , 1000 will sort ahead of 20 as the first character "1" is less than "2".

                                • 13. Re: Filemaker  Files / tables or portals (Design considerations)
                                  yandg

                                  Sorry,  I;m using FM11 from now on.

                                   

                                  Yeah the field I used is as a text field. 

                                  The portal is filtered to show just 'Unsold' items, this works well. If I click/run the 'sell' script the item disappears as it now 'sold' and appear in the list of sold items for the invoice.

                                   

                                  The sort works fine apart from the slight problem I posted previously. Yes i see what you mean re this. I set the field up to sort the records so the first items are the ones we are looking for.  I thought that would be better as our model numbers run on to 'like' models as they go up alphabetically which can be handy sometimes to show these along with their prices.

                                   

                                  I shall try again with the filter options to see what i can get.

                                   

                                  Many thanks

                                  Mike

                                  • 14. Re: Filemaker  Files / tables or portals (Design considerations)
                                    philmodjunk

                                    You might try a filter expression that uses pattern count for partial text matches.