Count

[AlvaroPIon]

How can I reset a count an make it start over again? I'm counting records from one account, depending on another field the count will reset to zero and start over from there on, not affecting the old records. In other words one user may have multiple records, I need to count a particular field in order to get to a certain level, but if he level drops in the future, I need to count from there on not affecting the past records.

[LaRetta]

Hi Alvaro, welcome to FMForums! 

We need more information to be able to answer your question. 

1. What type of data does this table contain?
2. What other tables exist in your solution?
3. What are you counting and why?
4. If 'the level drops in future' and the count should start over, what indicates a level drop?

My immediate impression is that you need to make some structural adjustments but we won't know for sure until we understand your situation better.

[AlvaroPIon]

Long story short, I currently work for a school that is using a level system for students(level 1-5). For example students start each day with 126 points, depending on their behaviors the students will have points taken away from them. To get to level 2 the student needs to have at least 85 points per day for 5 days no matter if the days were consecutive or not. And to get to level 3 they need to get 95 points per day in 10 days. Now the issue i am having, lets say the student is on level 3, He is on day 30. Now the student messes up and gets level reduced to level 2. What I am trying to do is keep the data from what the student did to get to level 3 but at the same time if we drop the student down to level 2. The count will start from level 2 day 1 etc etc until they reach level 3 based on points on a certain amount of days. I would like to keep the past records and at the same time if a student does drop down a level keep that data from the previous level he was on and he starts over fresh from level 2 till he hits level 3 again. I need help with finding a code/script to do this I have been trying to do this for some time. I have the first part of them going up and down levels but need the code to save the old data of a students level and continue on a new level and if he goes down again it keeps the data again and restarts again on a previous level.

[doughemi]

Each day's point sheet should be in a separate Point Sheet table related to Students by Student#. In addition to all of the lost points in the right-hand chart, this table  should have a starting level field (for that day), a Days at Current Level field, and a Move to Level field.

The script to populate the Move to Level field would look something like 

 Set Field [ Move to Level; Case(
     not isEmpty(AES Drop) or not isEmpty(Suspension Drop) or not isEmpty(Arrest Drop); starting level - 1;
     Days at Current Level ≥ {defined value}; starting level + 1;
     ""
     )
]

 From the relationship you can find the latest Move to Level record, and use it in a script to count the days since the change to the current level to populate Days at Current Level, and thereby calculate an upward Move to Level if warranted.  You will also have the means to report all level changes for any time period.

The starting level field can have an auto calculation: 

Let(
[~lastLevel = GetNthRecord( starting level; Get(Record Number) - 1);
~lastMove = GetNthRecord( Move to Level; Get(Record Number) - 1)
];
Case(
     isEmpty(~lastMove); ~lastLevel;
     ~lastMove)
)

 

Edited September 22, 2016 by doughemi
Corrected starting level calculation

[Alvaro Pion]

Thank you, I dont have a lot of knowledge i FM, can you help a little more? will appreciate.

Thank you

[doughemi]

It will be easier to help you if you ask specific questions, and give us more information about your current file structure (what tables exist, what relationships exist, etc.).

[Lee Smith]

Why not attach a copy of your file, remember to remove any confidential information or save a No Record copy.

 

[AlvaroPIon]

User Admin password adm1n

I deleted all scripts and layouts. Hope you can help me, thank you

 

[Lee Smith]

2 hours ago, AlvaroPIon said:

I deleted all scripts and layouts. Hope you can help me, thank you

 

Why? that the important stuff, it’s the client names, address, etc. that is the confidential information that needs to be removed. I removed your file above because it has student information in it.

Look at the Save options, there is a “Save a copy as....” select that and choose 

 

[AlvaroPIon]

Thank you

 

CRCPointsV13 Clone.fmp12

Edited September 23, 2016 by AlvaroPIon

[Lee Smith]

See your Private Message from me.

[Alvaro Pion]

On 9/23/2016 at 0:41 PM, Lee Smith said:

See your Private Message from me.

Thank you, can you help?

[LaRetta]

Hi Alvaro,

I've reviewed your file and the news is not good.  You've structured yourself using fields when it should be records - a common mistake to those new to database design.  You have hit a dead-end in the ability of your file and the only way to continue your build is to change your structure now before you put additional time/effort into it.  Some examples:

1. On Points:  You have the questions hard-coded as text on the layout and the point results of each question as fields.  You should have records with a field for 'question' and a field for 'point'.
2. You have a table ClassRoll_2016_17 joined to Points_2 on Last_Name in a many-to-many join.  A table should not be based upon year but rather have a year or date field within it, last name should not used as a join because multiple students can have same last name, and you need a join table between them to resolve the many-to-many.
3. Master Schedule Summary table wouldn't be needed at all with proper structure.  Any time you see same-name fields appended with different numbers, you can bet you are going in the wrong direction.
4. Global_Demographics contains multiple 'numbered' fields for contact.  This (again) is a problem.
5. You have a password field in the Users table which is very unsafe and you should use FileMaker's security instead.
6. This file has been closed improperly.  You can tell this by the 'Recovered Library' table.  Files should always be closed properly before copying elsewhere or moving.  And if the file ever improperly closes or crashes, you should revert to a prior good backup copy.  If/when the file contains real data, a Recover should be performed on the file and data migrated from the old damaged file to the new one.

There is more that could be discussed but those are the most critical to resolve.  The best thing you can do is to hire a good consultant to just get you started on the correct basic structure.  This will save you hundreds of wasted hours in future and prevent you hitting dead-ends such as this one.  It IS possible that you can use complex script to get you over this hurdle but you will hit more (and larger) hurdles over and over until the wall will be simply too high to climb.  Change it now.

If you cannot afford hiring a good relationally-knowledgeable developer then be prepared to put in the time to study and understand how relational databases work ( and there are training sites such as Richard Carlton ) which can help there.  Search this site for 'survey' or 'questionnaire' to find examples of base structure and also search for 'join'.  Comment has presented several perfect examples; offhand, here is one example of join table:  join table.  I regret giving you bad news but it is far better to get that news early in your design rather than later.

You can use examples from this forum on joins and begin with item #1 above and create your first new tables.  Then post a question about it and ask for input to be sure you are on right track.  Work on establishing a proper structure first as you go.  I wish you the best with it, Alvaro.

Edited September 26, 2016 by LaRetta

[Alvaro Pion]

On 9/23/2016 at 0:41 PM, Lee Smith said:

See your Private Message from me.

Thank you for taken the time. Really appreciate it.