sal88

Thanks ggt667. My script is definitely activated via PHP however the send mail step is not working at all - either via plugin or the inbuilt send mail (smtp) script step. Am I missing a trick?

Understood, thanks @ggt667. Out of interest, is there a specific way for filemaker server to perform the script but without any such instruction from PHP? i.e. could it trigger when a new record has been created (of which the 'type' field would be set to 'web') and then perform the script? Or would this require a plugin or scheduled script that runs every minute, checking for newly created web originating records?

Thanks very much rwoods that looks to be the 'ticket', I will pass this on to the web developer but if there's any problems i'll be sure to contact you. ggt667 I'm not sure what the difference between spread point and starting point is. The customer who is using the web portal is adding a query/information (as a new related record) to an existing support ticket. Our engineers can see within filemaker that the ticket has been updated (if they have their list of tickets on the screen), but it's more efficient if they can also be emailed a notification of the update as soon as it occurs.

Hi all We have a web based tickets portal which creates new messages in our filemaker database - this is for customers' use. It does this using the PHP API (the website was developed by a third party). We would like the owners of tickets to be notified when the customer has added a message to one of the tickets that they are in charge of. Is there a way for filemaker/filemaker server to trigger a 'send email' script when a new record is created via the web portal? Or would it be simpler for this action to occur on the web side? Many thanks

Hi all I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are: Clients Client_ID, Seats Jobs Client_IDF, Job_time, Job_Date The best I've managed so far is to find the distinct Client_IDF from all Jobs: ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line Or to do something similar but from the Clients table: ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL. Is this possible in FM SQL? Many thanks

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!

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.

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!

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!?

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?

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

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).

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

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

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.