OlderMan
New Member
- Joined
- Aug 28, 2016
- Messages
- 9
[FONT="]I have a query to a report I created on a form website and need to filter it into reports for different people.[/FONT]
[FONT="]The table has a column for "Approved By" and a column "Edit Submission" which is a link.[/FONT]
[FONT="]I need the Query to filter the table and only give results for a particular Approver, then I make a new query for another Approver on a new sheet.[/FONT]
[FONT="]Example table: So a separate report for each of the approvers below. [/FONT]
[FONT="]At the same time turn the last column "Edit Submission" into the hyperlink it is on the report. [/FONT]
[FONT="]Also, if possible make the Form No. into the same link as the Edit Submission link for that row.[/FONT]
[FONT="]Here is made up sample with fewer columns.[/FONT]
[FONT="]Website report[/FONT]
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>[FONT="]Filtered Excel Report for Cindy S[/FONT]
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>[FONT="]Here is what I have so far, not giving me the links.
[/FONT]
[FONT="]The table has a column for "Approved By" and a column "Edit Submission" which is a link.[/FONT]
[FONT="]I need the Query to filter the table and only give results for a particular Approver, then I make a new query for another Approver on a new sheet.[/FONT]
[FONT="]Example table: So a separate report for each of the approvers below. [/FONT]
[FONT="]At the same time turn the last column "Edit Submission" into the hyperlink it is on the report. [/FONT]
[FONT="]Also, if possible make the Form No. into the same link as the Edit Submission link for that row.[/FONT]
[FONT="]Here is made up sample with fewer columns.[/FONT]
[FONT="]Website report[/FONT]
Form No. | Approved by | Received Date | Approved Date | Edit link |
1 | Cindy S | 11/09/17 | 11/10/17 | Edit Submission |
34 | Claude D | 09/10/17 | 10/10/17 | Edit Submission |
25 | Claude D | 05/10/17 | 08/10/17 | Edit Submission |
8 | Cindy S | 14/10/17 | 20/10/17 | Edit Submission |
66 | Cindy S | 11/11/17 | 14/11/17 | Edit Submission |
155 | John Smith | 05/11/17 | 09/11/17 | Edit Submission |
12 | Claude D | 10/11/17 | 14/11/17 | Edit Submission |
88 | John Smith | 10/09/17 | 12/09/17 | Edit Submission |
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>
Form No. | Approved by | Received Date | Approved Date | Edit link |
1 | Cindy S | 11/09/17 | 11/10/17 | Edit Submission |
8 | Cindy S | 14/10/17 | 20/10/17 | Edit Submission |
66 | Cindy S | 11/11/17 | 14/11/17 | Edit Submission |
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>
Code:
TABLE 0[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]let[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Source[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Web[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Page[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]Web[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Contents[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"https://www.xxxform.com/table/72826000000000"[/FONT][/COLOR][COLOR=#666600][FONT=inherit])),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data0[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Source[/FONT][/COLOR][COLOR=#666600][FONT=inherit]{[/FONT][/COLOR][COLOR=#006666][FONT=inherit]0[/FONT][/COLOR][COLOR=#666600][FONT=inherit]}[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#660066][FONT=inherit]ExtractString[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]AddColumn[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data0[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Link"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Text[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]BetweenDelimiters[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Submission[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"HREF="""[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]""""[/FONT][/COLOR][COLOR=#666600][FONT=inherit])),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#660066][FONT=inherit]IndexedWebsites[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]AddIndexColumn[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]ExtractString[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Index"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#006666][FONT=inherit]0[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#006666][FONT=inherit]1[/FONT][/COLOR][COLOR=#666600][FONT=inherit]),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#660066][FONT=inherit]CreateHyperlink[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]ReplaceValue[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]IndexedWebsites[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] link[/FONT][/COLOR][COLOR=#666600][FONT=inherit]][/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"'=HYPERLINK("""[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]LinkConnections[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]""", """[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Submission[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]""")"[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Replacer[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]ReplaceText[/FONT][/COLOR][COLOR=#666600][FONT=inherit],{[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Edit Submission"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Reordered Columns" = Table.ReorderColumns(IndexedWebsites,{"Date", "CAT No.", "Approved By", "Designing Judge", "Province", "Host Club", "Courses", "Trial Date", "Received date", "Approved Date", "Post-Approved", "Edit Link", "Link"}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Approved By", Order.Ascending}}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Approved By", "Approver"}, {"Designing Judge", "Designer"}, {"Province", "Prov"}, {"Host Club", "Club"}, {"Trial Date", "Trial"}, {"Received date", "Received"}, {"Approved Date", "Approved"}, {"Post-Approved", "Post-Date"}})[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]in[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Renamed Columns"[/FONT][/COLOR][COLOR=#333333][FONT="]