16
Views
9
Comments
Controller / manager of exchanged products (only the value of the products €)

This is my first post, so be friendly with me!

(sorry for my english, i used a translator to help me)



First of all, I’ll introduce myself,

I'm an amateur / intermediary in IT, programming, etc. I've seen some courses from OutSystems.

I work in the healthcare area, more precisely in a pharmacy. My job is to handle orders.


I have a "little problem":

We exchange products between pharmacies (send and receive) and part of my job is to record these exchanges (only the value (€)) of the products, so that later on we realize that pharmacy owes money to whom.

For that I made a excel file (several linked files) that works beautifully, but has a catch;

- each pharmacy has this file

 - each pharmacy must register the products sent and received

- and here's the catch: we couldn't cross our data to see if everything is fine. I have to call each pharmacy and check if the values are the same.


What I would like to try to do here on the outsystem is an application, where pharmacies could register and verify these exchanges. I don't know if I should take advantage of my excel file or do an application from scratch! what do you think?


Now I explain how my excel file (s) works:

(Sorry for being so long and I hope to explain how it works)


1- We are 8 pharmacies in total;

2- For each pharmacy I have 1 excel file

   - The file of each pharmacy is divided as follows:

       - I have 14 sheets.

          - 12 sheets for each month (January, February, March, ...,

           December)

          - "previous year" sheet; values transiting from the previous year

          - "total" sheet - compiles the values of the other sheets (months and

            last year)

     - Sheet "Month" (exp: January) and "previous year" are the same and have:

         - the number of transfers by pharmacy in that month, which

           we receive and send

         - a table where I register what I send and another table where

          register what I get

            - the tables are divided into 5 columns:

                - Document number

                - TAX 6%

                - TAX 13%

                - TAX 23%

                - Total (sum of TAX 6% + TAX 13% + TAX 23%)

        - a table where I calculate the difference between:

             - TAX 6% shipping and receiving

             - TAX 13% shipping and receiving

             - TA X23% shipping and receiving

         - A table that sums up:

              - Total 6% TAX that I send

              - Total 13% TAX that I send

              - Total 23% TAX that I send

          - A table that sums up:

              - Total 6% TAX I receive

              - Total 13% TAX I receive

              - Total 23%TAX I receive


3 - I have a file called "TOTAL" that compiles information from other files.( I can explain later on how it works) 

PS: I can send my files (if allowed) if it helps


What is your opinion? What is the best way to do this?

(I will need a lot of help to try to do this)


PS: I can send my files (if allowed) if it helps


Thanks




Hello SpiritFox,

In my humble opinion.

According to your report I suggest making a new system. 

Model the data to meet these requirements.

 It will come out faster than trying to manipulate these spreadsheets.

Rank: #433

Hi SpiritFox,

It looks like you need a complete system where you can input your sales data and run the various reports based on the different criteria and in the format you require to continue your job. It would be better if you can start developing your own system with your requirements and whenever you stuck somewhere you can take help from various experts into this forum.

Thanks & Regards,

Sachin


Rank: #3248


So let's get started:

I started by creating 3 entities and attributes:

  - Utilizador#create users with whom I will trade

    - id; Name; address

  - recebidos #collect the values from all the places where I receive

    - id; N_dev; VAT_6; IVA_13; IVA_23; TOTAL_IVA; Place; date

  - enviados #collect values from all places where I send

    - - id; N_dev; VAT_6; IVA_13; IVA_23; TOTAL_IVA; Place; date


Now I need the values to be presented by location:

  I create a new entity called,

  - Utilizador_valores #compile both received and sent data per user

1-  How do I get the values from the "utilizador", "received" and "sent" tables and appear in the entity "utilizador_valores"? (I want him to bring me the following attributes: N_dev; IVA_6; IVA_13; IVA_23 and date.)


2 -I need the values by date (months) and user to appear. Do i create our entity?


PS: I don't know that it is well structured. Feel free to correct me

I'm having trouble creating relationships between entities


Thanks


Rank: #128

Hi SpiritFox,

