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?
-
aahh i miss this notification of the new db..i am gonna try it. Meanwhile i was trying to see about the barcode reading in android. Ninox doesn't says about android/apple..i follow this procedure
https://docs.ninox.com/en/script/function-overview/functions/barcodescan
but the thing is that when the app on y phone read the code it is not transfered on the field..so even this procudre it s of no use. Wrote twice at ninox but no reply yet...
-
stef. idea On scanning, you have to add a space between scans as the scanning part of the app doesn’t do it on its own. If you keep scanning then it will make one long string.
If your product codes are going to stay the same length then I can probably rewrite the code to figure it out based on length.
I can see this being an issue as it ruins the flow of scanning, thus the recommendation of doing an import later.
Another option is to use another product like Make or Zapier that can take a google Sheet and import it into the DB. But then you will also need a scanning app that works inside Sheet.
-
Fred let me understand, with a later import how will be th ework of driver?what actually he will need to do?
-
stef. idea These are only crazy ideas of mine as I have not actually done anything like this in a real work environment.
With that in mind, my two cents:
1) driver opens a google Sheet,
2) with a barcode scanner tool/app scans the customer number then scans the product(s) to return into Sheet, you will have to figure that part out
3) then you can import the Sheet into an import table in your DB and then it can run a script that will move the data into the proper tables.
If you want to automate it then you can use Zapier or Make and use API code to do things that I don't know about.
-
Fred ok i don't understand why you have had this idea, what is not convincing you for the driver to scan in ninox?
-
stef. idea I'm confused. I was responding to your opinion that the work flow was too difficult. I was just coming up with other options. If you think the driver would be Ok with what is designed then I have no problem with that. It can also be tweaked with input from the driver.
-
Fred i think the way it is now it is not too bad!
-
stef. idea I've updated v5 with a button that allows for no space in between the product codes. It is dependent on the product codes being 5 digit/letter long. So don't change your product code lengths. :)
-
Fred Fred i don't know how long th ebarcode for product...the one whio is there was in the example data! :) anyway seems not so difficult to change if it will be needed
-
-
ok starting from your table i have inserted the field Article in QUICKVIEW and i have strated to change the code you wrote for the possiiblities to query also by article. I mean t check the balance of products sold/returned by products and by client
let t := this; let startCheck := if 'Start Date' != null then 1 end; let endCheck := if 'End Date' != null then 2 end; let yearCheck := if year != null then 3 end; let monthCheck := if Month != null then 4 end; let custCheck := if Customer != null then 5 end; let artCheck := if Articles != null then 6 end; let dataEntryCheck := [startCheck, endCheck, yearCheck, monthCheck, custCheck, artCheck]; let gatherMovimenti := (select Movimenti); switch true do case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 4) and contains(dataEntryCheck, 5) and contains (dataEntryCheck, 6) : sum(gatherMovimenti[year = text(t.year) and monthNum = t.Month and '2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 2) and contains(dataEntryCheck, 5): sum(gatherMovimenti['Order date' >= t.'Start Date' and 'Order date' <= t.'End Date' and '2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 2): sum(gatherMovimenti['Order date' >= t.'Start Date' and 'Order date' <= t.'End Date'].Vendite.Sold) case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 5): sum(gatherMovimenti['Order date' >= t.'Start Date' and '2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 2) and contains(dataEntryCheck, 5): sum(gatherMovimenti['Order date' <= t.'End Date' and '2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 4): sum(gatherMovimenti[year = text(t.year) and monthNum = t.Month].Vendite.Sold) case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 5): sum(gatherMovimenti[year = text(t.year) and '2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 4) and contains(dataEntryCheck, 5): sum(gatherMovimenti[monthNum = t.Month and '2_Clients' = number(t.Customer)].Vendite.Sold) case dataEntryCheck[= 1]: sum(gatherMovimenti['Order date' >= t.'Start Date'].Vendite.Sold) case contains(dataEntryCheck, 2): sum(gatherMovimenti['Order date' <= t.'End Date'].Vendite.Sold) case contains(dataEntryCheck, 5): sum(gatherMovimenti['2_Clients' = number(t.Customer)].Vendite.Sold) case contains(dataEntryCheck, 4): sum(gatherMovimenti[monthNum = t.Month].Vendite.Sold) case contains(dataEntryCheck, 3): sum(gatherMovimenti[year = text(t.year)].Vendite.Sold) endthe first part i think is correct..then i don't know how to go on :)
-
stef. idea i am trying to go through all the logic of the sum part..but can't figure out yet how to do it..also not knowinf exactly the language makes the thing harder
the query should be "select and sum from' Vendite' the number of product sold and the number of product returned (considering the product selected in the QW form) within start date and end date (selected from the QW form) from the Customer slected (in QW form)..Now hot to translate? :)
-
-
i see you have uploaded a v5 with some edit in the quick view
One thing i could also suggest is th epossibility to choose also "all customer" option.
I see tere is nothing about product yet...wht ado you think of my attempt with code?
-
stef. idea said:
I see tere is nothing about product yet...wht ado you think of my attempt with code?It is a good start. In v5, make your modification to the gatherData field. You can't just add the article filter to the first case line. You need to make a new line and add article. Then you need to make a new for all new possible combination that could happen with the new article choice.
possibility 1: start date, end date, customer, article
possibility 2: start date, end date, article
possibility 3: start date, article
possibility 4: end date, article
etc.
Remember to always put at the end the single article choice.
Now it is getting quite crazy, I haven't yet figured out how to make it more dynamic.
-
Fred i think i'll not be able to start i ton my own!Anyway in v4 i have added the TOTAL RETURNED which seemed to work..but i don't see it anymore in V5. It is not good?
-
stef. idea You should try to recreate in v5. The use of the hidden field "gatherData" saves you from having to copy the big string of code for each summary field you want to create.
-
@Fred i'll try t look at it
-
-
sorry did not make any copy and i don't know how somehting went wrong in gather data field
-
stef. idea ok i copied over the gatherData code from my copy.
-
Fred thanks..i see now where the error was..i deleted a part of MOVIMENT...
I aded again the Total Returned Field..btw how you resize the field?
For the rest..don't know where to start yet :)
one thing..starting from this quick view table...will be able to see it in a grapgic manner..according to the query i do?
or we need to create a table and graph for different kind of queries?
-
-
one variation for data to display is the list of all the product sold and related return for a date client within a certain time...better than to select the single product
The single product would be useful for display the other way round of the previous
Sholl the list of all customer foir a certian product with related sold and retunr within a certain period of time
I think this 2 might cover all the needs
well there should be one previous probably..a list of the most returned and most sold..so once i have an overview i an go to analyze the details
and they might be 2 different tables?
-
stef. idea said:
one thing..starting from this quick view table...will be able to see it in a grapgic manner..according to the query i do?I don’t think you can do a whole lot with the Quick View, but I don’t have much experience with graphing. I did a quick mock up a graphing table in an earlier post. Take a look at that to give you some ideas.
-
stef. idea said:
..a list of the most returned and most soldtake a look now, there is a new View element that points to ‘3_Article’ table and then shows a summary of sold and returned by record.
-
Fred ok that s it!..is there possibility to sort by sol or by returned?
(edit: i find it by clicking on th ecolumn name..it s not the best for average user..but ok)
from this i should be able to filter per client and per date....and we have a big part of the stats!!
-
stef. idea moving forward it probably be best to start a new post with further questions. I don’t think anyone can follow this in the future.
-
Fred you are right!
-
-
stef. idea said:
how you resize the field?In admin mode, select the field and you see squares on the two short sides of the field. Drag the squares to change field size.