AnsweredAssumed Answered

Auto-enter/set date field based upon contents of date field on related record

Question asked by lpedscalny on Mar 5, 2018
Latest reply on Mar 5, 2018 by philmodjunk

Hello community,

I have long searched this forum to answer my many FM questions, with frequent success in solving the problem, but I have now come up against an issue I haven't been able to solve in this way. As a disclaimer; I am not a FM "expert", so please forgive any lack of correct terminology etc. I will explain as clearly as I can.

 

I am creating a database to manage my husband's electrical company operations. The primary entry screen for his employees will be a "Daily Job Report". On this layout, the employee will select the date, the customer, and the job in question. The customer and job info originate from other layouts, the date field is set based upon the day the work is complete.

 

Beneath these fields I have created 2 portals; 1 to record personnel info for that day & job and one to record material used for that day & job. Since the issue I am experiencing is the same for both portals, I will describe it for the personnel portal. As a visual, the screen in edit mode, looks like this:

 

The personnel portal displays data from a layout called DJR Personnel Charges, and I have formatted it so that records can be created via this portal. The issue I am having is that once the user clicks into the portal row (thereby initiating the creation of a new record), I cannot seem to get the date field (selected in the screen shot above) to auto-fill to be the same as the date the employee entered when creating that particular Daily Job Report. When I created a calculated field so that the DJR-PC Date field = DJR date field, it defaults to the earliest date of any Daily Job Report record, and the same thing happens if I create a look-up. The other (related) issue I am having is that I filtered the portal to only display records that have date fields = to each other (so that only DJR Personnel Charges related to this specific Daily Job Report will display), but the "Total Personnel Charges" field I created with the intent of calculating only these charges is actually summing all personnel charges, ever, regardless of date.

 

In case it is helpful, I will post a few more screen shots below - if any other info is required, please let me know and I will clarify as best I can.

 

Relationships between layouts

Daily Job Report Layout Fields (including the Total Personnel Charges calculation field, the issue with which is described above)

DJR Personnel Charges Fields (portal contents)

Current formatting of problem date field

 

As a final note, I am open to other ways of accomplishing the same thing, so if I have done something fundamentally wrong with my structure, don;t be afraid to say so.

 

Many thanks in advance for your help!!

 

Lisa

Outcomes