Hi All,
i'm trying to read an email and the parse the data from it into some variables and table so i can use it for further flows.
fow now, i have the following.
this is a sample of the Email received which contains the table and some field i need to read them.
(note: table columns is fixed - only rows are variables ranging from 1 row to N)
i start by converting the email from HTML to text as follows
Splitintolines
split(outputs('Html_to_text')?['body'],outputs('Newline'))
where NewLine is a new line compose only.
(Outputs)
----------
(HTML to Text)
From: ad.rew@si.com <ad.rew@si.com>
Sent: Wednesday, October 13, 2021 12:17 PM
To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>
Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)
for Buyer Royal Court requires your approval for processing
direct
global
e-marketplace
* Reasons for Violation:
Rule
Additional Comments
Overall Margin >= 0.00% And Amount >= 0.00.
Approver: xxx,yyy
Buyer: customer 1
Quote Number: 3745556
Quote Name: customer 1 - SOC Room Survey (84575)
Created By: solution member 1
Technology Margin: 20.00%
Services Margin: N/A
Total: SAR 0.00
requires your immediate approval.
Please action below accordingly.
Approve
[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]
OR Reject
[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]
MANUFACTURER SUMMARY
Manufacturer
Ext Price
Ext Cost
Ext Margin
Margin
Apple
SAR 4,021.88
SAR 3,217.50
SAR 804.38
20%
Microsoft
SAR 187,110.00
SAR 149,688.00
SAR 37,422.00
20%
Cisco
SAR 12,110.63
SAR 9,688.50
SAR 2,422.13
20%
xyz
SAR 62,500.00
SAR 50,000.00
SAR 12,500.00
20%
LG Electronics
SAR 237,500.00
SAR 190,000.00
SAR 47,500.00
20%
DDRXS
SAR 21,680.85
SAR 17,344.75
SAR 4,336.10
20%
Logic
SAR 42,000.00
SAR 33,600.00
SAR 8,400.00
20%
TOTAL
SAR 566,923.36
SAR 453,538.75
SAR 113,384.61
20%
PRODUCT SUMMARY
Item Type
Ext Price
Ext Cost
Ext Margin
Margin
Product
SAR 566,923.36
SAR 453,538.75
SAR 113,384.61
20%
TOTAL
SAR 566,923.36
SAR 453,538.75
SAR 113,384.61
20%
MARGIN WORKFLOW DETAILS
Approver
Date Actioned
Action
Comments
Manager 1
Pending
Manager 2
Pending
ad rew (MEA)
AV Solution Specialist
m. +
t.
email.
si.com [https://www.si.com/]
.
.
This email and all contents are subject to the following disclaimer:
https://www.si.com/email-disclaimer
Splitintolines Output
[
"From: ad.rew@si.com <ad.rew@si.com>",
"Sent: Wednesday, October 13, 2021 12:17 PM",
"To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>",
"Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)",
"for Buyer Royal Court requires your approval for processing",
"",
" ",
"",
"direct",
"global",
"e-marketplace",
"",
"",
"",
" * Reasons for Violation:",
"",
"Rule",
"",
"Additional Comments",
"",
"Overall Margin >= 0.00% And Amount >= 0.00.",
"",
" ",
"",
"",
"",
"Approver: xxx,yyy",
"Buyer: customer 1",
"Quote Number: 3745556",
"Quote Name: customer 1 - SOC Room Survey (84575)",
"Created By: solution member 1",
"Technology Margin: 20.00%",
"Services Margin: N/A",
"Total: SAR 0.00",
"requires your immediate approval.",
"Please action below accordingly.",
"Approve",
"[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]",
"OR Reject",
"[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]",
"",
"",
"MANUFACTURER SUMMARY",
"",
"Manufacturer",
"",
"Ext Price",
"",
"Ext Cost",
"",
"Ext Margin",
"",
"Margin",
"",
"Apple",
"",
"SAR 4,021.88",
"",
"SAR 3,217.50",
"",
"SAR 804.38",
"",
"20%",
"",
"Microsoft",
"",
"SAR 187,110.00",
"",
"SAR 149,688.00",
"",
"SAR 37,422.00",
"",
"20%",
"",
"Cisco",
"",
"SAR 12,110.63",
"",
"SAR 9,688.50",
"",
"SAR 2,422.13",
"",
"20%",
"",
"xyz",
"",
"SAR 62,500.00",
"",
"SAR 50,000.00",
"",
"SAR 12,500.00",
"",
"20%",
"",
"LG Electronics",
"",
"SAR 237,500.00",
"",
"SAR 190,000.00",
"",
"SAR 47,500.00",
"",
"20%",
"",
"DDRXS",
"",
"SAR 21,680.85",
"",
"SAR 17,344.75",
"",
"SAR 4,336.10",
"",
"20%",
"",
"Logic",
"",
"SAR 42,000.00",
"",
"SAR 33,600.00",
"",
"SAR 8,400.00",
"",
"20%",
"",
"TOTAL",
"",
"SAR 566,923.36",
"",
"SAR 453,538.75",
"",
"SAR 113,384.61",
"",
"20%",
"",
"",
"",
"PRODUCT SUMMARY",
"",
"Item Type",
"",
"Ext Price",
"",
"Ext Cost",
"",
"Ext Margin",
"",
"Margin",
"",
"Product",
"",
"SAR 566,923.36",
"",
"SAR 453,538.75",
"",
"SAR 113,384.61",
"",
"20%",
"",
"TOTAL",
"",
"SAR 566,923.36",
"",
"SAR 453,538.75",
"",
"SAR 113,384.61",
"",
"20%",
"",
"",
"",
"MARGIN WORKFLOW DETAILS",
"",
"Approver",
"",
"Date Actioned",
"",
"Action",
"",
"Comments",
"",
"Manager 1",
"",
" ",
"",
"Pending",
"",
"Manager 2",
"",
" ",
"",
"Pending",
"",
"",
"",
"ad rew (MEA)",
"",
"AV Solution Specialist",
"",
"m. +",
"",
"t.",
"",
"email.",
"",
"si.com [https://www.si.com/]",
"",
".",
"",
".",
"",
"",
"",
"This email and all contents are subject to the following disclaimer:",
"https://www.si.com/email-disclaimer"
]
FilterArray Output
[
"From: ad.rew@si.com <ad.rew@si.com>",
"Sent: Wednesday, October 13, 2021 12:17 PM",
"To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>",
"Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)",
"for Buyer Royal Court requires your approval for processing",
"direct",
"global",
"e-marketplace",
" * Reasons for Violation:",
"Rule",
"Additional Comments",
"Overall Margin >= 0.00% And Amount >= 0.00.",
"Approver: xxx,yyy",
"Buyer: customer 1",
"Quote Number: 3745556",
"Quote Name: customer 1 - SOC Room Survey (84575)",
"Created By: solution member 1",
"Technology Margin: 20.00%",
"Services Margin: N/A",
"Total: SAR 0.00",
"requires your immediate approval.",
"Please action below accordingly.",
"Approve",
"[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]",
"OR Reject",
"[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]",
"MANUFACTURER SUMMARY",
"Manufacturer",
"Ext Price",
"Ext Cost",
"Ext Margin",
"Margin",
"Apple",
"SAR 4,021.88",
"SAR 3,217.50",
"SAR 804.38",
"20%",
"Microsoft",
"SAR 187,110.00",
"SAR 149,688.00",
"SAR 37,422.00",
"20%",
"Cisco",
"SAR 12,110.63",
"SAR 9,688.50",
"SAR 2,422.13",
"20%",
"xyz",
"SAR 62,500.00",
"SAR 50,000.00",
"SAR 12,500.00",
"20%",
"LG Electronics",
"SAR 237,500.00",
"SAR 190,000.00",
"SAR 47,500.00",
"20%",
"DDRXS",
"SAR 21,680.85",
"SAR 17,344.75",
"SAR 4,336.10",
"20%",
"Logic",
"SAR 42,000.00",
"SAR 33,600.00",
"SAR 8,400.00",
"20%",
"TOTAL",
"SAR 566,923.36",
"SAR 453,538.75",
"SAR 113,384.61",
"20%",
"PRODUCT SUMMARY",
"Item Type",
"Ext Price",
"Ext Cost",
"Ext Margin",
"Margin",
"Product",
"SAR 566,923.36",
"SAR 453,538.75",
"SAR 113,384.61",
"20%",
"TOTAL",
"SAR 566,923.36",
"SAR 453,538.75",
"SAR 113,384.61",
"20%",
"MARGIN WORKFLOW DETAILS",
"Approver",
"Date Actioned",
"Action",
"Comments",
"Manager 1",
"Pending",
"Manager 2",
"Pending",
"ad rew (MEA)",
"AV Solution Specialist",
"m. +",
"t.",
"email.",
"dimensiondata.com [https://www.si.com/]",
".",
".",
"This email and all contents are subject to the following disclaimer:",
"https://www.si.com/email-disclaimer"
]
Buyer Compose Output as follows
now i have two problems:
1- some time the index of the fields change due to some additional lines in the email (so the variables not getting the real values as it should be)
i'm using this to get the Buyer, Quote Number, Quote Name, Quote Created By, etc..
split(body('Filter_array')[18],':')[1]
while [18] is the index.
2- how can i read the data from the "MANUFACTURER SUMMARY" Table since the data coming now into Arrays not as a table.
do i need to loop over the array, if yes; how i can know exactly where to start the loop from and where to end with konwing that i tried to use compose and varible to get the index of ['MANUFACTURER SUMMARY'] item from the array keep getting this error
