Creating Field from two other fields

zipster1967

zipster1967

New Member
I have a table of employee information where the user can enter their first name in one field and last name in another field and I need to create one more field with their full name. Can I create the fullname field automatically by combining the firstname and lastname fields and if so how do I do that? I am new to fabrik and am not fluent in it's controls so a step by step would be very helpful.
 
I do not see the calc element in my version of Fabrik (I am using 3.1rc2. I located the plug-in for download but it does not show for version 3.1 joomla but for version 2.5 Am I going to cause problems if I install that plug-in to my version of Fabrik?
 
It will be wise for you to update to using Fabrik 3.1 as many bugs have been corrected. (See http://fabrikar.com/download).
You will need to download the Element - Calc plug-in. V 3.1. (It is not part of the standard configuration).
It should work with the RC2 version, anyway.
Alastair
 
I am using Fabrik 3.1rc2 I have installed the calc plug-in but am not familiar with how to create the php code to combine the two fields into a new field. I have a field called FIRSTNAME and a field called LASTNAME both of which are in a table called employees Could you give me an example of what I should put in the Calculation area to accomplish the creation of the FULLNAME Field?
 
OK - to use the Calc element, you are going to need to create a 'dummy' field to hold the combined result of the concatenated first name and last name.
So - in Element, create a new element called 'combined_name'.
For the element type - select 'calc', and Save.
Save the new element in the Group that you have chosen.
Then under More (tab at the bottom), you can add the script:

$combined_name = '{employees___FIRSTNAME}'." ".'{employees___LASTNAME}';
return $combined_name;

All that this script does is concatenate (or join) the two text items with the space (i.e. " ") in between, and return it.

Try it out.

Alastair
 
That did it. Thank you very much. If I can bother you with one more question I need some help with a way to look up an element in one table and use it in another table. I have two tables connected by the Employee ID The employee table and a job assignment table . I have fields in both tables named EID in the job assignment form I want the employee information to be selected by the Employee last name have the EID in the job assignment table match the EID from the employee table. Is this feasible and if so could you help me learn how to accomplish this?
 
I am not sure that I understand the context of the problem sufficiently to recommend a course of action.

What will help is if you can provide a copy of the design of the two tables in question. (One way to do this is to use PHPAdmin, and export the two tables in questions - just the structure is fine).

Alastair
 
I don't have all the fields created yet as I am not sure how to create the fields I need. Basically it lays out like this:

Employee Table:
EID- Employee ID
FNAME - First Name
LNAME - Last Name
FULLNAME - Full Name
POSITION - Employee Position (Controller, Draftsman, Engineer, Office Manager...)

Jobs Table:
JOBID - Job ID number (P00101-14-01 To match value in accounting software)
JOBNAME - Job Name (Rochester Park Bridge To match Job Description in Accounting Software)
BILLDETAIL - Yes or No value to determine if billing details are required for Job)

Phase Code Table:
JOBID - Job ID from Jobs Table
PHASECODE - Phase Code for work performed (Numeric code as text 01, 02, 03...)
PHASEDESC - Phase Description (Project, Additional Services...)
CPDESCRIPTION - Contract Phase description (Converts Accounting software description to contract description)

Labor Code Table:
JOBID - Job ID from Jobs Table
LABORCODE - Labor code For Work Performed (entered code)
LABORDESC - Labor code description (Entered text)
CLDESC - Contract Labor Description (Translates Accounting software labor code into contract labor code)

Hours Table:
EID - Employee ID from Employee Table (Link to Employee Table)
JOBID - Job ID from Jobs Table
PHASECODE - Phasecode from Phase Code Table selected from dropdown
LABORCODE - Labor code selected from Labor Code Table
WORKDATE - Date work performed
WORKTYPE - Dropdown of type of hours Regular, Overtime, Double time
WORKHOURS - Hours of work performed (in quarter hour increments i.e. 4.25 hours)
WORKNOTES - Description of work performed (Text area field)

There will be forms for each of the tables for data entry. In the hours table form I want the user to be able to select from a dropdown list The Employee ID by selecting their name from a list or even just have their ID entered in the field automatically based on their User ID in Joomla. The Job ID field will be selected from a drop-down taken from the Jobs table. Phase code will be taken from the Phase Code Table and so forth. I know this is easy to do in a database I just don't know the SQL or PHP statements in fabrik I can use to accomplish this and where to put them. If you could at least direct me to exzamples of how to accomplish this type of operation I could figure out the exact statements needed myself if I knew where to enter them.
I hopoe that makes things more clear.
 
I have taken your table and added a column for the recommended element.

The columns highlighted in yellow are added - they are missing.

Alastair



Table Column​
Element ? and settings​
Employee Table:

EID- Employee ID
InternalID
FNAME - First Name
Field
LNAME - Last Name
Field
FULLNAME - Full Name
Calc (acts on FNAME, LNAME)
POSITION - Employee Position (Controller, Draftsman, Engineer, Office Manager...)
Dropdown list


Jobs Table:

JOBID - Job ID number (P00101-14-01 To match value in accounting software)
InternalID
JOBNAME - Job Name (Rochester Park Bridge To match Job Description in Accounting Software)
Field, (potentially a databasejoin)
BILLDETAIL - Yes or No value to determine if billing details are required for Job)
Radiobutton