let me say first off, it's pretty brave of you to go from keeping track in an excel sheet of some numbers, to a whole new app in Outsystems.  

I hope you enjoy the experience and end up with a useful app.

So, it's a pretty simple use case, and you should indeed be able to make it with only a few entities / screens.

I think, looking at your initial ideas, one of your challenges will be to understand that relational databases are not excel sheets.  So, it's not because you want to represent the list of receive and send transactions as seperate lists to the user, that you have to have them in seperate entities, it's not because you want to represent at some point totals per pharmacy to the user, that you need to have an entity for this.

I think I would name the top entity pharmacia, not user, as a user is typically the person logging in to the system.  Of course, you might want to make a link between pharmacy and user, allowing each user only to edit data of his own pharmacy, while you yourself have access to edit all data...

You don't need 2 seperate entities for recebidos and enviados, I would make a single entity transação, with 2 pharmacia id in it, one being the receptorId, the other being remententeId.  I kind of miss a date in this table, don't you want to know when some transfer happened ?

So for the Utilisador_Valores, not sure where you are going with this.   You say compile totals, there's no need to store that in an entity.

As for the attributes of the transaction, I'm assuming the 3 fields IVA6 IVA13 and IVA23 are where you put the net amount of the transaction, and dependent on the column you put it in, you calculate a different VAT amount ??  I'm not sure I understand the 3 VAT columns.  If each single transaction has only one VAT applicable, a better design would be to have a static entity with the possible vat regimes, and have a VatId to indicate the VAT to be used, in the transaction table.

So I don't know what the devolucao and data attributes are for ?  Also, do you make no reference to what actual products were transferred, only the total amount ??

So the above is about registering the day to day transactions, but at some point you will also do stuff to bring the balance back to zero, so to speak, right.  At some point (maybe end of each month...) you will calculate who ows who money, and make a payment to clear this ?  If it were me, I would add this to the same transaction entity.  Maybe you may want to have a transaction type in there, to show the difference between these 2.

Then there's also the time aspect, you have your current spreadsheet systems set op with sheets per month, and special sheets to bring funds from one year into the next, and so on.  I don't think you will need any of this in your new system, this is not an official part of your accounts, right, just a way for you to keep track of these exchanges internally ?

So, as to how these data will be entered into the system, you currently have all pharmacies keeping their own list, that at some point need to be consolidated by you, you don't want that, you want one single central truth that all pharmacies can check and update.  I could imagine that both the sending and receiving pharmacy can enter transactions into the system, but I would add maybe 2 statusses to the transaction, allowing for both the sender and receiver pharmacy to "approve" each transaction.

Dorine

Rank: #3248

Hi Dorine, Thanks for helping. I will answer your doubts


1 -"Let me say first off, it's pretty brave of you to go from keeping track in an excel sheet of some numbers, to a whole new app in Outsystems.  I hope you enjoy the experience and end up with a useful app. "  


 Thanks Dorine, Excel sheet are simple to me.

I am having trouble understanding the database relationships between entities and how to structure them


2 - "I think I would name the top entity pharmacia, not user, as a user is typically the person logging in to the system.  Of course, you might want to make a link between pharmacy and user, allowing each user only to edit data of his own pharmacy, while you yourself have access to edit all data... "


I plan to create two levels of access. 

   The Administrator has full access and can create / edit / delete pharmacies

   Pharmacies can only access / edit your data


3 - "You don't need 2 seperate entities for recebidos and enviados, I would make a single entity transação, with 2 pharmacia id in it, one being the receptorId, the other being remententeId.  I kind of miss a date in this table, don't you want to know when some transfer happened ? "

Every month I have to deliver a report to my boss with the totals sent and received for that month and the accumulated total.

Annual summary for months between 2 pharmacies

3.1 - "So for the Utilisador_Valores, not sure where you are going with this.   You say compile totals, there's no need to store that in an entity. "

Total summary per month between my pharmacy and the rest, plus accumulated until the end of that month



