Cascading Dropdowns Autimatically Populate

[Claude Cassidy]

Good afternoon Alexander.

Hope you are well during these uncertain times.

I have the following question with regards to the cascading dropdown functionality inside of DFFS.

We have a list that we are looking up to, the list includes managers as well as employees. I have now been given the task of creating a time sheet based off of this list.

Now what I have done is I have used the cascading dropdown functionality to get all of this information through, starting off with selecting the Manager, and then having the employees that work for that specific manager populate. The issue I am facing is that some managers have 5 employees under them and other managers have 40 employees under them.

My question to you is:

Is it possible to select the manager and have the employees that work for said manager auto populate?

So for instance, if I select Joe Soap as a Manager and he has 6 employees working under him, I need it to be able to prepopulate all information for all 6 of those employees.

Manager (Dropdown) Joe Soap

Employee 1 Code:
Employee 1 Name:
Employee 1 Surname

Employee 2 Code:
Employee 2 Name:
Employee 2 Surname:

Employee 3 Code:
Employee 3 Name:
Employee 3 Surname:

and so on and so forth.

The field names in the manager/user list are as follows:

Reports_x0020_To, Title, FirestNames, LastNames

And the names of the fields in the current list are as follows:

Manager_x0020_Name, Employee_x0020_Number, Employee_x0020_Name, Employee_x0020_Surname

So basically I need to be able to choose the Manager, once done, the employees that work under the chosen manager need to have their Employee Number, Employee Name and Employee Surname Populated.

So basically what I will have in the end is the following:

Employee Code
Employee Name
Employee Surname
Day 1
Day 2
Day 3
Day 4
Day5
Day 6
Weekly Hours

Employee 2 Code
Employee 2 Name
Employee 2 Surname
Day 1
Day 2
Day 3
Day 4
Day 5
Weekly hours.

This will be for a timesheet that the managers will need to fill out weekly. The reason I have been asked to make all the employee details auto fill is because some managers have 40 employees underneath them, and they don’t want to have to manually select all 40 employees.

[Alexander Bautz]

I don’t see how you can do this in one list item when you don’t have a fixed number of employees. It’s not practical to add 40+ sets of the same fields in a list – both because when one manager gets 41 employees it will break, but also because a list can only have a fixed number of fields and with a growing number of employee-placeholders (the set of the 10 fields) it will get close to this limit – ref: https://docs.microsoft.com/en-us/sharepoint/install/software-boundaries-and-limits#column-limits

Having one list item for each employee would fix this issue, but make the manager have to go trough 40+ list items to log the time for each employee each week. This last approach could work if your manager use quick-edit (datasheet view) to plot the numbers.

I can create the code to let you select the manager and then loop trough all the employees to fill in the field in the same list item, but you would have to add all the fields in this format:

1_Employee_Code
1_Employee_Name
1_Employee_Surname
1_Day_1
1_Day_2
1_Day_3
1_Day_4
1_Day_5
1_Weekly_hours

2_Employee_Code
2_Employee_Name
2_Employee_Surname
2_Day_1
2_Day_2
2_Day_3
2_Day_4
2_Day_5
2_Weekly_hours

and so on, but I’m not sure how practical a list like this will be.

Let me know what you think.

Alexander

[Claude Cassidy]

Hi Alex.

You are correct, after trying to create 40 x each field I realized it was not practical.

We have managed to sort it out via a vLookup, pulling in the users that work under the current logged in user (built a query in the vLookup to check if the current logged in user is the same as the Manager.) This works 100%. So the Manager opens the form and the query automatically pulls his employees information through including days and week hours.

I have been tasked with embedding a list view on a separate tab, which I have done now, but have been asked to see if its possible to not show the SharePoint Menus but only to show the List itself. I have attached a screenshot of the Embedded list and highlighted in red what I have been asked to show. I have also added a screenshot of the HTML code I am currently using for the Embedded List

Attachments:

1. 

   Embedded.png
   

2. 

   Embedded-2.png
   

[Alexander Bautz]

You can make this embedded view fullscreen by changing embed to iframe:

<iframe id="myEmbeddedListView" src="https://contoso.com/Lists/DFFS_TestList/AllItems.aspx" style="width:1000px;height:1000px">

Then add this to your Custom JS:

function makeFullScreen(){
    var isLoaded = document.getElementById("myEmbeddedListView").contentWindow.document.getElementById("s4-bodyContainer");
    if(isLoaded !== null){
        // Add timeout to ensure it is ready
        setTimeout(function(){
            document.getElementById("myEmbeddedListView").contentWindow.document.getElementsByTagName("body")[0].classList.add("ms-fullscreenmode");
        },500);
    }else{
        // Not ready yet
        setTimeout(function(){
            makeFullScreen();
        },100);
    }
}
makeFullScreen();

Let me know how this works out.

Alexander

[MikeS]

Alex,

I have a similar situation as above. I have a parent list that needs to import records from a child list (the child list contains boilerplate multi-line text that will be further custommized once in the parent list). The child list contains 20 records, 4 fields per record = 80 fields.

The user should be able to select any or all of the 20 records from the parent list and import all related fields for each child record to the parent list. Once imported the user must be able to edit the records in the parent list and export to a spreadsheet after editing.

Based on the above discussion it looks like vLookup is the way to approach this and avoid setting up 72 fields in the parent that replicate the child fields. However, I’m not sure your JS above will allow editing.

Do you agree or is there a better solution?

Thanks
Mike

[Alexander Bautz]

I’m not sure vLookup is what you are looking for, this is for creating child items and not picking from a ready set of selectable items.

Unfortunately for you I think you are looking at crating a custom solution as none of the plugins for DFFS can do this out of the box.

Alexander

[MikeS]

If you can help I would prefer a custom JS. Unfortunately it looks like I’m going to have to create 80 fields in the parent if I wish to pull any number of records forward from the child list.

Could the code in these posts be expanded or combined:

https://spjsblog.com/forums/topic/send-vlookup-data-in-a-workflow-email/

https://spjsblog.com/forums/topic/set-value-from-vlookup-table-to-a-field-in-the-current-form/

It seems that others may benefit as well with a custom solution.

Thanks,
Mike

[Alexander Bautz]

Sorry for the late reply. I’m not sure I understand exactly what you are trying to do so I cannot really answer your questions.

If you can give me some more details I can look at it.

Depending on the complexity and how much help you need I might have to charge you for the time I spend, but I’ll not charge you anything before I have looked at any details you send and I have decided if it is something I can help you with or not.

Alexander

[MikeS]

I was able to use the Autocomplete JS solution and it is working well. So no further action needed.

Mike

[Alexander Bautz]

OK, I’m glad you found a solution.

Alexander

[Author]

Posts