Static Worksheet to Active Project and Related Data

[SimmKa]

Hey there! I hope I put this topic in the right place. I am quite stuck on something so I thought I would crowd-source some input. I'm working on a Workflow database that also includes Inventory, Account Management, and Estimate functionality. My question pertains to one specific aspect of the Workflow part of the database.

The database currently has Events which then can have multiple Work Orders, the Work Orders can have multiple Projects and the Projects can have multiple Tasks. Tasks can then have multiple parts associated with them.

I am trying to develop a part of this database that has the capabilities to store "static" data at the Project level and below to be later transferred out as an active project at a later date. I want to have all possible projects that could possibly occur stored and then when it comes time to do this project I just look it up and 'add' it as an active project to an existing Work Order (which has an existing Event)

Imagine you have a spreadsheet or worksheet of any possible combination of projects and their related tasks and those tasks related parts. Instead of having to reference any one of those projects on paper and add them one field at a time when you add a new project and related tasks and parts - you could just look at them on one layout and by the click of a button "set variable" on all the data and "set field" in another layout so this "static" data becomes an active project.

My problem is coming in because I've been trying to do this in so many different ways and there is always one thing or another that goes wrong. 'Projects Static' is one TO 'Tasks Static' is another TO and then there are parts related to the task which I have to grab from the Inventory.

I tried one way that seemed to work the best in theory: on the 'Projects Static' layout having a Project title and then a portal to display and add the multiple tasks related to that project, but the problem with that is that there are also multiple parts related to a task so I would need an additional portal inside of the Tasks Static portal to get this to work exactly how I needed it to but I settled for using repeating fields for the parts inside the Tasks Static Portal. This gave me all kinds of headaches when I tried to 'set variable' on all of the data and loop and then 'set field' in the 'Active Projects' layout.

The real problem is not so much displaying the information on the layout 'Projects Static' because that works great. The problem is when I try to add the static project and all of its related data by essentially copying all of the data over to another table's layout and everything goes haywire. I need to be able to move it over to an existing Estimate as well. Moving the 'Project' to the estimate is no big deal but making sure all the project's data inside the portal gets transferred over correctly is a huge issue. Not to mention I need to get this data moved and made into an active project too (the multiple related tasks and parts are the real problem).

I've tried other ways too and am starting to think I am either going about this the wrong way or am going to have to deal with adding projects and tasks in the old fashioned way - one field at a time whenever new projects need to be added. I'd really like to get this Project worksheet feature to work though.

I hope the above information was clear enough, I know I am having a hard time explaining it so please let me know if you would like me to clarify further!

Thank you in advance for your help!

 

[comment]

I am going to assume here that once you have added a "static" Project to a WorkOrder, you want to be able to modify it and its Tasks and their Parts, without affecting the original "static" project and its components. Otherwise this would be a simple matter of creating and maintaining a many-to-many relationship between WorkOrders and Projects.

 

3 hours ago, SimmKa said:

The problem is when I try to add the static project and all of its related data by essentially copying all of the data over to another table's layout and everything goes haywire

What you describe is no more that a scripting challenge. Admittedly, a considerable challenge - but still just a matter of identifying the data that needs to be duplicated (either by loading all of it into variables, or by defining a relationship to it, or just having it in a found set), creating the corresponding records in the target tables (in top-to-bottom order, so you can link children to their parents) and populating them with the identified data.

I am not keen on doing this for you, but I will give you one hint: do this at the data level, not at the layout level. IOW, forget about portals, work directly in the target tables.

As for populating the fields: if you have too many of them, you could import the records, instead of loading all the required values into variables. A lookup is another option you may consider.

 

[SimmKa]

Hey Comment,

Thank you for such a quick reply! You are right the static project needs to remain static and be modified on its own and once its data gets moved to become an "active" project the static data needs to remain untouched.

I've figured that it is simply a scripting challenge that I am just getting stuck on. I'm not entirely sure what you mean by saying work in the target tables. You mean have the script get related data from the target tables while its running in the backend? 