Phase Code Table:

PC_ID
InternalID
JOBID - Job ID from Jobs Table
databasejoin (JOBID))
PHASECODE - Phase Code for work performed (Numeric code as text 01, 02, 03...)
Field (integer)
PHASEDESC - Phase Description (Project, Additional Services...)
Field (text)
CPDESCRIPTION - Contract Phase description (Converts Accounting software description to contract description)
Field (potentially a databasejoin)


Labor Code Table:

LCID
InternalID
JOBID - Job ID from Jobs Table
databasejoin (to JOBID)
LABORCODE - Labor code For Work Performed (entered code)
Dropdown list
LABORDESC - Labor code description (Entered text)
Field
CLDESC - Contract Labor Description (Translates Accounting software labor code into contract labor code)
Field (potentially a databasejoin)


Hours Table:

HoursID
InternalID
EID - Employee ID from Employee Table (Link to Employee Table)
Databasejoin (to EID- Employee ID)
JOBID - Job ID from Jobs Table
Databasejoin (to JOBID - Job ID number)
PHASECODE - Phasecode from Phase Code Table selected from dropdown
Databasejoin (to PC_ID)
LABORCODE - Labor code selected from Labor Code Table
Databsejoin (to LCID)
WORKDATE - Date work performed
Date
WORKTYPE - Dropdown of type of hours Regular, Overtime, Double time
Dropdown list
WORKHOURS - Hours of work performed (in quarter hour increments i.e. 4.25 hours)
Date
WORKNOTES - Description of work performed (Text area field)
text
 
I note that the table format was not preserved in the post. I attach a pdf copy of the table display.
Alastair
 

Attachments

  • table-element-mapping.pdf
    67.7 KB · Views: 383
Okay I have created the missing elements but I am still unsure of how I should do the database joins. I have the settings set up as follows for the EID in the Hours table:
Render as: Dropdown
Connection: Site Database
Table: Employees
Value: EID
Label: EID
Or Concat Label: Left Blank
Joins Where and/or order by statement: Left Blank

I want toi be able to Fill in the hours form with the EID of the Employee who is signed into their joomla account and taken from the employee table. the rest of the lookups would follow the dropdown list where the data would be taken from the respective table. For instance each Phase has certain labor codes that are allowed. Phase 01 would have labor codes associated with it as follows
Phase Codes​
Labor Codes​
01-Projects​
​
01-Alternative Evaluation
01-Construction Administration
01-Construction Documents
01-Construction Observation
01-Cost Estimating
01-Data Collection
02-Drafting
01-Field Measurements and As-Builds
01-Final Design
01-Kick-off
01-Permits
01-Preliminary Design
01-QA/QC
01-Quantities
01-Site Visit
01-Space Needs Study
02-Additional Services​
02-Additional Scope
02-Meetings
02-Permits
03-Marketing​
03Client Contact
03-Client Meeting
03-Contract Preparation
03-Interview Preparation
03-Proposal Preparation
03-RFP Meeting
04-General Administration​
04-Holiday
04-Internal Meeting
04-Office Administration
04-PTO
04-Time w/o Pay
05-Employee Development​
05-Conferences and Seminars
05-Internal Meeting
05-Training
06-Sub-Consultants​
06-Inspection
06-Studies
06-Testing
So once a Phase code was selected from the Phase Code table only the associated Labor Codes could be selected. I could figure out the SQL query myself but I don;t know how to translate it into the Fabrik commands in the form.
 
Okay I figured that in order to limit the Labor Code to a select set that matches the available phase codes I had to have a phase code field in the labor code table so I created a PHASECODE field in the Labor Code table. I just am not sure how to set up the databasejoin field in the other tables to accomplish the look-ups I want. If I have the database join elements in the hours table how would I create the settings so that the form in the hours table would automatically fill in the PHASECODE field in the hours table as one of the values for PHASECODE in the phase code table?
 
With reference to you Hours Table (i.e. from above):