4 - " If each single transaction has only one VAT applicable, a better design would be to have a static entity with the possible vat regimes, and have a VatId to indicate the VAT to be used, in the transaction table. "

Each transaction can have only one or more IVAS. For example: 

N_Dev 6545/565    Product A - 20 € IVA_6    Product B - 15 € IVA_23


5 - "So I don't know what the devolucao and data attributes are for ?"

N_Devolução -is the document number (ex: invoice number)

Data - Date of document "I didn't know if I should put it on or not"


6 -"Also, do you make no reference to what actual products were transferred, only the total amount ?? "

We just need the values (€€€)

(in the future I would like to classify the products by categories, but it is not necessary - a personal thing)


7 - " At some point (maybe end of each month...) you will calculate who ows who money, and make a payment to clear this ? "

See answers 3 and 4.

If the values do not go to zero, they move on to the next month.

We usually only settle accounts at the end of each year. Values may not go to zero so they move on to the next year


8 - "Then there's also the time aspect, you have your current spreadsheet systems set op with sheets per month, and special sheets to bring funds from one year into the next, and so on.  I don't think you will need any of this in your new system, this is not an official part of your accounts, right, just a way for you to keep track of these exchanges internally ? "

It's not official, just for internal control but I have to report tomy boss for months and years.


9 - "So, as to how these data will be entered into the system, you currently have all pharmacies keeping their own list, that at some point need to be consolidated by you, you don't want that, you want one single central truth that all pharmacies can check and update.  I could imagine that both the sending and receiving pharmacy can enter transactions into the system, but I would add maybe 2 statusses to the transaction, allowing for both the sender and receiver pharmacy to "approve" each transaction. "

My idea is to "force" each pharmacy to register each transaction. Then check what I sent to pharmacy X was received in pharmacy Y.

How can I do what you are suggesting?

Thanks

Rank: #128

Hi,

ad 2 : yes, exactly, I would model a separate pharmacy entity, and relation entity saying what user of your system can update transactions for what pharmacy.  This way, you can give yourself access to all pharmacies, and for example a single pharmacy could have maybe 2 employees that can update this, or maybe there is a pharmacist with 2 adressess, and he can update both.

Ad 3 : this is the exact thing i think you will need to wrap your head around.  In your database, you only need to store the data, you don't need to organise / summarize / aggregate them.  So only the original data entered by the user need a place in your database, the way you summarize and present them, the way you calculate other data from them, can all be done in the application by designing your screens and reports.  For example, you store with each transaction the date, and then you can make a report, if you feel like that, showing a summarized value by month or year or ...


Ad 4 : ok, if more than 1 VAT code can be applicable to a single transaction, then your design is ok.  I would personally do it different, but that would probably make it too complex.


Ad 5 : understood, both should be in transaction, is correct.


Ad 6 : ok, be aware that if at some point in the future you want to identify details about product (category), you will have a lot of work to redo, as such a requirement means you can have several product categories in a single transaction, you'd have to work with transaction and transaction row or something like that, each row having it's own product, it's own vat code, and transaction would be total of all rows.  That's very different from what you have now.

Ad 7 : ok.  So this 'settling' that you do, maybe once a year, maybe even less often, I think that would have to end up as a 'settlement' transaction into your transaction table.


Ad 8 : ok, as long as you record a date with each transaction, you can report it any way you want.  I was wondering more about the relationship between these lists you make and the official accounts ?  So am I understanding right that even though Pharmacy A sends 5 products to pharmacy B, in the official accounts this is not represented, as far as taxes and inventory management and so on, it is AS IF these 5 products never left pharmacy A ??


Ad 9 : I would do it something like this : either the receiving or sending pharmacies adds a transaction.  You could let them both enter them, or you could enforce that it is always the sending party that initiates it, for example.  Then both the sending party and the receiving party see it in the list, and both have to approve it (basically both saying : I agree that these transaction details are correct)


Dorine

Rank: #3248

Good morning,  thank you so much for helping me


