13 Replies Latest reply on Jan 8, 2014 1:35 PM by bruns

Opinion on design Structure

Title

Opinion on design Structure

Post

I have an employee database, in our industry we rehire people all of the time.  What would be the best way to capture this.

I have an employee table now where I figured I would have a portal to show past employment, so would I need a past employment table, would I need a table between those.

Lost as to the best way to proceed.  Thanks

• 1. Re: Opinion on design Structure

A "table between" would only be needed for a many to many relationship. Since this is one to many--a given "history" record links to one and only one employee record, but an employee record can link to many history records, you do not need such a table.

• 2. Re: Opinion on design Structure

Thanks

I have a calculation for calculating tenure but it is not calculating right.  I have enclosed screen shots of calc.  Can you please tell me it is wrong.  Thanks

• 3. Re: Opinion on design Structure

Another Screen shot

1/1/2014 - 1/3/2014  it gives me 6 days

• 4. Re: Opinion on design Structure

Last

• 5. Re: Opinion on design Structure

Get ( CurrentDate ) - DateHire produces a number result, the number of days from the current date to DateHire. Thus, Year ( span ), Month ( span ) and day ( span ) make no sense here and will not produce the values you want.

Here's a calculation from the Brian Dunning site that should work for you:

// © 2006 Winfried Huslik, www.fmdiff.com

Let ( [
neg = Case ( date1 > date2 ; -1 ; 1 ) ;
d1 = Case ( neg < 0 ; date2 ; date1 ) ;
d2 = Case ( neg < 0 ; date1 ; date2 ) ;

d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (
Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;

m = mod ( Month ( d2 ) - Month ( d1 ) -
( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
];
y * neg & ¶ &
m * neg & ¶ &
d * neg & ¶ &

y & " years, " & m & " months, and " & d & " days" & ¶
)

Use Get ( CurrentDate )  and DateHire in place of the date1 date2 variables and delete the text in blue.

• 6. Re: Opinion on design Structure

It does not work with Get (Current Date) , it will not update and gives wrong date.  I substituted date_term and it works.

however, it shows a ? in the field until I click on it, then it shows me the numbers, when I click out it goes back to ?.  I have never seen that, any idea?

• 7. Re: Opinion on design Structure

Get ( CurrentDate ) will work, but the calculation field must be defined as an unstored field of type calculation. It can't be a stored calculation and it can't be an auto-entered calculation and still update.

To remove the ?, make your field wide enough to display the result without having to click into it. Also, make sure that "text" is selected as the calculation's result type.

• 8. Re: Opinion on design Structure

ah the ? was because it was set to Number.

It is unstored, but the Get (Current Date) would not calc.

I will make a backup of database and try again.

• 9. Re: Opinion on design Structure

How did you use it with the above calculation?

I tested the following in the data viewer and it worked for me:

Let ( [Date2 = Get ( CurrentDate ) ;
Date1 = GetAsDate ( "4/5/2011" ) ;
neg = Case ( date1 > date2 ; -1 ; 1 ) ;
d1 = Case ( neg < 0 ; date2 ; date1 ) ;
d2 = Case ( neg < 0 ; date1 ; date2 ) ;

d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (
Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;

m = mod ( Month ( d2 ) - Month ( d1 ) -
( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
];

y & " years, " & m & " months, and " & d & " days" & ¶
)

The only change needed to use in your DB would be to replace the GetAsDate ( "4/5/2011" ) term with the name of your Date Hired field.

• 10. Re: Opinion on design Structure

I tried Get (CurrentDate) and is not working but date_term does.  Don't know.  Any idea?

• 11. Re: Opinion on design Structure

How would I put in if statement that if date_term is blank then tenure is blank?

• 12. Re: Opinion on design Structure

I would think that you'd want the opposite, but

If ( Not IsEmpty ( YourTable::Date_Term ) ; //Put tenure calculation here )

• 13. Re: Opinion on design Structure

Thanks, everything great