HoursID
InternalID
EID - Employee ID from Employee Table (Link to Employee Table)
Databasejoin (to EID- Employee ID)
JOBID - Job ID from Jobs Table
Databasejoin (to JOBID - Job ID number)
PHASECODE - Phasecode from Phase Code Table selected from dropdown
Databasejoin (to PC_ID)
LABORCODE - Labor code selected from Labor Code Table
Databsejoin (to LCID)
WORKDATE - Date work performed
Date
WORKTYPE - Dropdown of type of hours Regular, Overtime, Double time
Dropdown list
WORKHOURS - Hours of work performed (in quarter hour increments i.e. 4.25 hours)
Date
WORKNOTES - Description of work performed (Text area field)
text

You will set up as follows:
EID: Element: Databasejoin - to Employee table
JOBID: Element: Databasejoin - to Job table
PHASECODEID: Element: Databasejoin - to Phase code table:
LABORCODEID: Element: Cascading dropdown: Link the value to Laborcode ID; Value to Laborcode label; Watch is linked to Phasecode ID in laborcode table, Foreign Key is linked to Phaseccode (in Phasecode table).

In the display of the form, you need to have visible the following elements:
PhaseCodeID - This is the primary selection dropdown
LaborCodeID: This is the secondary selection drop down. When you change the phasecode selection, the laborcodes will be automatically refreshed.
Then you will have the following fields to fill in with data:

WORKDATE - Date work performed : Element - Date
WORKTYPE - Element: Dropdown of type of hours Regular, Overtime, Double time Dropdown list
WORKHOURS - Element: Field - decimal: Hours of work performed (in quarter hour increments i.e. 4.25 hours)
WORKNOTES - Field (i.e. up to char of 255) or type of TEXT: Description of work performed (Text area field)

That should do it.

Alastair
 
Okay I see the relations you are describing but I cannot find the Watch selection when I set up the LaborCodeID as a databasejoin element I don;t find any watch selection choice. IS this because I am using Joomla 3.3 or am I just not looking in the right place?
 
It is very difficult to suggest a response to your note above. To make some recommendations, insight is needed into the actual structure of the following tables: Phase codes, labor codes, and hours. Can I suggest that you export these tables (structure + data) using Phpadmin, zip the .sql file, and attach to the next post?

Alastair
 
I am not sure the export I created is what you want but I have included the file I got from the PHPMyAdmin Structure + Data file creation. What I am trying to accomplish is the ability ti have a form for Hours that allows the user todo the following:
A) access/create records in the hours table that contain their own Employee ID EID since each user who is an employee will have an employee number (not sure how to create that relation at this point either) But, I also want the user to be able to select a Phase code from the list of all poissible phase codes from the PHASECODE table then be able to select a labor code only from the labor codes that match the available labor codes of that phase code.
Say they selected the Marketing Phase then the labor codes they would be allowed to choose from would be.
Client Contact
Client Meeting
Contract Preparation
Interview Preparation
Proposal Preparation
RFP Meeting

If I am not mistaken the SQL query would look something like this.
Select LABORCODE,PHASECODE from labor_codes where PHASECODE=(selected Labor Code from previous Dropdown)
I am just not sure how to create this in Fabrik.
 

Attachments

  • Schema_Timesheet.sql.zip
    764 bytes · Views: 225
I have attached two files to this post:
a) an updated sql file (20140517-model-sql.txt); and
b) a pdf file that shows all the settings needed to implement the model.

A few notes:
i) Some of the tables have IDs with specific names (i.e. like EID). These are ignored in favour of the more general 'id' that is associated with each mysql table.
ii) some of your ID columns were listed as of type field; these are replaced with integer fields - where they have been databasejoin-ed.
iii) the table 'job-assignment' is actually redundant - as it is realised using a database join. (or perhaps it has a future use?)
iv) the hours table uses three databasejoins (phase, jobs, employee).
v) the hours table has a single cascading dropdown,

My recommendation is that you import the sql file (I have prefixed the names of the tables with aa_ to distinguish the new tables from the previous ones). The imported tables now also contain sample data.

Alastair
 

Attachments

  • 20140517-model-settings.pdf
    286.6 KB · Views: 478
  • 20140517-model-sql.txt
    7.7 KB · Views: 267
I imported the tables you provided but they do not show up in Fabrik at all. The EID field is entered in the Employee database and must conform to the value in our accounting software which I cannot change. (The values from this database are exported as a CSV file to the accounting software.) Job assignment table is to limit the number of hours an employee can accumulate for a specific project/job. What I really need is how to set up the elemnets necessary to create a cascading dropdown in the hours form that allows a user to select a Phase code and corresponding labor code. Only certain labor codes are allow3ed depending on the phase code chosen. I hope that is clear because I am getting into the weeds here. (Golf term)
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 144 (members: 4, guests: 140)
    Back
    Top