0

Help in making queries

Hello i have a db (many thanks to Fred   ) and now i need to make some queries. In a table where i have a list of "transaction", (sum of products sold and product returned) i want, using appropriate field) query this table so i can see the list of products(sold and returned) by c certain client within a certain period.

Alreadyhave fiedl for selecting client and date, need to build the query

Also would be nice to have the chance to have kind of "*" for theclient filtering..that means show all sold/retuned within a certain period!

Thanks

14 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    One option would be to create a view element in your dashboard and put this for the View formula:

    let custArticle := gatherData.Vendite.Article;
    let allArticle := (select '3_Article');
    allArticle[var v := this;
        count(custArticle[= v]) > 0]
    

    Line 1: we are using the gatherData field that is in your DB that figures out what data to gather based on the many possibilities in your dashboard.

    Line 2: we then get all records from your Article table to display.

    Lines 3 & 4: we filter line 2 with what articles show up in your filter.

    Then we have to add two formula columns in your view. Since the base table for your new view is the '3_Article' table then you need to do a bit jumping around to get the correct information.

    For your sold column you could do something like:

    let xDash := first(select 'Quick View');
    let xArticle := this;
    sum(xDash.gatherData.Vendite[Article = xArticle].Sold)
    

    Line 1: we have to go and get the data from your Quick View dashboard.

    Line 2: we also have to know the which record in the article table we are in

    Line 3: so we point back to the Quick View dashboard and use the gatherData field which we have to filter at Vendite to get only the Articles that match the Article of the line we are on in the View Element. Then we do a sum of the Sold field.

    You would create another column for Returned and just change Sold to Returned.

    There is a new view element in the v5 DB.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred I have seen the new view..the code you are suggesting is already there?

      For what i see It seems just what i need!!Great!

       

      One question, will be possible if clicking on one row of the subtable  will show up other data?

      I am thinking (in the new view) if i click on one row of lets' say SUSHI 1,,it might show up all the client sold/returned to that product

      Of course this ha a sense when i don't select any client

      This because with few products and clients it might not need but with tens of both.,,might be useful to have an averall view of all the clients sale/return and select for example to most returned artcile and see who return it most...

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    I have seen the new view..the code you are suggesting is already there?

     Yes.

     

    stef. idea said:
    One question, will be possible if clicking on one row of the subtable  will show up other data?

     There is a way, but the code is still in beta and not recommended for production environments. It is called Ninext and created by Jacques TUR.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred ok so better to do not use it now then

    • stef_idea
    • 1 yr ago
    • Reported - view

    for the table  that don't respond to query, the opverview

    Does it have sense to keep it there

    I am thinking when there will be hundreds or thousands of records...How it will look like? And anyway with so many records probably it will be of no use..what do you think?

     

    in general, even the other table, what happen when there are many records? do i get  scrolling bar on the side?

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    for the table  that don't respond to query, the opverview
    Does it have sense to keep it there
    I am thinking when there will be hundreds or thousands of records...How it will look like? And anyway with so many records probably it will be of no use..what do you think?

    Those are good points. I can see wanting to a overall per product summary of sold vs return. If the product list is going to be big, more than 25 or so, then you will need a way to filter it in a way that makes sense for the company.

    Does the company sort the products by main course, dessert, or side dish? Or however they sort their products then you can use that to order your by product view.

     

    stef. idea said:
    do i get  scrolling bar on the side?

     yes, once you get past 6 records a scrollbar will be available.

    • stef_idea
    • 1 yr ago
    • Reported - view

    the product list for now can reach 50..and there isn't any kind of sort..they are all sushi products..each box it's a meal itself...could be ordered by sold and return anyway

     

    the 6 records limit can be changed?

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    the 6 records limit can be changed?

     yes, just drag the view box bigger.

    • stef_idea
    • 1 yr ago
    • Reported - view

    by the way, is any customization of the table possible? i mean shape, color or whatever?

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea I don't know. Maybe someone else who knows more can chime in.

    • stef_idea
    • 1 yr ago
    • Reported - view

    also i am thinking it would be good to be able to export datas when they start to get old, and be able to see it again in needed (also with other software on a pc..)

    That's because i think we don't need datas older than , let's say, 1 year. Because it become useless and because the data might grow too much. So exporting them and empty the db would be usefull. I see only the option to backup..which is not excatly what i am talking about though...can it be done?

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea At at the table level, you can select the gear icon and select export data and you can export as a csv or excel.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred ahh great..it was easy!

    • stef_idea
    • 1 yr ago
    • Reported - view

    i am holding on, until i can show to the boss the db...not before next week..so i'll come later with more info!

Content aside

  • 1 yr agoLast active
  • 14Replies
  • 158Views
  • 2 Following