Increase part count in Sql table

Ignition
1

I am looking to do an increase in to a part-built table in one my active orders. I can each order have a specific ID which will help me determine what order I am on but I'm curious the most efficient way of increasing the part count in the column ever time a good part comes of the production line. Thanks

2

How many parts per minute / hour day / year do you make?
Why would you not create a new record with a timestamp for each?

1 Like
3

An update query. But I am sure you knew that. What question are really asking?

  • The most performant way?

  • The best way to run the update when a new part is detected?

  • How to write an update query?

What have you tried? Did it work? If so why is that not good enough?

4

So, what i have is a scheduler software that puts a schedule in to a sql database then with the part number, orderID number, quantity ordered and total built of order. So, what I have in the PLC end is the order number and I increase the Total built until it hits the total ordered quantity then goes to the next order. I want to Increase on the sql side so when i run a query i can see where they are at in the order.

5

It sounds as though you just need to read the current count from the PLC with an OPC tag read.

If the PLC tag doesn't reset at the start of a batch then,

  1. Read the PLC current count with a regular OPC tag.
  2. Create an SQL tag to read the count at the end of the last batch.
  3. Create an expression tag to calculate the difference between the two.

Tag 3 gives you the current batch count.

6

The Order ID will change at the end of each order, and the order count will reset to zero

7

So why does the order count tag not give you the required data? Why put it in a table - unless you need to record it when the machine is powered off when everyone goes home and the batch isn't finished yet.

8

That's exactly why it always seems like they shut the line down during a order so I just want another level of protection. We have had it where a download was done to the plc, and it all got wiped out.

9

Then you just need an SQL table to store miscellaneous pieces of data such as this and write to it to update the record. I'd be inclined to do a new INSERT each time the count changes - if there aren't a ridiculous number. That would allow you to extract all sorts of useful information such as average rate, time taken per part, etc.

https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate?src=sidebar

To get the lates count, create a named query and extract the last row. In MySQL it would be something like,

SELECT t_stamp, count
FROM production_log
WHERE machineId = 'Machine 7'
ORDER BY t_stamp DESC
LIMIT 1
1 Like
10

I will try this thank you

11

Select {[~]ActivePallet/Data/Pcode}, Built
FROM View_ActiveOrders
WHERE TargetLineId = 50
Limit 1
This is what i am trying for my sql tag but it doesnt seem to work

12

I don't think that you can modify column names in a query tag like that, (I have never tried personally as not the best practice).

I would expect your query to look something like:

SELECT Built
FROM View_ActiveOrders
WHERE Pcode = {[~]ActivePallet/Data/Pcode} AND TargetLineId = 50
LIMIT 1

Note that this is a scalar query. This is needed if the result that you are expecting is a single value.

13

Please explain,

  1. What database engine your system is using. (MySQL, MariaDB, MS SQL, PostreSQL, etc.)
  2. Where you used this code.
  3. What you expected.
  4. What you got.

Tip:

Formatting code
Formatting code741×191 17.4 KB