Journal entries apps: need advice

[Voltes]

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.

TZ

[eka24]

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.

[v-xida-msft]

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,

[Voltes]

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,

[v-xida-msft]

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,

[Voltes]

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

[Voltes]

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.

[v-xida-msft]

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,

[Voltes]

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,

[v-xida-msft]

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,