how to project this db
Hello need to manage my small business. Every day X products goes out from my store to my clients, and Y products return (my client has not sold them all). So i think a table for products i sold, and a table for clients. Then my doubt is how to manage the sold product and the returned products Shall i do 2 different tables, one for sold one for returned? That means that i wil have a table with
date client product sold quantity
and the other with product returned insted of sold
At the end i must be able to see the overall product sold/retruned by clients and percentage
And also to see by products the relation sold/returned
For this i see the report/graphic available don't allow me to get data from 2 different tables..so how can i do that?
101 replies
-
the problem is that i don't know how to write the code for this reporting...
-
this is where i am now
-
stef. idea said:
the problem is that i don't know how to write the code for this reporting...Did you take a look at the Quick View table in my sample? It has an example of how to do it.
-
yes i did but i am not able to translate to my table...as i don't understand it completelly
-
this is updated, with return/sol field. There is also sold return checkbox with color, which could be automtically selected depending of what field i fill (this is just to have a quicker understanding at glance..not necessary though)
I think this base is fine, it should work
But can't go further with reporting as i don't know coding!
-
little by little i am understanding the code in quick view...i have problem though...whn i go to sleect year in the quick view table i have created, i have only 2024 as choice, not 2023...
-
may be there is something wrong with that Loop function in the year?
-
definitely there is somehting not working in your db. in the quick view, if i select july 2023..i get 100 produced 100 shipped 7 returned. but there is no order in july 2023..most of them, nearly all of them are 2022
-
yes it is dynamic, i copied ypr formula, trying to adapt to my db which is little bit differnt from your..basically don t have catalogue, but straight the order with subtable
I got no error so it seemed good, but then i have weird yeard to choose, i add more record like if it was 2024 and 2022 and now i have to choose in th equick view 2024 and 2035..
But as i said there is somehting wrong in your db also..it gives me a good number of total even i put the date on july 2023..where there is nothing produced
But i tell you more..i tried to change somehting in your db, making it similar to mine, bypassing the catalog and checking the date straight n order..
i have the same, weird year come out in quick view
And even after putting back as th eoriginal the weird year remain
p.s. if i set the year as simple choice then it seems to work fine
-
let me add..work fine in your table..in mine i don't get any total ever..always 0
-
Can you make it work with just a month field like in my code?
Since you have a dynamic choice field, getting data from it is not as straight forward as a simple choice field.
To simplify your education, try creating a simple choice field with the years you want and it will be easier to reference in your code.
Can you upload a new version of your DB so we can see the latest state of your DB and the code you are working with?
-
that's it
yes they are not dynamic now, both month and year
p.s.
just disvoered that if i take away the year it works..the problem is that the simple choice get as value the id number of the items. For year it doesn't work, as 2023 return 1 being the first item in list
-
Looking at your Month field, you didn't create the month names in chronological order. You need to have Gennaio as selection 1, Febbraio as 2, Marzo as 3. The code to find the months is assuming that the number of the selection matches the month number.
-
stef. idea said:
a part that...do you think is it a good structure?it seems so o meAre you asking if the choice field is good structure? or the dashboard? or your whole DB?
-
the whole db. I am asking if it structured enough good to obtain report and keep data for some years. Also..i would need to input client name and products by scanning a barcode (with smartphone may be)..so need to know if is good also for that
-
stef. idea said:
the whole dbAny self designed DB from a beginner will always be a work in progress. My personal DB has changed so much from when I first started. You will start small and as you use it more you will start to want the DB to do more. So expect it to change and probably change dramatically in the future.
You mentioned that other people might be using the product. If that is true then you should consider having multiple accounts so you can protect data from being edited/deleted by accident or by malicious intent.
I just tried out scanning barcodes on my iPhone and it worked, but it only enters in data into a text/number field. I don't see how it can select a record in a reference field, your clients for example. You will probably have to create a data entry page that would allow someone to scan the code then click a button to find the correct client and product.
On another note, I don't think you need to create a new record in Vendite for returns. I would recommend that you keep track of that data in the same record for the item. The data you are tracking is that you sent an item to a customer on a particular day. It doesn't matter if you sent them 1 or 1,000 you only create 1 record. How many is returned is also tracked in the same record as that data is a data point for that same record.
You will create another table like I did in my sample. See the Graph table. Here you can pull data from different tables into one and then graph it.
-
there will be 1 or 2 persons using the db in general. It will be 1 at the office in the morning, inserting product going out, and then the operator that is out for deliver will insert the returned products while he collects them.
And this is the point for the records in vendite. It is supposed to be something very easy and fast to do (hence the barcode)...i don't see him searching for the order where these return belong. Cannot be done..the procedure should be quick,, and easy with as less as possible work on the smartphone and db.
ANd here we reach the other point
You say that there is no possibility to search for example a product straight with barcode reading? I need to understand how much complicated it will become. How do i adpat the procedure you are telling me to the table i already have? Do i need to create new table?
-
just to specify more..it is not a need to know to what order belong that return..in any case. i understand perfectly your point to "save "records..and this was my concern too..but i don't see how to do it
Anyway..it seems now that the main problem is the barcode reading..that must be easy otherwise has no sense. the ideal solution would have been..Operator scan and automaically a record with date is open..at maximum he should press a button..but i could not find somehting similar
-
i had an idea about the barcode
I actually don't need a table for client and a table for product
In the SALES table i can just insert what i read from barcode..whic is the real name..so don't need to make a search in the client (for example table the corresponding value
Does it make sense?i think so
What would be really nice is ot have at the same of scanning that it CREATE the record
By the way ho w do you do for reading the barcode?you said you get to read the data right? i want to try..this should easy the entire process
-
stef. idea said:
i had an idea about the barcode
I actually don't need a table for client and a table for productThat is one way to go about it. I think it would be good to have client and product tables. You will probably want to have that data sometime in the future.
I've attached a sample DB where I control the input of data. I didn't want to go down this route right now as it takes a lot more coding. But like anything, work flow will push how things are developed.
I've created a new Data Entry table that has two tabs, Kitchen and Delivery. I think you can show/hide the tabs based on user name so the two groups don't see the other tab to make the work flow easier.
The user in the kitchen would scan the bar code with the clients ID and then scan the barcode of the product ID (if your barcodes have the client name and product name then the code would have to be modified) then enter in a quantity. Then they click the Nuova Consegna (Google Translated :) ) button to add the item to the customer. Since you make food daily, this form puts in today's date into Movimenti.
Behind the scenes the button checks if there already is a record in Movimenti and Vendite so it doesn't create duplicates.
The delivery employee would click on the delivery tab and where they see a customer list of today's customer (maybe it needs to be yesterday's customers?). They select a customer and then they will see what was delivered to the customer. Then they can record the returned items.
-
stef. idea said:
for what i undertand those fileds in the new table don't work with barcode reading right?Actually the Data Entry table allows the use of the barcode scanner in iOS. I don’t know if Android has the barcode scanner.
stef. idea said:
Because the operator has to find to what order that return belong..and this is really not necessary.The delivery driver only has to find the customer, through a simple drop down, or it can be a radio selection. This method does not need any scanning, but you would have to have a driver try it out to see if they think it will work.
-
Ok.The dropdown (or radio) with 100-150 client on a mobilephone (the operator is moving around and will use a smartphone) it isn't very hand. He should at least use a search field inserting 2,3 letter.
And to find to what order those return belong isn't somehting i can ask him really..he will kill me, with the help of the boss! :)
Unless i can use any order to return those products, this could ease the process...as i told you i don't need to consider the single order, but the overall send/return for that product for that client in that month
It would be easier for him to do the same of what kitchen does anyway, wouldn't it possible? I know that your pourpouse is to keep the db light..and this is important i agree. For this i might consider to export data every year? and start the db from scratch..as long as i can keep the whole process quick
Content aside
- 1 yr agoLast active
- 101Replies
- 456Views
-
3
Following