Calcs on Fields deeper in relationship

[Harry]

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!

 

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

 

[Harry]

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

 

Edited October 6, 2016 by Harry

[Harry]

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?

 

Edited October 6, 2016 by Harry

[bruceR]

You do not need GetField.

It is doubtful that you need to do this at all; but if you ARE going to do it you would just use:

worksorder_Stock_Take_COUNT::stockcode

The reason GetField returned the "?" result is that getField is looking for a text string that is an accurate, full field name.

Example:

GetField( "Contacts::FirstName" )

[Harry]

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?

[bruceR]

No, of course that won't work.

And; what would seem the obvious question: why is there no relationship?

But overall, I find your diagram impossible to understand without more detailed description.

A Kanban (storage location) related to a work order ID? What?

[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'.

 

 

[bruceR]

Well; for me; completely incomprehensible.

[Harry]

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

[bruceR]

No, not tricky. Incomprehensible. Disorganized. Illogical. Scattershot.

[Harry]

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. 

[bruceR]

You want a count of something somewhere and want to store it somewhere, quite possibly in the table you declare has no connection to the source, and you propose GetField as a solution, pointed to a single irrelevant field, and then declare you think it will do a count when we remove getfield or maybe it won't. That's what we have so far.

[Harry]

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.

[comment]

I am afraid this is not getting much clearer. Let me start with this:

Suppose you have two products, Widget and Gadget. That would be a table of Products, with 2 records.

Next, you go and take an inventory count. Say there are 2 widgets and 3 gadgets. That would be a table of StockItems with 5 records.

Now, you say that each stock item has a unique barcode? Or do all widgets have the same barcode, and all gadgets another one? If the barcodes are unique, you need to tell us how one can tell from reading the barcode if the product is a widget or a gadget.

 

 

[Harry]

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

[comment]

4 minutes ago, Harry said:

Yes, each StockItem has a unique barcode.

1 hour ago, comment said:

If the barcodes are unique, you need to tell us how one can tell from reading the barcode if the product is a widget or a gadget.

 

 

[Harry]

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.

[comment]

Sorry, I am not able to follow this and cannot spend the time it would take at this rate.

[bruceR]

Same.

[comment]

I will say one thing, though. I don't know where you want to take this, but I am quite sure that if you want to know how many units of Product X you had on date Y,  you need to start by getting the product SKU into the StockItems table - preferably by a lookup, so it can be a stored field. That would enable you to summarize the StockItems table by date and by SKU, as well as define a relationship to the Products table matching on SKU (and optionally a date), so you can count from there.

[Harry]

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.

[bruceR]

"Thanks for taking a look. I honestly didn't think it was that complicated to explain."

I suspect that it is not difficult to explain. But you just can't bring yourself to do that. To actually answer direct questions or provide meaningful explanations.

Just now, you describe how products are "labeled". You do NOT describe a TABLE that actually contains this information.

[Harry]

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.

[Wim Decorte]

6 minutes ago, Harry said:

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.

 

Then why not start by making that so?  Give yourself a fresh start instead of fighting with a convoluted structure.  Bruce and Comment are trying to help, including trying to reset your thinking about this and urging to provide the right info in the right lingo.  That will help your thinking down the road.

[Harry]

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.

[bruceR]

In the usual way of thinking about work orders, the table you name as Works_orders would be named work_order_items and there would be a parent Work_order table. 

Continuing, we would expect one work order to quite possibly include many items. Work order 1234 is for 12 Widgets and 73 Gadgets.

If that is the case your system CANNOT, ever, give you the results you are looking for because you do not record the SKU when you generate the item barcode and attach it to the product.

Your production system is deficient.

I would think that when the inspection record is generated, the inspector would know what product he is inspecting and should be able to correctly identify it and enter the SKU at that time. 

[Harry]

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.

 

[bruceR]

Lacking that, your original statement is correct. You CANNOT relate the data and you must declare to whoever is asking that the data system is deficient and there is no way to value, count, or age your inventory. 

[Harry]

2 hours ago, BruceR said:

.... because you do not record the SKU when you generate the item barcode and attach it to the product.

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.

[bruceR]

And once again you do not answer the critical question presented by my example.

[Harry]

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.

[bruceR]

You keep intermixing your language. Now you introduce product TYPE. Usually that indicates several different products.

Product TYPE = garden hose. There are 10 foot, 25 foot 50 foot products, all of product TYPE garden hose but all with different stock codes.

If a single work order produces only a single SKU then Comment's suggestion to use a lookup applies.

[Harry]

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.

[bruceR]

Comment introduced Widget and Gadget in his original attempt to get you to clarify the problem.