I need to write a script that determines if the portal table changed. My portal table counts the number of steps of a command executed by a computer and if last one changes it needs to update a field. Any ideas? Thank you for your time
It depends one what you mean by "change".
New record added? Record deleted? Existing record modified? Modified in any field or just certain fields? Modified since when? Since the last time that your script ran to check for this?
Do the records in the portal's table of a ModifiedBy Date field that auto-enters the current date if the record is modified in any way?
If so, you can check that field to see when it was last modified. There are audit tracking tools that you can acquire and install if you need a more detailed record of what has changed.
You can also set up script triggers that update fields when such changes occur to better track when a change took place.
Thanks for the response.
I just need to track if the last portal row still contains a certain value.
The portal Table looks like this
I need to determine if the Task Name is still "execute" and the TaskNumber is still 2. If it is not, I need to update a field outside my portal table.
The portal table does not have a modifiedByDate field? Is that something you would recommend me adding?
Write the script:
Go to table::TaskNumber // which is the field in your portal
Go to Portal Row [Select: Off ; Last]
If [TaskNumber = 2]
Else update your field
If allow creation is enabled go to last portal row will go one row too far.
Assuming the portal is unfiltered and unsorted, you might use the last function to check the value of fields in the last related record.
Correct, I didn't think about it because I never use Allow Creation. Hence the difficulty to give suggestions, I would have seen it obviously if I were doing it for real.
In this case, assuming no Sort applied on the portal the last related record will be the last row - 1, so the modified script would be:
Go to Portal Row [Select: On; With dialog: Off; Get ( ActivePortalRowNumber ) - 1] // back one row
Go to table::TaskNumber
Update your field
Or just use last ( portalTO::Field )
to get the value of field from the last related record. No need to interact with portal rows at all.
Thanks for you help
The example I used was with 2 but I have thousands of records and I don't know the last task Number for each of them. Knowing this how could I determine if the task number has changed? Thanks again for your help
Whether there is 2 related records or 20,000 related records, Last ( relatedTable::Field ) returns the value of the last related record where the referenced field is not empty.
Does changing the last task number mean that the last record's task number field was modified or does it mean that a new record was added to the portal's table?
in Either case,
Set Field [ParentRecord::LastTaskNumber ; Last ( tasks::TaskNumber ) ]
Would capture the current task number of the last related tasks record and store it in a field of the parent record. You could then compare values to see if a change has taken place:
If [ParentRecord::LastTaskNumber <> Last ( Tasks::TaskNumber ) // task number has changed ]
Do what you need to do when the task number has changed here
But you might take a few steps back and put your questions in the context of the larger task that you are trying to perform and also describe the fields, tables and relationships involved in much more detail. It's possible that given that "context", you might get very different suggestions on what to do.
So are you now saying you that you have thousands of records for one parent record and you don't know what would be the last task recorded task for each or are you saying you have thousands of parent records, please answer the following questions:
If [PortalTable::Task = PortalTable::Memo_Task]
# No Change - Take any action if needed
# Change - Take any action if needed
new record added the portal table.
To give some contexts. I have two tables, one called module details contains data from a spreadsheet that contains a command_ID and a list of steps for each command ID. The next table is Activity Details where each record in the table contains a command_ID and contains fields such as preqs and successor. It also contains the portal table from Module Details. Activity Details has a one to many relationship with module Details because each command_ID can have many steps. The portal Table contains a list of steps and tasks that each command_ID executes.
Say we are in command_ID 3 and my successor is 4. Currently, I want to first portal row in the record containing command_ID 4 to say "Notify" . Now lets say i add a task number 8 and its task description is "exit". I want my database to know that and auto update command_IDs 4 first portal row with exit instead of Notify.
Portal table for Command_ID 3 before auto update
Portal table for Command_ID 4 before auto update
Portal table for command_ID 3 after update
portal table for command_ID 4 after update
if you have any questions please let me know. Thanks again for your help.
That raises a lot of questions. In particular, why adding a record for command 3 modifies what's shown for command 4. That's something totally new to this thread.
Are you saying that task 0 of Command N + 1 should always the Last Task of Command N?
And if I already have 5 tasks for Command N + 1 and I add a new task to Command N, what happens to the 5 tasks already listed for Command N + 1? Do they all "move down one" and thus a new task 0 is added and the previous records have a task number that increments by 1 from what it was previously?
If I understand this correctly, and I'm far from confident that I do, You should use a script to add a new task to a given command that then duplicates that task and makes it Task 0 with Command N + 1 so that it is added to the portal for the next Task in the series.
No Command N + 1 task 0 is want ever the Command N is in the prereq field for that record.
The record basically goes to its prereq and pulls the last portal row task description field and places it in the first portal row of that record. For instance command_ID 5 has a prereq of command_ID 10. My script would know if the user is in command_ID 10 record and if the user changes to last portal row. The script will go to command_ID 5 (command_ID 10s successor) and makes its first portal row task Description field set to that value.
I have a script that does it but it does update the successors first portal row if the prereq last portal row is changed.
Here it is. CAR_ID = Command_ID
1. To give some contexts. I have two tables, one called module details contains data from a spreadsheet that contains a command_ID and a list of steps for each command ID. The next table is Activity Details where each record in the table contains a command_ID and contains fields such as preqs and successor. It also contains the portal table from Module Details. Activity Details has a one to many relationship with module Details because each command_ID can have many steps. The portal Table contains a list of steps and tasks that each command_ID executes.
2. there can be as many tasks at a Command_ID needs to have
3. The records are not in any order. I just search all the records until I have find on that contains Command_ID N. Go into that record and go to the portal and do X. It would be nice to make it so if Command_ID say 5's prereq is 10 and 10 portal tables last row is change it would auto update command_ID 5 first portal tables row.
If it did the script you wrote with the memo_task when would I run it. Would I use an onobjecttrigger?
Hmmm, my Crystal ball has been down for weeks awaiting a system upgrade. I can't see inside your head or magically peer over your shoulder while you type. I only know what you tell me here in this one discussion. If you don't provide the info, any help offered could be way off target.
"Prerequisite"? A new term and concept. How do you link a command record to it's prerequisite?
I skimmed your script, but it just raises more questions an comments.
There are zero comments in your script explaining it's intended purpose and the function of each subsection of your script. It may make sense to you now, but other developers and your future self will more easily understand how that script is supposed to work if you add in some comments to document what's going on here.
You don't have to loop through a portal to get to the last record in most cases. Note my comments about using the last function to access data from the last related record. Even if Last isn't going to work, there are other more efficient ways less likely to break due to future layout changes or adding a script trigger that then gets tripped by the go to portal row step. Both relationships and ExecuteSQL can access such data without the need for a loop. You can even pull up the related records as a found set in a hidden window and sorting can make the desired record the very first or very last record.
We are still missing crucial details about the design and intended function of your database solution.
I apologize for not being clear enough. I will give you a detailed explanation of functionally.
The goal of the database is to store and automate a lot of information that they are doing by hand. My company currently has all these excel spreadsheets and they want me to create a database that is a one stop shop for them. The excel spreadsheets contain Command_IDs and each command_ID has a list of tasks that the computer with execute. The field "task number" is just a way to keep track of the order of tasks. They want me to have a table where each record has a field that represents a command_ID. A command_ID can be any number and has a lot of fields and a portal table connected to another table that houses all the excel spreadsheet inform like what each task number does. That's why I made two tables one to house the information from the spreadsheets and one to display and edit tasks based on the command_ID field. The whole goal is to export this information and have a computer read the export and for them to analyze the data from the computer.
prereq was just short for prerequisite.
In the code I sent you I loop through all of them because I dont know if the record I am on is related to the record I want to go to. Do you want to post a diagram of my relationship table?
This still doesn't answer a key question that I asked:
How to you link a command to its Prerequisite command? (Yes, I know what the abbreviation meant, I'm asking how, by looking at the data, you can tell what command links to which in this fashion.)
It's also not clear why you want the last task record of the prerequisite command to be repeated as the first task of the current command. That may be besides the point in terms of getting this to work and maybe I am still not understanding what you have in mind here. Posts with statements such as:
No Command N + 1 task 0 is want ever the Command N is in the prereq field for that record.
leave me very confused.
Either you are relying on a translation web site to translate your posts and it failed to translate here or you didn't proofread what might be a sentenced messed up by smart phone's auto-correct, but it sure doesn't help understand what you want to do here.
Retrieving data ...