• Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About sal88

  • Rank
  • Birthday

Profile Information

  • Gender
  • Location
    United Kingdom

FIleMaker Profile

  • FM Application
    15 Advanced
  • Platform
    Windows 8.1
  • Skill Level
  1. Unfortunately we've had to continue with a type of mishmash setup. I believe the main issue here is that the organisation in question is not actually a 'seller of stock', i.e. a sold item might not be shipped, a shipped item might not be sold. A shipped item will not find it's way back in to stock via a returns procedure as it might be returned to the warehouse before it has actually been sold. As I mentioned, we have engineers going to the customer with items that may or may not be sold to the customer. Furthermore we do need to stick to selling Items as per their individual purchase costs - there's no need for us to be setting sale prices per product which is what I initially thought. Therefore all we have effectively done is just add the 'Products' parent table to the transactions (which are actually stock lines) so that the stock manager can more easily see what is going on. Thanks for your help all the same Wim! I've learned a lot about how it should work!
  2. That's a business rule and for the valuation of the stock itself it is an accounting rule (things like LIFO / FIFO,... if those don't ring a bell then check with the accounting folks). Computing the purchase cost based on the historic purchase cost is just a matter of putting the right formula together. According to FIFO, would the following logic be correct (and the calculation achievable): The quantity at hand is 10 The latest IN transactions are: 20 @ £10 (January 2016) 5 @ £11 (June 2016) 3 @ £12 (September 2016) Therefore the purchase cost I will be pulling in to the sold Line Item is: (3x£12 + 5x£11 + 2x£10)/10 Not sure I follow the "But obviously..." Yes: you definitely need to create an inventory out record, but only when the item is shipped, not when it is sold. When it is sold you need a script to update the 'qty sold but not shipped' so that users can see both how many are in stock and how many have been 'committed' already. Then when the item is shopped, an inventory out record is created and the 'qty on hand' and 'qty sold but not shipped' is also updated. Excellent. What I meant is that the two stages are separate, for example: 1. 5 Items of the same Product are sold via a LineItem record 2. An OUT inventory transaction is made when it is shipped One potential difference here is that many Items are not actually shipped, they are taken with the engineer onsite (a customer's premises) - at which point the status of the Line Item is changed to 'checked out'. If they return with the item then the Line Item is deleted, but if it remains on site then it is sold and the delivery status is changed to 'delivered'. Also, it isn't uncommon for an engineer to go on site with a number of items - not knowing whether they will use them all. therefore a number will be returned to stock. My instinct is telling me that the best thing to do is stick to the inventory in/out process when things are returned, and for engineers to sell things only when they are actually sold. So they would take things to site (via an inventory OUT transaction) and then selling that which is remains on site, and then returning them to stock via an inventory IN transaction. But then it's important that the purchase cost is included in inventory IN records. Does it make sense for inventory IN to be used for such returns? Would I include a 'returned from site' tickbox - whereby its population automatically pulls in the purchase cost via a slightly modified version of my above FIFO calculation. But then if Items leave the stock room before they are sold, it is more complex to record the serials in the Line Item. Sorry for yet another curveball! I think this is the last.
  3. Sorry I keep forgetting that point! I guess up till now we have 'sold' such stock to our company record (which never gets invoiced) - but the method you state is much more rational so we will try and migrate to that. Not having to check stock each time items are sold/converted from a quote would make my scripting life much easier! But how would the person who has sold the customer an Item know whether it is in stock, (either on back order or completely out of stock)? How would we know it needed reordering, and how would we know what date of delivery to promise the customer? What if it emerges that Product cannot be reordered from the Supplier? There are bound to be a few Products that are one-offs e.g. custom made servers, how would the end user know that these are not be sold anymore? How would we know what the purchase cost is, given that there are multiple inventory lines per Product (each with slight variances in purchase cost)? At the moment we are thinking of having the sale cost manually inputted by our stock manager - short term price fluctuations doesn't bode well for one thing. But up till now we are able to work out exactly how much profit is made per invoice, because each Line Item pulls in the stock purchase cost. Is there still a way of achieving this? I was thinking it pulls in the average purchase cost of all remaining cost (but i can't picture how this would be ascertained). Is it common practice/necessary to know at the Line Item level the true purchase cost (and therefore the profit). Understood. I'll make sure we refer to it as inventory in/out. That's right, I no longer see the point in noting down mfg. serials as they come through the door. All that needs checking is that the quantity of the expected product has arrived. Then when we take out items from stock to deliver to a customer the mfg. serials are recorded via barcode scanner in to a single field in the LineItem record. Therefore we wouldn't need the 'Stock Individual Items' table (the right most portal on the layout screenshot). Serial tracking is just for customer warranty purposes so they have proof of purchase date. Ah OK i was unsuccessfully reading between the lines there. The reason I have a 'current qty' as well as qty at hand is so that it is known whether a stock item can be sold (an issue that I am unclear about as per the above queries), i was assuming 'qty at hand' just adds up all 'IN' transactions and then current qty subtracts from that the sold Line Items (which would be based on an incorrect relationship). So, 'qty on hand' effectively minuses all inventory INs from all inventory OUTs. Is it a calculation or is it updated via script on inventory in/out transactions? I think we would need the 'sold but not shipped' field that you mention. How would this figure be ascertained? Wouldn't it require a delivery status field on the Line Items (and a relationship between the two :S (?) ) So I take it that when an item is physically removed from the warehouse (for whatever reason, it might be sold, or donated), we create an 'inventory out' transaction? But obviously if it is sold then there is ALSO the Line Item record that has already been created. The sold item record is in the LineItems table - Invoices are essentially a list of these records (doesn't really 'pull' it in). So to rephrase: When creating a LineItem, the related Product's 'sold qty' field is modified - the quantity specified in the LineItem record is added to it. Overall I can understand the separation of stock + selling processes and I can see how it will make life easier for everyone, however in ascertaining accurate profits and knowing when to reorder and when to sell I am a bit stumped. Thanks for your help Wim!
  4. OK having looked in this further I think I can see things a bit clearer. Does this set up conform to the standard? We will do away with the individual items table. So we are just left with Products and Batches. The products table has a 'qty at hand' field which is a calculation that sums related batch quantities. It also has a 'sold qty' field, and a 'current qty' auto-enter field which subtracts 'sold qty' from 'qty at hand'. When a supplier shipment comes in, this is added as a new batch record. Qty and purchase cost are recorded, as well as supplier details and order no. When selling items the quantity that is being sold is added to the 'sold qty' field in the Products record. This sold item record is what the invoice pulls in. An item cannot be sold if the 'current qty' of the Product record is zero (or below the required qty). When selling items the purchase cost is pulled in - this is the average purchase cost of all remaining batches. This is worked out using the Product current qty field and the most recent batches' purchase costs (not entirely sure what this calculation would look like). When sold Items are leaving our stock room the manufacturer serials numbers are individually scanned in and recorded in the sold Item record (the one that appears on the invoice). Am I right in thinking that what is missing from here (as far as the standard arrangement goes) - is an 'inventory out' transaction record every time items are removed from the stock room? I can tell that long term this is the right thing to do but I can't get my head around why!?
  5. Surely they are related though? Whatever the arrangement, a line item on an invoice must include the stock ID to which it refers? And not just for the benefit of the recipient of the invoice, but so that there is a means of seeing to whom a stock item was sold? (I've a feeling the answer is geneally no?) Also: inventory movement records - what are these? Are these just records that refer to an incoming delivery of stock from a supplier?
  6. Having discussed this at length we have the following structure and basic layout of a single stock (we are calling it product) as per the attachment. What we really need to do is track precisely which individual item has gone where, so each of the 'Items' records has the manufacturer serial and is referenced somehow when the items are sold - I haven't seen this in any examples. However what we would also like to retain is the ability to sell products with a quantity above 1, as a single line, even if it spans multiple batches. In order to track precisely which individual items were sold, e.g. 25x 2.5 SAS SATA HDD Tray Caddy Would this require the use of multikeys? Is this a sensible approach to stock management? Thanks
  7. No joy unfortunately. I've just resorted to including the category the records belong to as part of the path (the final subfolder being the category ID).
  8. Hi all Fairly trivial (but annoying) problem here! I've just transferred all Invoice PDFs to their respective container fields - which is externally stored (non secure) on the FMS Server. However the first PDF in the table had an '_1' at the end of the filename on the server. When I cleared the field and re-added the PDF it then has '_2' at the end, and so on. The underscore disappeared when I transferred the container from 'Invoices/' to 'Invoices2/' but the problem remains when I transfer back to 'Invoices/'. It doesn't happen to any other PDFs/records, just this first one in the table. Any ideas how I can 'reset' this? MT
  9. Hi all Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null. So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records. SELECT COUNT (*) FROM Cases C JOIN Personnel P ON p.personnel_ID = c.User_IDF WHERE c.Closed IS NULL AND p.exclude_stats IS NULL Many thanks
  10. Ah I see. We have quite a rudimentary system by comparison, Instead of an inventory IN process we simply have a single line in Stock per every order that comes in from our suppliers, with an initial qty specified - there is no parent table. If an Item is sold then it is implicitly moved out of stock (though we do now have delivered/waiting/out for delivery statuses, but these are simply so we know where items are and what is waiting to get shifted). One reason for this may be because we don't invoice as soon as Items are passed to the customer, invoices to all customers go out just once a month. Other than that I can see we may have to move to the more standard approach that you mention. For now I will have the script amend all qty fields, and I will look in to a revamp. Is there an article you can recommend so that I can learn about the fundamentals of inventory systems? Thank you for you invaluable help Wim.
  11. Understood. Couldn't my stock::current_qty be a calculation that minuses the stock::sold_qty (which is added to/subtracted from when adding/deleting LineItems) from stock::initial_qty? Returning stock to the manufacturer isn't an issue (or at least it hasn't been!). Or would having such a calculation, even though simple, defeat the object? Is the idea to modify both current_qty and sold_qty during the script?
  12. Instead of having the script modify the current qty field would it be ok for it to modify the sold qty field? This would make things simpler as sometimes we loan items instead of selling them, in which case the loan qty field would be populated. Also, in Todd Geist's example, he uses an exit loop if $PortalCount = $ItemCount step. Can I just utilise the exit after last option within the Go to Portal Row step?
  13. Hi all This has been a well discussed topic however I have struggled to find a direct answer to: Should the 'currenty qty' field in my stock table be a calculation that refers to related line items, or should it be modified via script when related line items are added/deleted to Invoices? I have looked at Todd Geist's excellent Inventory Transactions tutorial and am very excited about implementing it for this and other functions. For one it would mean I could remove a constant server script that hides/unhides stock items (from the stock list that appears when users are creating line items) depending on whether the quantity is zero or not. To be honest I'm a bit surprised that the portal that is used to display stock items that have a quantity above zero can't just refer to the calculated quantity (I'm guessing i opted for the server script method because the alternative was too slow or it was unable to see the quantity given that it was an unstored calculation). I believe that in general things would be sped up too (at the moment there are just 3000 stock records and 9000 line items). However I am partly confused as looking at FM15's Invoicing starter solution it would appear that a calculation field is used. Which path should I take? TIA
  14. Only took me 2 years to notice this error In the case of Wdays = 1 and not startIsWeekend it was indifferent to whether the current time is after 5pm. Therefore instead of Case ( startUntil9 and ct>~9h ; ( ct - ~9h )/3600 ) & Case ( startB9And17 ; ( ct - StartTime )/3600 ) & Case ( startUntil9 and ct ≤ ~9h ; 0 ) It should be Case ( startUntil9 and ct>~9h ; ( if (nowFrom17;~17h;ct) - ~9h )/3600 ) & Case ( startB9And17 ; ( if (nowFrom17;~17h;ct) - StartTime )/3600 ) & Case ( startUntil9 and ct ≤ ~9h ; 0 ) & Case ( startFrom17;0)