Harry

Hello Board. I may be searching the incorrect terms; but I can't find an answer to my problem. I have built a system, it's running on FMS18 and multiple client users access it throughout the day. One part of the system is a quality check screen at a physical bench/station - items are scanned and labels are printed out as required, records are created and used elsewhere. There are three label formats. Each station has three label printers - Citizen 631's. They are networked printers. I would like to have more than two bench/stations operating this process in the future. Can someone point me in the direction of information that would help me to ensure that the scripts run by Operator 1 send the print jobs to their printers, and the same scripts run by Operator 2 are sent to that operators printers? Bench 1 Operator 1 Printer_1 - Citizen 631 - 90x25mm Label - IP(example) 192.168.2.100 Printer_2 - Citizen 631 - 50x50mm Label - IP(example) 192.168.2.101 Printer_3 - Citizen 631 - 40x25mm Label - IP(example) 192.168.2.102 Bench 2 Operator 2 Printer_1 - Citizen 631 - 90x25mm Label - IP(example) 192.168.2.103 Printer_2 - Citizen 631 - 50x50mm Label - IP(example) 192.168.2.104 Printer_3 - Citizen 631 - 40x25mm Label - IP(example) 192.168.2.105 I can't make two operators work. The only way I can make this work is if I connect the three printers via USB locally to each PC and name them the same as the ones connected to Operator 2's PC (Printer_1, Printer_2, Printer_3), so they can be found by the scripts. It feels as though I'm looking at this solution incorrectly or missing some information. What strategy can I use to make the networked printers operate? My IT support lean heavily towards IP printers, because they can be centrally monitored and controlled. If it's not possible, I can go USB, with a powered hub or other. Thank you!

Ah, right, now *you're* making sense to me. Sorry the post wasn't formatted correctly; yes that was a reply. When I say 'Product', and not in reference to 'Table Product', I'm talking about what you talk about as an Inventory Item. Yes. An actual product thing; something we make or sell. (Which brings a very interesting question - when I say 'Product', what are you thinking about, if not a product....?) Anyway, to (hopefully now) clarify. An Inventory Item has one Stock Code. An Inventory Item also has one Barcode. The Barcode is a unique serial number designated to that particular individual instance of that Inventory Item, whether physical or not. Like a Primary Key, they are never repeated. The Stock Code gets repeated and is shared amongst all the Inventory Items of the same variation, or SKU or Product Code or Stock Item or etc etc. All '15m Green Hoses with Nozzle. have StockCode 'SAME'. And not to confuse things - but the stock code is, yes, represented as a barcode, too. in a lot of the system it's called 'Barcode' or similar.... That's why i don't put the file up; i'm not allowed to post it and it's quite confusing.

Thanks, Lee, for your input. I think, if you read through my posts from the off, you might see that I've actually done pretty much all of the things stated in that topic. I probably have apologise too much; we have V13, V14, and V15, so my info could be updated. Also remained pretty jovial given the 'Your database is crap and you need to start again' rhetoric in the responses received so far! Comment has unravelled my posts many times before; if I scaled those against this one, I'd have to say this new post is massively less complicated. So from my point of view, i'm not sure what i've not answered or what i've not done that is producing the responses. Bruce is telling me that I'm not answering the questions, but I am really sure that I am. I ended up posting the question and then reposting my quoted answers, just to make sure that I had answered them; and I have. I thought. Sorry that I haven't got all the wording and nomleclature correct. I'm sure if I did, I would probably be of greater understanding in this platform and perhaps, therefore, I wouldn't be on 'this' side; but rather, on yours.

I recoginse that it's not explained fully, yes. I have stated that and then further asked for clarification on which questions weren't answered. You keep replying by telling me that it's not comprehensible. But of course, without knowing which bit is incomprehensible, I cannot try to communicate it in a less complicated fashion. Indeed, I tried, but it only served to further complicate the discussion. Therefore, as is obviously the case that my entire description and subsequent answers to critical questions do not serve to enlighten you and Comment, it is not unreasonable for me to suggest that smaller parts are discussed and agreed upon, rather than trying to communicate the entire problem. So i tried to take it question by question, you've stated that I'm not answering the question. Although I see that the answer is confusing, i do not see how. I apologise. How has my answer, above on this page, confused things further? I've taken each individual question that Comment has asked and answered it fully, while still briefly. I understand there's confusion, but I can't see what I've written that is so confusing.

