Journal entries apps: need advice

(0) Share

Report

Posted on by Voltes

265

Hello everyone,

I want to create an apps like a journal entries, and need some advise or guidance on what do I need and what should I do to achieve that.

The apps I want to create is an Stock count journal, which in then end I would like to create journal in Dynamics 365 F&O. So in my mind, this apps will do these steps:

1. 1st screen, I need to select Warehouse, Location, and the journal name

2. 2nd screen, there is a barcode scanner which will scan the item, and this will create data rows like in a list contains the item number and the quantity.

3. All those list will goes or save to CDS.

4. There is another menu, probably in the 1st screen to push those records in CDS to D365 F&O to create inventory journal there.

What I did so far are :

1. Create tables Warehouse, location, journal type, and the table to records all the counting result (lets call it CountEntries) in CDS.

2. the 1st screen is created, data source is the CountEntries, so I display the Warehouse, Location, and Journal type which is a lookup.

3. the 2nd screen is created up to Barcode scanner button.

and currently I'm confuse on how to bring the Barcode scanner result to a gridlist which is maybe a "Data table" control. Can it create a new record everytime I scan, and the information of warehouse, location, and journal type follows what's in 1st screen ? So when I scan 3 (three) times, the grid / data table control will have 3 rows of item and quantity with in background the row also fill in by warehouse, location and journal type from the 1st screen automatic.

The grid and also the CountEntry table which I imagine will be something like this :

.. and there is also one important thing which I still don't know how to handle it, is on that table has primary key (usually with fieldname "Name") which I don't know what to fill in ? can it be a number sequence, and how to do that ?

Anyone know a good reference to it, with the steps needed to create this. Or just some advice what do I need to do to achieve this.

Many thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

eka24 20,923 on at

Like (0)

Report

For the journal watch the video and work around it
https://youtu.be/vjfrXUa1Juw
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @Voltes,

Based on the needs that you mentioned, I think the Collection could achieve your needs.

I have made a test on my side, please consider take a try with the following workaround:

1. Within your 2nd screen, add a Text Input box (called "QuantityTextBox") prior the Barcode scanner control to collect the quantity.

2. Set the OnScan property of the Barcode Scanner control in your 2nd screen to following:

