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

    Opinion on design Structure

    bruns

      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
          philmodjunk

               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
            bruns

                 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
              bruns

                   Another Screen shot

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

              • 4. Re: Opinion on design Structure
                bruns

                     Last

                • 5. Re: Opinion on design Structure
                  philmodjunk

                       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
                    bruns

                         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
                      philmodjunk

                           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
                        bruns

                             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
                          philmodjunk

                               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
                            bruns

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

                            • 11. Re: Opinion on design Structure
                              bruns

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

                              • 12. Re: Opinion on design Structure
                                philmodjunk

                                     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
                                  bruns

                                       Thanks, everything great