That's my point, the interchange of labels and names to try and find a middle ground where we can communicate correctly. When Comment introduced the Widget and Gadget idea, I thought I had answered his question regarding that very directly and completely. Each Product / SKU has two codes on it. A Unique Code, and a Barcode.

OK, so I did answer the question but used a new word, 'TYPE'. I introduced that word because you introduce the word 'Widget' and 'Gadget' as opposed to using the predefined terms already in use; 'stockcode'. I thought there was a misunderstanding around that building (as I presume you also thought, which is why you tried describing it with different words), so i dual-purposed 'TYPE' to show product or Variant. SKU/TYPE/VARIANT/STOCK CODE are all used to mean the same thing in various systems that we interact with, not through our design; so it must be put into context. I understand that context within a FM forum should remain centred on FM, but sometimes that isn't possible. Often through lack of knowledge on my behalf, certainly. So it goes back to my second post and then Comment's validation of that and suggestion of using Lookup.

Bruce, what critical question didn't I answer? Because, from what you've written and what i've written, my answer is right there on the screen. You actually haven't asked a question that I haven't replied to directly. You're critical question, presented in your example is answered. I demonstrate from my TO graphic and in contextual writing how the data is related and how one can tell which Stockcode the Stock_Take product is. Sorry, BruceR, I just can't see what information I'm not giving you that is making this so difficult for me to communicate.

Just to pick this piece up that you wrote... We do not attach the Stock_Code when it is inspected, no. We know what Works_Order it was created on and we know what Stock_Code was on that Works Order.

OK, thanks for the input. One Works Order is only for one product type. There are never Works Orders with multiple product types (different stock codes) on our system. (This is where the Kanban/Kamban nomleclature came from.) [A Works Order is only for 12 x StockCode OR 200 x StockCode OR 5000 x StockCode.] The 'Works_Order' table is the parent/header table. The other data it uses is not solely for Worksorders or any other table, it is shared. So it does not form a strict Parent/Child relationship, in the same way an Invoice_Header would have an Invoice_Line_Items child table. When the Operator does the Inspection, they do not need to know what that product is, strictly, let alone enter it manually. IRL, they scan a printed report, the Works Order table, which enters the Works Order Number into the Inspection Table. The relationship between the Works_Order table and Product table pulls through the product description and other associated data.

Wim, Thanks for your message. I can't start again, these are the tables i need to use. They are incumbent within my systems, i can't just start again.... It's not 'convoluted' in a database schema way. It's an SQL schema that's been built with strict relationship parameters; that is, table 'worksorder' only includes things like 'WO Number' and 'Stockcode'. The description for the product of 'Stockcode' isnt even in the 'product' table, it's in 'Product_Details'. That's not convolusion, it's just data integrity. I certainly appreciate all you guys looking at my problem and I'm also sure I'm looking at it incorrectly; which i thought i'd explained. As Comment stated, copying the Stock Code data into the StockTake table makes the whole thing work. But the more i've learned about Relational Databases, the more I'm sure that we shouldn't be copying data from one table to another, just to make the realtionships work; it's defeating the purpose, or seems to be to me. I will make another database with simplified tables to show and talk about; which certainly may help me to see it more clearly. Thank you. Here is a replication of the tables with the labelling (table labelling, not physical product labelling) simplified. I've also taken out the fields etc that don't apply. Sorry this wasn't done before. The last time i presented this board with simplified TO's in a diagram, i was asked to provide the full information, with all the other fields contained so that the posters could make sense of it. What I'm trying to do is get a count each TYPE of Product in table 'Stock_Take_Scanning', designated by and grouped by 'Stock_Code'. I would need to self join to Count, but the Stock code is only available in table 'Works_orders' I feel like I should have a field in 'Stock_Take_Scanning' called 'Stock_code' that is a CALC field that equal something like 'Get(Works_Orders::Stock_Code)' and it would just relate through, then i could attach the Stock_Take_Scanning_Self_Join via the Stock_Code and it would COUNT.

