Help Troubleshoot Difficult Task

[pmcfarlain]

I have a rather complicated task that I'm trying to execute. This may take a bit of explaining. It references three lookup tables and two other tables. The point of this task is to set a number of days required to complete a specific task (Engineering, Welding, etc.) in a project. These days vary based on the unit size and type. Here is part of the table that I am using for reference so that you can understand what I'm working with.

So for a job making a "Non Jacket Bin" that is under 500L, the Engineering task should take 5 days and the Weld task should take 10 days.

Here is the task as I currently have it. So the table that this is updating is specifically for tracking the number of days required to complete each operation for a job line. It is connected to the job lines table via a Line Number. Right now I only have it updating the days it takes in Engineering but I will add Weld and more. That's why I have the joins in the Update statement and not the select - just to avoid redundancy. So first, it joins the days line number to the lines line number. Then it takes the unit size and joins it to a lookup table. This lookup table is used to convert the size to an integer. In the lines table it is written as "100L" so this join just allows for a value "Liters" to be used later which removes the L. The next join connects the Unit Type to another lookup table which will be used to find the general category the type is in ("Bin" rather than a code like "BINX"). Finally, this is joined to a table that is similar to the one pictured above. Here is part of that table for reference:

(The days are the days before the ship date)

The Unit Size refers to anything that is less than or equal to that number. Thus, why my join is on Liters >= Unit Size. I then sort the select by the unit size ASC which should connect Liters to the smallest Unit size without going over. 

Instead, what I get is all units, regardless of size, assigned the smallest days. What am I doing wrong?

Any help would be appreciated!

 

[BaySunshine]

Hi @pmcfarlain,

While I didn't grasp all the details you provided, I can make some suggestions to improve your task:

1) Modify WHERE in UPDATE clause to specify which records in tbl_ggl_job_lines_days table should exactly get updated. Since there is no reference to any fields from tbl_ggl_job_lines_days, the one value that is returned from SELECT will get updated to all the records in that table.

Here is one example you can refer to:

This will update only the records where the IDs in both tables match.

2) Modify SELECT query and add the joins that you have added below in the UPDATE part to this query including WHERE Unit_Type = "FREIGHT".  I am not sure if you need to add these joins only in SELECT query or add in both SELECT and UPDATE but they have to be added in SELECT query for sure.

I hope this helps.

[RunForrestRun]

Hello! 

I recommend that you check out these links as well, they have been extremely helpful to me

• https://howto.caspio.com/tasks/
• https://www.youtube.com/watch?v=v9SDmaHuSr4