What i need to do is allow "Category" (Normal,Supply,Comprehensive) under the Job Type table to be a selection field so that it can show all the job ledger entries that belongs to the same Job Type category. Can anyone assist me to check if what i have done is correct? It dun seem to work. Thanks!
[Job Type] [Job] [Job Ledger Entry]
Category No_ as JobID Job No_ as JobID
Description Job Description "Employee No_"
No_ as Code Job Type as Code "Hour X 1_0"
Job_List:
LOAD
"Job Description",
"Job Type" as CODE,
"No_" as JobID;
SQLSELECT "Job Description",
"Job Type",
"No_"
FROM ENSURE.dbo."Ensure Engineering (Live)$Job";
Job_Ledger:
LOAD
"Employee No_",
"Entry Type",
"Hour X 1_0",
"Hour X 1_5",
"Hour X 2_0",
"Job No_" as JobID,
"Job Task No_",
"Type",
"No_",
date("Posting Date"),
Month("Posting Date") as month,
year("Posting Date") as Year,
"Quantity";
SQLSELECT "Created By",
"Employee No_",
"Entry Type",
"Hour X 1_0",
"Hour X 1_5",
"Hour X 2_0",
"Job No_",
"Job Task No_",
"Type",
"No_",
"Posting Date",
"Quantity"
FROM ENSURE.dbo."Ensure Engineering (Live)$Job Ledger Entry"
Where Type = 0;
Job_Type:
LOAD
Category,
Description as TITLE,
"No_" as CODE;
SQLSELECT Category,
Description,
"No_"
FROM ENSURE.dbo."Ensure Engineering (Live)$Job Type";
This is a bit tough to tell what you are doing wrong without you data and model, still what i get from your code, you would be having a problem of synthetic key issue. you can try removing No_ from your [Job ledger Entry ] table.
