Welcome back, avid readers and tech explorers! We hope you've been finding our series enlightening thus far. In the first installment, we familiarized you with the mesmerizing world of ChatGPT and Power Platform. The second chapter saw us meticulously constructing a Custom Connector to serve as a bridge between these two technological giants. Now, it's time to turn theory into practice.
In this third episode of our journey, we're moving beyond the connectors and into the realm of user experience, as we introduce the Power App interface into the equation. And we're not stopping there; this time, we're adding the power of Microsoft Business Central into the mix! Here's what's in store:
Building an Intuitive Power App Interface: With the Custom Connector securely in place, our focus now shifts to constructing an intuitive Power App. This isn't just any app; we're designing it to interact with Microsoft Business Central, fetching vital Item Ledger sales data on your chosen inventory items.
Choice and Flexibility: Users will be able to select one or two inventory items for analysis, as well as choose the type of analysis they wish to perform via ChatGPT.
Data Retrieval and Analysis: Once the inventory items and type of analysis are selected, the program will extract the historical Item Ledger data from Business Central. This data will then be converted into a JSON stream, ready to be analyzed in conjunction with ChatGPT.
So, buckle up as we delve into creating a Power App that not only makes accessing historical sales data a breeze but also offers a new dimension of analysis through AI capabilities. We're about to take a significant step closer to an integrated, intelligent, and incredibly powerful ecosystem.
Ready? Let's jump right in!
1. Power App. Create a new Power Apps Canvas App.
2. ChatGPT. On the left navigation menu click on Data. Then click on Add data. Search for the ChatGPT custom connector and add it.
3. Business Central. On the left navigation menu click on Data. Then click on add data. Search for Business Central and add it. Select a company and click on the itemLedgerEntries (v2.0) table.
OnStart
On the left navigation menu click on Tree View. Enter the code from the next sections in the App / OnStart trigger.
4. ItemLedger. This will create a sub-set of the item ledger data that will be referenced later in the program.
// Create the ItemLedger Collection as a master
ClearCollect(
ItemLedger,
ForAll(
Filter(
'itemLedgerEntries (v2.0)',
'Entry Type' = "Sale"
),
{
itemNumber: 'Item No.',
salesAmount: Round(
ThisRecord.'Sales Amount (Actual)',
0
),
postingDate: DateValue(ThisRecord.'Posting Date'),
documentNumber: ThisRecord.'Document No.'
}
)
);
5. ItemNumber. This will create a collection of the item numbers in the ItemLedger.
// Create an Item Number Collection
ClearCollect(
itemList,
Distinct(
ItemLedger,
itemNumber
)
);
6. Asks. This will create a collection containing stock requests of ChatGPT.
// Create the ASK Option Collection
ClearCollect(
askOptions,
{
theOption: "One Item Document Count",
theComparison: false,
theAsk: "How many documents are in the data for this itemNumber? "
},
{
theOption: "Two Item Document Count",
theComparison: true,
theAsk: "How many documents are in the data for each of the two itemNumbers? "
}
);7. Ask Clarification. This will create a collection containing clarifications and stipulations for ChatGPT.
// Create the Ask Clarification Collection
ClearCollect(
askClarification,
{theClarification: "Refer to salesAmount as Sales Amount. "},
{theClarification: "Refer to postedDate as Posted Date. "},
{theClarification: "Refer to itemNumber as Item Number. "},
{theClarification: "Refer to documentNumber as Document Number. "},
{theClarification: "Use the $###,##0 format when showing the salesAmount. An example of the $###,##0 format is $11,204. "},
{theClarification: "Use the yyyy-mm-dd format when showing the postedDate. An example of the yyyy-mm-dd format is 2022-10-23. "}
);
Item and Ask Controls
We will add controls to the screen that allows the user to select the item(s) and the Ask from dropdowns.
8. Item Number. Add a Combobox to your screen. This will let the user select one or two item numbers. Configure the properties as follows:
a. Set the Items to Sort(itemList,Value)
b. Set the SelectMultiple to true
c. Set the OnChange trigger to:
Set(
selectedCount,
CountRows(ComboBox1.SelectedItems)
);
Set(
itemNumber1,
""
);
Set(
itemNumber2,
""
);
If(
selectedCount > 0,
Set(
itemNumber1,
Index(
ComboBox1.SelectedItems,
1
).Value
)
);
If(
selectedCount > 1,
Set(
itemNumber2,
Index(
ComboBox1.SelectedItems,
2
).Value
)
);9. The Ask. Add a dropdown to your screen. This will let the user select the ChatGPT Ask. Configure the properties as follows:
Set the Items to Filter(askOptions,theComparison=If(selectedCount>1,true,false))Submit Our Ask
Add a Button to the screen and rename it Submit. Add the following code in the next sections to the OnSelect trigger.
10. JSON. We are taking a subset of the ItemLedger and creating a JSON output for ChatGPT.
// Put together the data to submit
UpdateContext(
{
dataJSON: JSON(
ClearCollect(
ItemLedgerSelected,
Filter(
ItemLedger,
itemNumber in ComboBox1.SelectedItems
)
);
)
}
);11. Message. We are building the message that we will send to ChatGPT.
// Build the Message that will be sent to ChatCPT
ClearCollect(
messagePassed,
{
role: "user",
content: Dropdown1.Selected.theAsk & Concat(askClarification,theClarification) & dataJSON
}
);12. Ask the Question. We send the messagePassed to ChatGPT and receive a reply.
// Ask the Question
Set(
responseValue,
ChatGPT.AnalyzeHistory(
"gpt-4",
messagePassed
)
);ChatGPT Results
You will receive a reply from ChatGPT after submitting the item number(s) and the Ask. To see the response add a Text box to the screen. In the Text property enter the following code:
First(responseValue.choices).message.content Testing your App
You are now ready to test the Power App to see if you have working connection with ChatGPT. For the test, we will be asking ChatGPT two simple questions.
13. One Item Test. Select an item number from the Item number list. Take the default on the Ask dropdown. Click on Submit. We are asking ChatGPT to count the number of documents for the one item selected. You should see something like the screen below.
14. Two Item Test. Click on the item number list and add a second item number by holding the control key while selection an additional item number. We are asking ChatGPT to count the number of documents for each of the two items selected. You should see something like the screen below.
Conclusion
Congratulations on reaching the end of another impactful chapter in this enlightening journey towards AI-powered Sales Analysis! We've crossed yet another milestone by integrating our Power App with Microsoft Business Central. Now you have an application that not only fetches historical sales data effortlessly but also prepares it for analytical exploration via ChatGPT. The dynamic user interface we built today offers flexibility and choice, allowing you to select inventory items and types of analyses to suit your needs.
As we wrap up this segment, the stage is set for the next exciting part: Seamless Integration with ChatGPT. Prepare to be amazed as we take the data and analyses you choose and channel them through our Custom Connector, thereby unlocking profound insights using the intelligence of ChatGPT. The future is integrated, intelligent, and incredibly potent, and you're right at the forefront!
Stay tuned for Part 3, where we bring all the elements together to initiate the core analytical process. Thank you for walking this path with us; the best is yet to come!
Comments