Jump to content

This topic is 2767 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

Hi Alvaro, welcome to FMForums! :smile3:

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.

Link to comment
Share on other sites

  • Newbies

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.

Screen Shot 2016-09-22 at 2.47.57 PM.png

Link to comment
Share on other sites

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: 

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


Edited by doughemi
Corrected starting level calculation
Link to comment
Share on other sites

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.).

Link to comment
Share on other sites

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 



Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

This topic is 2767 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.