Wow, Bruce. ... tough week? No need for that sort of tone old chap. You can't really thing i'm being obtuse on purpose? You don't really think i'm trying to not provide you with the information? Do you really think that i've taken screen shots, written and formatted those posts in a way so as to obscure the point i'm trying to make and therefore discourage discussion and expert opinion? Or perhaps, just possibly, i'm on a help forum because I do not fully understand my problem and therefore some of that misunderstanding could have been miscommunicated as part of the problem itself? Anyway...... I have answered the direct questions; I'm sorry if I didn't make it clear enough. No, I did NOT describe a TABLE that actually contains the information. I DID however describe the RELATIONSHIP that contains the INFORMATION. That is, because, the information is not in one table, but contained within the relationship; across several tables. The products are "Labelled", physically labelled with a printed label. They have two barcodes on that label - a Unique Barcode and a Non-Unique-Barcode. Those two sets of data do not reside on one table, they are in a relationship through tables. The screen shot at the beginning of my post detailed how that information (The non-unique stockcode) is related to the unique stock code. It's not amazingly clear because they are old legacy tables that aren't nicely labelled and don't convieniently contain just the fields i'm discussing. Again, an apology if this isn't clear; I am trying to explain what's going on without building in seemingly unnessecary detail.

Hi both, Thanks for taking a look. I honestly didn't think it was that complicated to explain. Our products come off our production line (Inspection) labelled like this: 1. Product Code: 12345 UBarcode: 0000076354 2. Product Code: 12345 UBarcode: 0000086332 3. Product Code: 12345 UBarcode: 0000033812 4. Product Code: 12345 UBarcode: 0000097493 5. Product Code: 12345 UBarcode: 0000091123 But they were scanned (StockTake) like this: 1. UBarcode: 0000076354 2. UBarcode: 0000086332 3. UBarcode: 0000033812 4. UBarcode: 0000097493 5. UBarcode: 0000091123 Product Code is not held directly in the 'StockTake' table. Thank you honestly both for taking a look. I've got some reading to do. Thank you for suggesting the fix - i copied the Stock Code data into a field inside the table Stock Take via a scripted routine. This could be part of the import routine when a user plugs the scanner into their PC and runs the Filemaker script to show the reports. It uses a relationship to get the related field contents and set the Stock Take table with that data. Obviously, because it's native to the table, i can self join and it sort really easily; of course. I was presuming there would be a way to build a M2M setup, where I would build a relationship to get the Stockcode into a table, then self join itself to that relationship in another TO..? Too much? Perhaps like summarising totals into a table 1 field deep, with loads of TO's related in different ways to give live summary calcs? Anyway, thanks again, it is appreciated.

So, from reading the barcode from that stock take - the stock take table has been created. Someone went and scanned Widgets and Gadgets with a handheld scanner and it built a csv inside with the Unique Barcode (UB...?) in one field and a timestamp on another. I imported that data to StockTake Then, relate the StockTake::UB to our table called 'Inspection'. This has all the products that have come off the end of the production line in it. It has the UB code, the 'creation date' timestamp, the Works Order number it was created under and a few other details and fields. StockTake::UB to Inspection::UB then, to get the Stock Code, i will need to access the Worksorder Table; through Inspection::WorksorderNumber to Worksorder::WorksorderNumber the Worksorder table has the StockCode in that will tell you if its a Widget or a Gadget.

Hi! Yes, each StockItem has a unique barcode. There would be 5 unique ones in the 5 records in StockItems. Each Product has a barcode. Products table would have two Barcodes in it. StockItems would have two Barcodes in it, attached to the two products. Or, We make 100 x Model A and 100 x Model B. There are 200 'unique barcodes', 2 'product barcodes'.

OK... I'll try and not start again, sort of... Basically, I'm trying to do a Stock Take here in our warehouse, and get an aged stock analysis, too. All my stock is serial numbered, that's the Barcode that's been scanned. That data/table was created in Filemaker, partly, as the 'Inspection' table. When our products are made, they are checked and then its barcode is scanned and a record is created. I created the Stock_Take table from physical scans in our stock room with a little wireless scanner. SO i have a bunch of Serial Barcode Numbers and Timestamps. Each Serial Number relates to one of two products (In this instance, only two shelves were physically scanned to test this process). That data is held in the 'Inspection' and 'Worksorder' table. If I script a copy that will put the 'Stockcode' field from 'Worksorder' into 'StockTake' tables, I can easily do a Self Join on StockTake and get this information. But, isn't here a way NOT to copy the data? Am I trying to do something that I shouldn't? Should I just copy the data over into the StockTake table? Thanks for keeping with me here.

