Jump to content

Harry

Members
  • Content count

    157
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Harry

  • Rank
    member

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

1,777 profile views
  1. Harry

    Calcs on Fields deeper in relationship

    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.
  2. Harry

    Calcs on Fields deeper in relationship

    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.
  3. Harry

    Calcs on Fields deeper in relationship

    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.
  4. Harry

    Calcs on Fields deeper in relationship

    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.
  5. Harry

    Calcs on Fields deeper in relationship

    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.
  6. Harry

    Calcs on Fields deeper in relationship

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

    Calcs on Fields deeper in relationship

    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.
  8. Harry

    Calcs on Fields deeper in relationship

    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.
  9. Harry

    Calcs on Fields deeper in relationship

    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.
  10. Harry

    Calcs on Fields deeper in relationship

    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.
  11. Harry

    Calcs on Fields deeper in relationship

    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.
  12. Harry

    Calcs on Fields deeper in relationship

    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.
  13. Harry

    Calcs on Fields deeper in relationship

    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'.
  14. Harry

    Calcs on Fields deeper in relationship

    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.
  15. Harry

    Calcs on Fields deeper in relationship

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

Important Information

By using this site, you agree to our Terms of Use.