Help with a Time Series Problem

[Rona_Leonard]

I am asking for some advice. I am working on a problem that I have with my Base. That problem is that I am attempting to automate data updates to one table from 4 other tables on a weekly basis. The puzzle is that I am tracking events that occur on each of these 4 tables on a weekly timeline. These events are by employee, in the case that they incur a trackable event on that week. We are tracking these events on a week (or month) per the time stamp on each occurrence. Each event results in either a positive or a negative score. This score is then subtracted/added to/from a credit on a different table. I have run into two problems. First, I tried to roll up each occurrence on the Employee directory. Rolling up there created an accumulation of points, not tracking by week. I then did a look up on the table with that person’s weekly stats. That repeated all the scores from each week to every week thereafter, making that occurrence manipulate their score every week instead of just the week it occurred on. The result I am trying to accomplish, is to update one table’s field, on a weekly basis, by employee, events occurring on another table. Then using that table to calculate a weekly (or monthly) score for that employee. I keep running into imitations and thinking that I should just use a report block to summarize these events on these tables on a timely basis and use that report update the stats table manually. Is there a report that does that? Any thoughts? Much appreciated.

[Mohamed_Swella1]

Hi @Rona_Leonard,

I believe this is the same issue I replied to on another post. The main issue here is that you need to have a unique record for each employee for each week since this is what you are trying to track. Check out this reply, it might be of help.

Number of Records per Week Number Formulas

Hi Felipe, Ya its clear. However, there is no such formula that would do it in a straightforward way, you will have to do a work around. The Work around that I can think of would be as follows: Create a new Field, this Field should be a formula combining the Participante and the Semana. Something like Participante&","&Semana . This will create a Field with content such as Felipe De Morais Gardim,7 . In this new Field, customize it to Link To New Record, Create New Table. This will creat…

If you need more help with this work around, feel free to send me screenshots of your table and let me know how I can help you more. If you cannot attach screenshots here, feel free to send them to my email mohamedswellam@hotmail.com

BR,
Mo

[Rona_Leonard]

Let me give you some more context about my Base and how I have it designed.

I have 5 tables:

1 – Employee directory – Primary Key = Employee ID

2 – General Performance – P.K. = Employee ID & Record #

3 – Attendance – P.K. = Employee ID & Record #

4 – Safety – P.K = Employee ID & Record #

5 – FA Stats – P.K. = Employee ID & Week

My objective is to carry over the points, by Employee, by week from tables 2, 3, & 4 into table 5, by employee, by week. Currently I am able to do this the problem is that the points stick to the employee ID and repeat with every week that is added. I need them to only show up on the week specified by the table where the point lives, for that employee. Table 5 is the “score” table where all the categories are added up to a score that is tracked on a weekly basis.

I also tried to roll the points up on table 1 but there they are listed as an accumulation for each employee. The weeks are not being tracked on this Table. I created a Weeks Table, made the week the primary ID. I linked a field to table 5 and created a roll up on another field bringing back the average for each week. This returns a collective average of every employee. This table does not have the Employee ID.
Does this create a more defined picture of my project? Can you provide me some ideas to try within this scope?

[Mohamed_Swella1 · Accepted solution]

Hi @Rona_Leonard,

Yes it is more clear now. You have data in tables 2, 3, and 4 that you want to consolidate into table 5, correct? The following is assuming you already have the week no. added in all 3 tables. If you haven’t done that already, use the formula WEEKNUM(date, [startDayOfWeek])

Well in this case, you need a master Table first that has ALL the data in the 3 tables into one table. This Table will have to have the following Fields:

PK: Employee ID + Week No.
Field 2: Data from Table 2
Field 3: Data from Table 3
Field 4: Data from Table 4
Field 5: Formula you use to calculate the Stats (not sure if it is Sum of the 3 fields or what)

How to do that, now that is the tricky part.

First, you will need to add the Employee ID to the Week No., this can be done with a simple “&” Formula. Then in a new Field, Copy and Paste the Formula Field into this Field, then you can set it to Link To New Record + Create New Table (this is Table 5 that I mentioned earlier, so if you already created it, you can link to it directly, but this would be better so it would take the items in this table and paste it directly to Table 5). Im assuming that you have the same in all 3 sheets, so you will do the same in all of them.

Now you have table 5 ready for you, with only the data in the Primary Field. There will be 3 other Fields for Tables 2, 3, and 4, but they are empty. Use the Rollup function in each Field so it would collect the data from the respective tables.

The important thing to notice here is that the new Linked field in all 3 tables will not be automatically filled, you will have to copy and paste the Employee ID + Week No. formula field into this field every time you have a new entry. You can automate this with a 3rd party app such as Zappier. This also depends on how you are filling these fields, manually or otherwise.

If you need more help, you can send me a view link to your base.

Hope that was helpful :slightly_smiling_face:

BR,
Mo

[Rona_Leonard]

Thank You Mo! Your advice worked. I was able to complete my project. Your help was extremely valuable.

[Bill_French]

Mohamed_Swellam:

the new Linked field in all 3 tables will not be automatically filled, you will have to copy and paste the Employee ID + Week No. formula field into this field every time you have a new entry.

To avoid copy-paste operations, I recommend you investigate the script block feature as a possible way to automate the process, albeit with a single click, but still better.

[Mohamed_Swella1]

You’re welcome @Rona_Leonard, my pleasure.

Please mark it as answered so others can benefit from it as well.

BR,
Mo