Collect( TempCollection, { Id: CountRows(TempCollection) + 1, // generate a auto-number value to act as Primary Key Item: BarcodeScanner1.Value, Quantity: QuantityTextBox.Text, Warehouse: WarehouseDropdown.Selected.Value, Location: LocationDropdown.Selected.Value, 'Journal type': JournalTypeDropdown.Selected.Value } )

Note: I assume that you add several Dropdown controls/ComboBoxes in your 1st screen to list the available options for Warehouse, Location, and the journal type.

Add a Data Table in your 2nd screen, set the Items property to following:

Sort(TempCollection, Id, SortOrder.Descending)

then enable corresponding columns in this Data Table, then you could preview these scanned Item record.

Add a Button (called "Patch") in your 2nd screen, set the OnSelect property to following (press it to save your above TempCollection back to your CDS Entity😞

ForAll( Filter(TempCollection, Not(Id in 'Your CDS Entity'.ItemID)), Patch( 'Your CDS Entity', Defaults('Your CDS Entity'), { ItemID: Id, Warehouse_Entity: Warehouse, Location_Entity: Location, JournalType_Entity: 'Journal type', Item_Entity: Item, Quantity_Entity: Quantity } ) )

Note: The ItemID represents the Number type column in your CDS Entity, which is used to store the Id column value from above TempCollection table. The Warehouse_Entity, Location_Entity, ... are all columns in your CDS Entity, please replace them with actual column name from your CDS Entity

Set the OnVisible property of the 2nd Screen to following:

Clear(TempCollection); ForAll( 'Your CDS Entity', Collect( TempCollection, { Id: ItemID, Warehouse: Warehouse_Entity, Location: Location_Entity, 'Journal type': JournalType_Entity, Item: Item_Entity, Quantity: Quantity_Entity } ) )

Please consider take a try with above solution, then check if it could help in your scenario.

Best regards,

Was this reply helpful? Yes No
Voltes 265 on at

Like (0)

Report

Hi Kris,

Actually yes, I'm in the middle of creating the same, with collection also. Mind to tell me that dropdown - "WarehouseDropdown.Selected.Value," what control is that, I'm using a DataCard that attached to my Data source, which is correctly displayed as combo box, but as I tried to refer to that DataCardValue, it does not give me an option of Value.

So if for example my dropdown control name is DataCardValue3, I can only type until "DataCardValue3.Selected"

Thanks,

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @Voltes ,

The "WarehouseDropdown" that I mentioned is actually the DataCardValue3 that you mentioned. If you want to reference the selected value from the ComboBox, please use the following formula:

DataCardValue3.Selected.Value

Note: I assume that the DataCardValue3 connected to an Option Set type column in your CDS Entity.

When you patch data from your collection back to your CDS Entity, there is something you need to know -- when assign data to the Option Type field, you could not provide a String value for it directly, instead, you need to provided available option under this Option Type field:

ForAll( Filter(TempCollection, Not(Id in 'Your CDS Entity'.ItemID)), Patch( 'Your CDS Entity', Defaults('Your CDS Entity'), { ItemID: Id, Warehouse_Entity: If( Warehouse = "Option1", Warehouse_Entity.Option1, Warehouse = "Option2", Warehouse_Entity.Option2, ... ), ... } ) )

Note: I assume that the Warehouse_Entity represents the Option Set type column in your Entity, please replace it with actual column name

If the solution I provided above is helpful in your scenario, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

Was this reply helpful? Yes No
Voltes 265 on at

Like (0)

Report

Hi Kris,

No, my DataCardValue3 is a data type Lookup, because I set a Relationship to my other entity which is the master data of Sites. Same as the other : Warehouse and Journal type. Is that why there is no suggestion of "Value" when I type DataCardValue3.Selected ?

Thanks

Was this reply helpful? Yes No
Voltes 265 on at

Like (0)

Report

Hi,
I've managed to retrieved the value. So it turns out I need to use the field name instead ".value" at behind.
So the code is like this :
Collect(colEntry,
{ Site : DataCardValue3.Selected.Site,
Warehouse : DataCardValue4.Selected.Warehouse,
JournalType : DataCardValue5.Selected.'Inventory Journal',
Item : GalleryProducts.Selected.Name,
Qty : 1 })

But there are some problem, number 1 is the returned value is the GUID not the text / id / name. like below:
Is it because it is a relationship that I set to its master entity ? How to have the text / name ?
With GUID like that, I cannot insert into my CDS transaction table, right?

Another one is, as I'm using Gallery of products to connect with the Barcode scanner result, the behavior that I'm planning is, after Barcode scanner send back the scan result, it will search and filter my Gallery of products. This is working perfectly, but to make it "selected" as in my Collect's code "Item : GalleryProducts.Selected.Name,", we should touch/select the gallery first (although it is only contain 1 (one) row). Can we make it as it is selected already ? or what is the code to retrieved the filtered Gallery ?

I'm so sorry, this has 2 questions in this topic.

Many thanks in advance.

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @Voltes ,

For your first question, based on the screenshot that you mentioned, I think the Site, Warehouse, JournalType column are all LookUp type columns in your CDS Entity, which references record value from other Entities, is it right?

According to the formula that you mentioned, I think the DataCardValue3.Selected.Site, DataCardValue4.Selected.Warehouse and DataCardValue5.Selected.'Inventory Journal' formula all reference the Unique Identifier column value (GUID value) from related Entities.

Your thought is right, with these GUID value, you could not insert it to your Main Entity properly. As an fixed solution, please modify your formula as below:

Collect( colEntry, { Site : DataCardValue3.Selected, // Reference the record directly from the corresponding ComboBox Warehouse : DataCardValue4.Selected, // Reference the record directly from the corresponding ComboBox JournalType : DataCardValue5.Selected, // Reference the record directly from the corresponding ComboBox Item : GalleryProducts.Selected.Name, Qty : 1 } )

then you could save your colEntry data back to your Main Entity.

For your second question, based on the needs that you mentioned, I think you could consider set a Default value within your Gallery based on the scanned barcode result. Please set the Default property of the Gallery to following:

LookUp('Products Table', FilteredColumn = BarcodeScanner1.Value)

Note: Above LookUp function is used to find the specific record based on the scanned barcode result.

Actually, if there is only single one record in your Gallery against the scanned barcode result, the single one record would be selected automatically without selecting it manually.

Note: In default, the first item in the Gallery would be selected automatically

Best regards,

Was this reply helpful? Yes No
Voltes 265 on at

Like (0)

Report
Hi Kris,

Sorry to inform you, the last suggestion is still not working.
Collect( colEntry, { Site : DataCardValue3.Selected, // Reference the record directly from the corresponding ComboBox Warehouse : DataCardValue4.Selected, // Reference the record directly from the corresponding ComboBox JournalType : DataCardValue5.Selected, // Reference the record directly from the corresponding ComboBox Item : GalleryProducts.Selected.Name, Qty : 1 } )
Unless I'm wrong, but u suggested to type until ".Selected" only, right ? Actually it give me error like below:

error
Btw, "For your first question, based on the screenshot that you mentioned, I think the Site, Warehouse, JournalType column are all LookUp type columns in your CDS Entity, which references record value from other Entities, is it right?"
--> Yes, it is. Actually I mentioned it in this previous conversation. 😅

Anymore suggestion what I should used in that Collect statement ?

Thanks,

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @Voltes ,

If these Site, Warehouse, JournalType column are all LookUp type columns, you could reference the Primary Name column in those related Entities through these LookUp field.

For example, the Warehouse LookUp field reference record value from the 'Warehouse Entity', and the Primary Name column in your 'Warehouse Entity' is called "Warehouse Name" (stores "WH1", "WH2", ...). Then you could reference the "Warehouse Name" column value from the 'Warehouse Entity' through the Warehouse LookUp field using the following formula:

DataCardValue4.Selected.'Warehouse Name'

Note: I assume that the DataCardValue4 (ComboBox) is connected to the Warehouse LookUp field

So on your side, you need to modify your Collect formula as below:

Collect( colEntry, { Site : DataCardValue3.Selected.PrimaryNameColumn_SiteEntity, Warehouse : DataCardValue4.Selected.PrimaryNameColumn_WarehouseEntity, JournalType : DataCardValue5.Selected.PrimaryNameColumn_JournalTypeEntity, Item : GalleryProducts.Selected.Name, Qty : 1 } )

Note: The PrimaryNameColumn_SiteEntity, PrimaryNameColumn_WarehouseEntity, ... represents the corresponding Primary Name Column in these Related Entities.

Please try above formula, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Voltes 265 on at

Like (0)

Report

Hi Kris,

Thanks. it works. So I did use the primary field of the Lookup Entity (master).
However the Default thing is not working, I still need to touch/select the only one record in gallery to make my item having value.

But this is not a big deal, unless to resolve a curiosity how to make it automatically selected. One bigger problem is the patch is not working, as it throw error saying that my CDS expected the type of Record, yet it found type : Text.

I tried to go back to the Collect and try out to use back the one the return GUID, but it also throw error saying expected is Record, and found type is : GUID.

Here is what it's like, I try to Patch 2 field first, 1 for Id, 1 for Site.
ForAll(colEntry,
Patch('App Countings',
Defaults('App Countings'),
{
    Name:CountRows('App Countings') + 1,
    Site:Site
}
and the error after I tried to use back the GUID:
Site it self is the column name of my Entity in CDS, which I'm using and want to insert.

Found some resources : https://powerusers.microsoft.com/t5/Building-Power-Apps/Patch-a-CDS-Lookup-column-within-PowerApps/m-p/174190/thread-id/58032#M58033%C2%A0
saying I should put Lookup on that Patch, but I have error: Incompatible type for the condition or the specified column " _ " does not exist->
ForAll(colEntry,
Patch('App Countings',
Defaults('App Countings'),
{
    Name:CountRows('App Countings') + 1,
    Site: LookUp(Sites, Name = Site)

}
Sites is the Data source name found in the treeview
Name (supposedly) is the primary field (key) of entity Site
Site (supposedly) is the field of the collection ColEntry.

Thanks again,
V

Was this reply helpful? Yes No