0

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

null
    • stef_idea
    • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr ago
      • Reported - view

      Fred i think the way it is now it is not too bad!

      • Fred
      • 1 yr 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
      • 1 yr 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
    • 1 yr 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
      • 1 yr 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
    • 1 yr 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
    • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr ago
      • Reported - view

      @Fred i'll try t look at it

    • stef_idea
    • 1 yr ago
    • Reported - view

    sorry did not make any copy and i don't know how somehting went wrong in gather data field

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea ok i copied over the gatherData code from my copy.

      • stef_idea
      • 1 yr 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
    • 1 yr 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
    • 1 yr 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
    • 1 yr 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
      • 1 yr 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
      • 1 yr 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
      • 1 yr ago
      • Reported - view

      Fred you are right!

    • Fred
    • 1 yr 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.

Content aside

  • 1 yr agoLast active
  • 101Replies
  • 445Views
  • 3 Following