
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...
-
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) end
the first part i think is correct..then i don't know how to go on :)
-
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.
-
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.
-
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.