how to project this db

stef_idea
2 yrs ago
101replies

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?

- stef_idea
- 2 yrs ago
- Reported - view
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...
- Fred
  
  2 yrs ago
  
  Reported - view
  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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  Fred let me understand, with a later import how will be th ework of driver?what actually he will need to do?
- Fred
  
  2 yrs ago
  
  Reported - view
  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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  Fred ok i don't understand why you have had this idea, what is not convincing you for the driver to scan in ninox?
- Fred
  
  2 yrs ago
  
  Reported - view
  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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  Fred i think the way it is now it is not too bad!
- Fred
  
  2 yrs ago
  
  Reported - view
  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. :)
- stef_idea
  
  2 yrs ago
  
  Reported - view
  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

stef_idea
2 yrs ago
Reported - view

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
- 2 yrs ago
- Reported - view
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? :)

- stef_idea
- 2 yrs ago
- Reported - view
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?
- Fred
- 2 yrs ago
- Reported - view
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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  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?
- Fred
  
  2 yrs ago
  
  Reported - view
  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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  @Fred i'll try t look at it
- stef_idea
- 2 yrs ago
- Reported - view
sorry did not make any copy and i don't know how somehting went wrong in gather data field
- Fred
  
  2 yrs ago
  
  Reported - view
  stef. idea ok i copied over the gatherData code from my copy.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  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?
- stef_idea
- 2 yrs ago
- Reported - view
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?
- Fred
- 2 yrs ago
- Reported - view
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.
- Fred
- 2 yrs ago
- Reported - view
stef. idea said:
..a list of the most returned and most sold

take 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
  
  2 yrs ago
  
  Reported - view
  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!!
- Fred
  
  2 yrs ago
  
  Reported - view
  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.
- stef_idea
  
  2 yrs ago
  
  Reported - view
  Fred you are right!
- Fred
- 2 yrs ago
- Reported - view
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.

Welcome to the Ninox community

how to project this db

101 replies

Content aside

Categories

Overview