Filling a table from 2 other tables

General Discussion
1

I have a project I am working on that involves matching employees from one table with positions in another table and then putting that information into another table entirely. I have 3 separate shifts. Each shift has the some of the same departments in it. What I am trying to do is when a department button is selected the DepartmentTable populates with the required employee info and the positiontable populates with the positions based off what department is selected. I need to be able to have the position from the first table and the employee info from the second table be dropped into the third table and then have a submit button put the information from the third table into a SQL db and clear out the third table.

Or if someone knows of another way to do this in ignition I am all ears. I am attaching a screen shot of what I am trying to do.

Capture
Capture1208Ɨ773 28.3 KB

2

Personally, I donā€™t know as Iā€™d use the 3rd table. I would use the selected values from the tables to create a confirmation popup when submitting to the database. That same submit script can check that both tables have a selected value before doing anything else and throw an error if one needs to be selected. I would think this would be the cleanest option.

Unless you want to be able to submit multiple entries at once though, having the 3rd table isnā€™t needed. If you do want to do multiple entries then you would need a button to tell it to move the selections to the 3rd table and another to remove any incorrect entry. I would use a custom property looking at the selected row from both tables to see when both are not equal to -1. When they arenā€™t then I would make the move to 3rd table button visible. With the remove button I would only have it visible when an entry in the 3rd table is selected.

There are a lot of different ways to do everything though, it comes down to personal preference. If you donā€™t like either of the ideas above you can still do what you described by setting a custom property that looks for when both tables have selected row values not equal to -1. Do a property change script based on that custom property. In the property change script you can create your dataset for the 3rd table. I would either have a dataset with just the header stored in the table when nothing is selected or hide the table until something is selected. In your submit button I would set your table one and two selected row values back to -1.

3

Yeah, it would need to be multiple entries. Basically, coordinators of each department would login and select their department, from there they would select each individual position and ā€œassignā€ an employee to it from the second table and then submit each position and employee to the database as a whole. I would think doing each one individually would be very time consumingā€¦

4

Thatā€™s why I tried to mention options. So with that I would do the second option and have the button to move it to the table. In that button I would pull the selected values from the 2 tables then use the system.dataset.addRow() function to add it into the dataset for your 3rd table.

The part that will get complicated is scripting your insert/update query when you submit. Iā€™m assuming you would want to check to make sure the entry doesnā€™t exist currently so you would need to do a query to compare what your putting in to what already exist. Then create your insert/update queries. You can have it loop through doing the inserts individually but that will take longer and hits your database more. I would have the script build your insert/update queries dynamically so you can limit how many times you have to hit the server.

1 Like
5

I would make the position table with extra columns for the employee name / number. Then drag/drop from the employee table to the position table. Example attached.

table_drag_drop_2020-07-20_1620.zip (14.7 KB)

Position table will highlight duplicate entries.

2020-07-20_16-21-22
2020-07-20_16-21-22762Ɨ542 21 KB

1 Like
6

Taxed.
Do you ever sleep?
Pilgrim. :cowboy_hat_face:

2 Likes
7

Sleep is for wimps and mamaā€™s boys.

4 Likes
8

Do I use Ignition to open a .json file type? Sorry I am fairly new to this side of ignition

9

In the designer:

  • File ā†’ Import
    2020-07-21_8-59-27

  • Pick zip file
    2020-07-21_8-59-39

  • Choose what you want to import (I used a window file name I was relatively sure you werenā€™t using :slight_smile: )
    2020-07-21_9-00-01

10

I swear, I am having the worse time. I tried importing what you had in the zip file but the only selection it allows me is a .proj file type.

image

11

So I just realized you are using Ignition version 8.0 and I am using Ignition version 7.9.11. My version is on a winows server so I downloaded the free 8.0 verison on my desktop. and was able to access it.

Thank you for that! One question i have though, is that my position table pulls the positions listed from a sql query based off which department button I am pushing. Is that still possible to do like that if I add the additional columns in the position table?

12

Sorry about the version mixup. Glad you sorted it.

It's certainly possible. The easiest way would be to adjust your query.

Be sure the polling is off, or it will overwrite whatever is in the table when you drag into it. Been there...

Your query would look something like:

SELECT position, '' as name, 0 as employee_id 
FROM my_table
WHERE department = {Selected_Department_Property}
13

Okā€¦ so I added additional columns to the position table that is manipulated from my department buttons and once I selected a different department all the other columns I had created disappeared. So Iā€™m not sure what to do at this point.

14

Not sure I understand, this is what I currently have:
event.source.parent.getComponent(ā€˜PositionTableā€™).data = system.db.runQuery(ā€œselect Position from HumanResources.dbo.AutoDefectingPositionsā€)

15

There are many ways to do things in Ignition. You just picked a different one than what I was expecting. :wink:

event.source.parent.getComponent('PositionTable').data = system.db.runQuery("select Position, '' AS Name, 0 AS EmployeeID from HumanResources.dbo.AutoDefectingPositions")
16

Unfortunately, doing this removes the addtional columns I created in the table in ignition. it only has the orginal position column.

17

okā€¦ so if I ā€œcreateā€ the columns through the sql query it works. Weirdā€¦

18

Nevermindā€¦ once I added the additional fields for columns it would not work.

event.source.parent.getComponent(ā€˜PositionTableā€™).data = system.db.runQuery(ā€œSelect Position, ā€˜ā€™ AS EmployeeID ā€˜ā€™ AS FirstName, ā€˜ā€™ AS LastName, ā€˜ā€™ AS Shift, ā€˜ā€™ AS Department from HumanResources.dbo.AutoDefectingPositionsā€)

19

Youā€™re missing a comma after EmployeeID

20

LOLā€¦ long day!!

1 Like