"Ad 3 : this is the exact thing i think you will need to wrap your head around.  In your database, you only need to store the data, you don't need to organise / summarize / aggregate them.  So only the original data entered by the user need a place in your database,

   - ok i got it

 the way you summarize and present them, the way you calculate other data from them, can all be done in the application by designing your screens and reports.  For example, you store with each transaction the date, and then you can make a report, if you feel like that, showing a summarized value by month or year or ... "  

   - I understand but I still don't understand how I do it ("can all be done in the application by designing your screens and reports"). When I realize how I do this I will be able to evolve a lot


"Ad 4 : ok, if more than 1 VAT code can be applicable to a single transaction, then your design is ok.  I would personally do it different, but that would probably make it too complex. "

  - How would you do it?


"Ad 6 : ok, be aware that if at some point in the future  ..."

  - I will forget this one


"Ad 7 : ok.  So this 'settling' that you do, maybe once a year, maybe even less often, I think that would have to end up as a 'settlement' transaction into your transaction table. "

   - I'm not sure if I understood this part.  Is the "settlement" transaction, the missing amount that passes to the following year or month?


"Ad 8 : ok, as long as you record a date with each transaction, you can report it any way you want.  I was wondering more about the relationship between these lists you make and the official accounts ?  So am I understanding right that even though Pharmacy A sends 5 products to pharmacy B, in the official accounts this is not represented, as far as taxes and inventory management and so on, it is AS IF these 5 products never left pharmacy A ??"

  - our stock control program is able to remove products and indicate for, when and where they went, etc ...
Everything is recorded. Taking your example:

If pharmacy A sends 5 products to Pharmacy B everything is registered and the products are out of stock. Pharmacy A has 5 fewer products.

The system creates a transfer order (similar to an invoice, but it is not an invoice. I don't know the correct name in English). Hence you need the "N_Dev" attribute.

I also have to present the report every month with the summary of the exchanges between the pharmacies to my boss so that he knows where his money is. 

PS: This process is provided for by our legislation


"Ad 9 : I would do it something like this : either the receiving or sending pharmacies adds a transaction.  You could let them both enter them, or you could enforce that it is always the sending party that initiates it, for example.  Then both the sending party and the receiving party see it in the list, and both have to approve it (basically both saying : I agree that these transaction details are correct)"

   - My idea is:

The pharmacy that sends something, records it. The pharmacy that receives, will register what you receive can see my registration, cannot edit it but must register as well. (then the application itself will see if the values entered are the same or not and alert)


Thanks a lot @Dorine Boudry 

Rank: #128

"Ad 4 : ok, if more than 1 VAT code can be applicable to a single transaction, then your design is ok.  I would personally do it different, but that would probably make it too complex. "

  - How would you do it?

Well this is a classic data modeling question : 

Do you do something like

Entity Transaction, with 3 attributes VAT6_Amount, VAT12_Amount, VAT23_Amount

OR

Entity Transaction with a 1 to many relation with a second entity Transaction_Amount that is of one of the VAT types. 

The second approach is more flexible (imagine government invents new VAT regimes) and you can attach behaviour to each type (such as how to calculate the inclusive amount), but is much less straightforward to understand and develop software against.

simple approach

complex approach

"Ad 7 : ok.  So this 'settling' that you do, maybe once a year, maybe even less often, I think that would have to end up as a 'settlement' transaction into your transaction table. "

   - I'm not sure if I understood this part.  Is the "settlement" transaction, the missing amount that passes to the following year or month?

No, i mean, what is the point of making these lists ??  Do you ever do something to bring it back to 0 ??  If you do, then this act has to be added into the system.  What does it mean if something "passes to the next month or year" anyway ??  Why is the month end or year end important for this ?


Ad 8 : this is where all my doubt comes from, if you properly register these transfers in your stock system, and create proper transfer orders, why do you even need this separate adminstration of these transfers, can't you get the reports from your accounting / stock system ??  Or is it because each pharmacy has unrelated accounting systems, and the sole purpose of these lists is to consolidate information between you.  But then still, why ?


Ok, so, good luck with it, I can't say much more about it without knowing more about the requirements.

Dorine