it's not really, it's just a 72dpi res vignette of a graphical representation of two systems created from two completely different requirements by two sets of people 15 years apart coming together for a purpose that was never intended and me trying to communicate that over the internet using my fingers as the main info bus.

Really? OK, thanks for taking a look anyway... It's quite a tricky one though, eh?! Other than scripting a lookup, to copy the Stockcode data into the Stock_Take table, i can't make this work> But i just feel that it should work, all the data is there and there are other problems like this i would like to solve. Thanks, Harry

There's no relationship... because there's nothing to relate it to... there's no matching field between those tables; all the data is there, but not in the one table, that's my problem. I can't get my head around how to fix it. I need to link the two together so I can count the totals from our Stock Take. A self join of Stock Take. To explain, the image is a set of several TO's used in a much bigger FM system. We are manufacturers, primarily, the concept of Kanban/Kamban is used in that arena and can basically mean a 'works order'. The differences are crucial at a detailed level, but not here. The 'worksorder' table is from an external SQL source and its schema is modelled on another external ODBC system - Sage 50. We've moved to SQL Sage 200, so we have a rolling program of standardising the schema which is working. It's a lot better than it was, shockingly! 'fixedworksorderid' is the 'Works Order Number' that is given to the record.... It's part of a scheduling app on our SQL we wrote to 'suggest' a list of Works Orders (in Kanban order, but lets not get into that). So the 'Works Order Number' would actually be a floating point that changed based suggestions and only until a human called for production and set in motion the WO, did the 'fixedworksorderid' field populate with something like '12345' or '12346' or '12347'.

So to Count the instances of StockCode in TO StockTake, i put that CALC field as 'Stock_Take::StockcodeCOUNT' COUNT ( worksorder_Stock_Take_COUNT::stockcode ) Even though Stock_Take and Worksorder aren't linked by StockCode? I'm blinded by the simplicity, perhaps; how does it know to count based on StockCode?

I tried making Stock_Take::Stock_Code to be - GetField ( worksorder_Stock_Take_COUNT::stockcode ) I've made sure I'm referencing the correct TO's, too. I'll check now, though. But it returns a '?' Either way - is this the right way? To echo the data through to the field? Then, i can do another self join and COUNT that stockcode occurances. Or, is there a more logical 'database' way to do it?

Here's a screen grab of the layout. field -'fixedWorksorderid' is the Works Order Number. (Edit to add: in this grahpic, Stock_Take::Stock_Code and Stock_Code_Count are empty fields i'm trying to propergate. Harry

I actually can't see how this can be done... Should I copy some of the information from one table to the StockTake table? I can't put a 'StockTake' table on the end of this relationship, because there's nothing to join it to... It feels like i need to copy StockCode from the 'Works_Order' table to a new field in the 'Stock_Take' table. Then i could easily self-join. But that doens't feel right to me.

FMPA15/FMS14 I have Three tables StockTake Barcode_Serial_Number Timestamp_Of_Scan Inspection Barcode_Serial_Number Works_Order_Number Works_Order Works_Order_Number Stock_Code StockTake is a log of someone scanning unitque Barcodes and their timestamp. Inspection is the 'end of line' scannign machine when teh product is booked into stock. Works_Order is the list of Works Order Numbers and the stock code of the product. I don't undetand relationships enough; so some guidance on where i should be reading would be appreciated. I need to do a few things, but they all centre around this problem. I need to, firstly, Count the number of time a particular Stock Code has been scanned in the StockTake table. It seems that I need a Self Join, to count. But the data I need to Count isn't in the first join of the relationship; it's on the 3rd table deep. In the Relationship dialog box, I can't change the drop down boxes to look 'through' tables to see the related fields behind them and do Calcs on them. Does that make sense? How do i do it, what I am thinking about wrong? Thanks!

OK, that looks like a table our Sage developers created for migration.... thank you!

The tables are listed as 'dbo.StockItemUnit' for example. They all have the dbo. prefix in their name. My SQL developer is seeing a table marked 'STOCK' but I am not seeing it at all on the list in FM via External Data Sources or through Specify Table in the Graph.