Looking up the data is probably the most simple way to go about this but I did try this, it works great for the first populated portal record (because remember, the data being moved is on the ProjectsStatic layout, then there is a TasksStatic portal with PartsLineItems repeating fields inside of that. So the first portal record gets moved over with the Project but none of the other tasks do.

Maybe List View on the TaskStatic layout would work and then isolating the tasks to relate to a specific ProjectStatic is the best way to go about this (is this what you mean when you says 'work directly in the tables')? Maybe instead of having ProjectStatic as its own table, make it a field on TasksStatic so it can be part of a found set within TaskStatic? That doesn't completely eliminate portals though because if I had my way I would use a portal to display the parts on tasks but the way it is currently, the parts fields for this worksheet are repeating fields (10) inside of the TasksStatic portal on the ProjectsStatic layout. Because projects can technically have unlimited amount of tasks and tasks should technically be able to have unlimited amount of parts but in practice the task can be split up so if there are more than 10 you can just create a a new "task continued" so that more parts can be a part of that same task.

Maybe with the more detail I've provided above you'll find that there is an even better way to do what I am trying to.

 

[comment]

Can you give us a rough idea how many fields need to be populated with default values (taken from the "static" project) in the Projects table, in the Tasks table, and in the Parts table (not counting the key fields linking these tables)?

What I meant by "working at the data level" is that your script should not use steps such as Go to Portal Row[]. Similarly, it should not matter at all which view a layout is set to use. Portals an views are meant for users - you, as the developer, should not rely on them for your scripting.

 

28 minutes ago, SimmKa said:

the way it is currently, the parts fields for this worksheet are repeating fields (10) i

Is this only in the "static" branch of your solution? Or is this aberration carried over to the actual projects themselves?

[SimmKa]

On the individual level, 2 fields from Projects (Project Name and System Type) and for the Tasks I need 2 fields the Task Name and Estimated labor Hours, then from parts I need 5 fields: part name, part number, Qty, Cost date, Retail cost, and our cost. Parts are being pulled from a lookup based in the taskstatic table and is looking them up from the Inventory table.

I didn't mean to imply I was relying on them for scripting purposes ( lists and portals) My suggestion for the lists was to kill two birds with one stone by showing the listed records on the layout thus making it easier on the viewer and also allowing for easier scripting by pulling the records together in order to move them to become an active project. 

Yes the Project worksheet will be the only static branch at least the only one where I will have to do anything like what I am trying to do with it. There is also an inventory that is kind of static the way it relates to tasks but the tasks do need to communicate to the inventory at some point that a part has been used and to deplete it from the inventory even though that won't happen until the Task has begun and the part is being used.

I hope that helps answer your questions! I'm behind my computer for the rest of the night so my responses should be quicker. Thank you!

[comment]

5 hours ago, SimmKa said:

2 fields from Projects (Project Name and System Type) and for the Tasks I need 2 fields the Task Name and Estimated labor Hours, then from parts I need 5 fields: part name, part number, Qty, Cost date, Retail cost, and our cost.

Well, that's practically nothing, so I see no reason why you shouldn't keep it simple and use variables for all of them.

Now, as I said earlier, I am not going to write the script/s for you. I can give you the pattern, though:

• Start by storing the WorkOrderID of the current work order in a variable.
• Next, locate the record of the StaticProject you want to replicate. Load the ProjectName and SystemType values into variables.
• Go into the (real) Projects table, create a new record, and populate the 3 fields (WorkOrderID, ProjectName, SystemType). Now store the ProjectID of the newly created project in a variable.
• Return to the StaticProjects table and do GTRR to find the related StaticTasks. For each task, load the TaskName and EstimatedLaborHours values into variables, go into the (real) Tasks table, create a new record, and populate the 3 fields (ProjectID, TaskName, EstimatedLaborHours). Also store the new task's TaskID in a variable, so you can use it when creating the Parts (if you create them at this point - I didn't really get this part of your description).