Portal duplicating

[Leather Knight]

I have made this timesheet for recording my work hours. I made a portal and relationship via the date of the time sheet. When I create a record in the portal, it makes a mirror duplicate of the record in the master. So when I create "1" record, and go to add the job codes in the portal, it makes another record in the main time sheet, not too difficult to fix, however I am drawing a blank on what I am doing wrong. Any advice??

Time_Cards.zip

[Søren Dyhr]

Instead of tweaking your template, try to see if you can make sense of this:

New Window [ ]

New Record/Request

Set Variable [ $NewMainID; Value:Main::mainID ]

Close Window [ Current Window ]

Go to Related Record [ From table: “Portal”; Using layout: “Portal” (Portal) ] [ Show only related records ]

If [ Get ( LastError ) xor 1 ] 

      Sort Records [ Specified Sort Order: Portal::Created; descending ][ Restore; No dialog ]

      Go to Record/Request/Page[ Last ]

      Loop 

              Duplicate Record/Request 

              Set Field [ Portal::fk_to_main; $NewMainID ]

              Exit Loop If [ (Get ( FoundCount )-2) xor 1 ] 

              Omit Record 

              Omit Record

      End Loop

       Go to Related Record [ From table: “Main”; Using layout: “Main” (Main) ] 

End If

--sd

[LaRetta]

Hi Kevin, your portal is based upon the same table as your main record (which you probably already knew). And you have 'allow creation' on so it shows the 'ready-to-create' ghost record in the portal. And when you type into the portal, it will create a new main record.

This main record would be your 'shift' record for one staff member. You also need a Staff table. But the portal should be a many side to one shift from the looks of the data within the portal (which appears to be job-costing) because you will have many trips per shift, right?

I'm unsure what Søren is suggesting but I'm sure there are valid reasons for it so now I'll step back and see how this progresses. I hope my input was helpful.

[Søren Dyhr]

Well I just threw in an algorithm, without seeing the "personalized" approach or take on relations, which isn't fully embraced by this community - where the doctrine is never use no-nonsense fields for linking unless there are very very good reasons for doing so.

A date is typically a very good thing to group around when reporting but for the purpose of letting employees enter their expenditures for a certain assignment, should the task carrying the expenditure be the one creating the key value or the person which does the task be creating nonsens keyvalues only for the purpose of linking stuff together.

Laretta is right here, the personal take on relations needs further explanations!

--sd

[Leather Knight]

Well, I think I see what you mean. I will make the changes and try this approach. It is not for just anyone, it is so I can track my personal time and pay. So having different employee's would be redundant I think, but lets give this a try and see what happens.. Thanks, I will post my result so we can see if I am getting this right.

[Leather Knight]

Hmm...Ok, I think I got it, please take a look and any advice is appreciated..

UMTS.zip

[LaRetta]

Hi Kevin,

I'm unsure why you didn't put your data files as tables within your Time Cards file; it would be simpler for you particularly since this is only for you. Separation model would be important with large data-sets or when sharing same UI with other companies but maintaining separate data-sets. One file is MUCH easier to work with.

I don't understand why you are using global text fields within your TimeCards file. If you are always the ONLY employee then you don't even need an Employee table (or file). But the theory is that, you plant the EmployeeID in every time card record and then simply take your fields from your Employee File and place them on your TimeCards layout. So your LastName (which you have as global in TimeCards) can be deleted. If you use an Employee table then you want the EmployeeID to be an auto-enter (FM-generated) serial number starting with 1. When this is standard number field in TimeCards (and if your EmployeeID is 1 in your Employee table), then typing a 1 in TimeCards will create a relationship and fields you've place from Employees onto your TimeCards layout will display FirstName and LastName etc. This is the power of relational.

You don't need the relationship in your Employee file at all. You can add a file reference to the other tables right within TimeCards and create layouts for Work Data and Employee Data in Timecards and create your relationships in TimeCards. But still, I'd suggest that you move your Employee file to a table in TimeCards (same with Work Data). Then do all your joining and layouts only in one file; it is soooooo much easier.

I have no idea why you have a Portal 1 table occurrence (which is a self-join).

Your PayRate is also global and it shouldn't be ... but then it shouldn't be in the TimeCards table at all - it would be a property of the Employee (see Note below). When you have a proper relationship from Employee to TimeCards (based upon Employees::EmployeeID = TimeCards::EmpoyeeID) then you can grab the employee rate of pay any time you want it. I also notice that you have Pay Rate in your Work Data table and I don't understand why.

But your original question was regarding adding records in your Work Data portal and that should work fine now. However, the relationship from TimeCards to Work Data is based upon date so you don't need the date in the portal since it will always be same date as your time card or it wouldn't appear in the portal. Also, if you change that date to different date than the timecard, you will create an orphan work data record and it will disappear. It is best not to display the key field (the field which binds your relationship) unless you disallow entry to the field for this reason (but when it's only one field such as date and same date, don't bother displaying it).

I can suggest studying using key fields to connect relationships and finding out when to use global fields. Overall, you are getting there and you solved your portal allow-creation issue so good on you!

Note: Of course there are times where each day or shift an employee would get a different rate of pay so it isn't ALWAYS based in Employee table. However, usually an Employee's base rate exists within their HR record (for each employee). And then if they get special pay rates, it would be shift-based, holiday pay etc as percent increase etc and THAT could be specified within the TimeCard for calculation (but even with special shift considerations), the business rules for overtime is usually specified within employee's record.

Edited July 28, 2010 by Guest
Added note

[Leather Knight]

Very good points. I will work on revamping it and tweeking it a bit more using your input.