Welcome to NESR! Most features of this site require registration, including replying to threads, sending private messages, starting new threads, and uploading files. Click here to register.

Results 1 to 20 of 20

Web CSV Search Info

  1. #1
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Web CSV Search Info


    So I'm at the bicycle shop a lot and am currently trying to help the owner out a bit.

    The issue is customers constantly calling and asking the status of the bike during repairs. Since he is a one man operation taking all these phone calls really eats into his repair time.

    What I would like to be able to do is export a csv file of service work orders that have not been paid or completed from his POS system.

    I would then like to add a function to index match the service work order number on their check in receipt to the current status and estimated completion date.

    This is the web site. Apollo Cycles

    Ideally this csv file would update to the ftp site every time there is a change made to a work order status.

    Any info would be greatly appreciated.

    0 Not allowed!
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  2. #2
    Lifer
    Join Date
    Nov 2009
    Location
    Bristol County
    Age
    32
    Posts
    2,771

    Re: Web CSV Search Info

    I think most are going to need to know more about the POS system unless there this is one of those areas where all systems typically operate the same way? What software is it?

    When you talk about index matching, are those pieces of data all coming from the POS system, or is there a second piece of software/system/tool in the mix?

    0 Not allowed!
    02 Aprilia Futura
    86 GPz900R

  3. #3
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    Quote Originally Posted by aldend123 View Post
    I think most are going to need to know more about the POS system unless there this is one of those areas where all systems typically operate the same way? What software is it?

    When you talk about index matching, are those pieces of data all coming from the POS system, or is there a second piece of software/system/tool in the mix?
    The POS system is Bike Shop Point of Sale System | Lightspeed POS

    Index match would be the excel function that would be used to match the customer order number to the order status and estimated date of completion. This data would then populate on the web site once they have entered their work order number. It would be a html command of some sort that would be matching this instead of an excel function, but that is the best way I could think of communicating it.

    0 Not allowed!
    Last edited by csmutty; 04-09-19 at 06:09 PM.
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  4. #4
    Lifer jasnmar's Avatar
    Join Date
    Mar 2012
    Location
    Chichester, NH
    Posts
    4,021

    Re: Web CSV Search Info

    To derail this a bit from index matching and excel functions... The problem you are looking to solve is:

    • The guy that runs the bicycle shop has a POS system where he tracks his repair work.
    • He gets bothered a lot by people calling him to find out the status of their repair.
    • He wants to expose the POS repair data on his website so that people can look it up themselves, based on some "order number".


    (There are a ton of hidden requirements here, but I want to make sure we understand the problem first).

    1 Not allowed!

  5. #5
    Burns retinas nhbubba's Avatar
    Join Date
    Jun 2011
    Location
    Seacoast NH
    Posts
    15,757

    Re: Web CSV Search Info

    What you need is a microservice, hosted in the cloud, which periodically accesses the POS API, extracts the work order list, converts it to a view-model suitable for presenting to the user through a REST endpoint-providing filtering and authentication capacities of course-and rendered using a golang front-end.

    1 Not allowed!

  6. #6
    Backwoods lobster boy number9's Avatar
    Join Date
    Aug 2005
    Location
    Hanover, NH
    Posts
    3,639

    Re: Web CSV Search Info

    Quote Originally Posted by nhbubba View Post
    What you need is a microservice, hosted in the cloud, which periodically accesses the POS API, extracts the work order list, converts it to a view-model suitable for presenting to the user through a REST endpoint-providing filtering and authentication capacities of course-and rendered using a golang front-end.
    lol

    1 Not allowed!
    '08 Ducati HyperMotard 1100S, '14 Subaru XV Crosstrek

  7. #7
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    Quote Originally Posted by jasnmar View Post
    To derail this a bit from index matching and excel functions... The problem you are looking to solve is:

    • The guy that runs the bicycle shop has a POS system where he tracks his repair work.
    • He gets bothered a lot by people calling him to find out the status of their repair.
    • He wants to expose the POS repair data on his website so that people can look it up themselves, based on some "order number".


    (There are a ton of hidden requirements here, but I want to make sure we understand the problem first).
    Yes. You've got it!
    Quote Originally Posted by nhbubba View Post
    What you need is a microservice, hosted in the cloud, which periodically accesses the POS API, extracts the work order list, converts it to a view-model suitable for presenting to the user through a REST endpoint-providing filtering and authentication capacities of course-and rendered using a golang front-end.
    After doing a decent amount of digging it looks like I might be able to do this running a google script in sheets. I am going to try my hand at writing some code for this over the next couple days.

    That will only address part of the issue though. I still won't be able to send results from the POS service to google sheets.

    0 Not allowed!
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  8. #8
    Lifer jasnmar's Avatar
    Join Date
    Mar 2012
    Location
    Chichester, NH
    Posts
    4,021

    Re: Web CSV Search Info

    Quote Originally Posted by nhbubba View Post
    What you need is a microservice, hosted in the cloud, which periodically accesses the POS API, extracts the work order list, converts it to a view-model suitable for presenting to the user through a REST endpoint-providing filtering and authentication capacities of course-and rendered using a golang front-end.
    I mean, except for the golang part, that pretty much sounds right.

    Quote Originally Posted by csmutty View Post
    Yes. You've got it!

    After doing a decent amount of digging it looks like I might be able to do this running a google script in sheets. I am going to try my hand at writing some code for this over the next couple days.

    That will only address part of the issue though. I still won't be able to send results from the POS service to google sheets.
    Is the POS a dedicated system? Is the data stored on some sort of actual computer? If so you should be able to do a cron job to upload the data (assuming you don't want to do the real solution that 'bubs described).

    If I were doing it I'd convert the .csv to a json doc. Store that in a bucket or possibly in a NoSQL db for searching / rendering to a web page of some sort. That probably isn't very straight forward using the SquareSpace site that he's currently using, so it'd need to be some sort of custom thing embedded into the site.
    It might be possible to do this with some custom Google docs / forms.

    0 Not allowed!

  9. #9
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    Quote Originally Posted by jasnmar View Post
    I mean, except for the golang part, that pretty much sounds right.



    Is the POS a dedicated system? Is the data stored on some sort of actual computer? If so you should be able to do a cron job to upload the data (assuming you don't want to do the real solution that 'bubs described).

    If I were doing it I'd convert the .csv to a json doc. Store that in a bucket or possibly in a NoSQL db for searching / rendering to a web page of some sort. That probably isn't very straight forward using the SquareSpace site that he's currently using, so it'd need to be some sort of custom thing embedded into the site.
    It might be possible to do this with some custom Google docs / forms.
    POS is a cloud based system. I'll have to do some research on things you and bubs mentioned later this week.

    Thank y'all.

    0 Not allowed!
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  10. #10
    Burns retinas nhbubba's Avatar
    Join Date
    Jun 2011
    Location
    Seacoast NH
    Posts
    15,757

    Re: Web CSV Search Info

    Quote Originally Posted by jasnmar View Post
    I mean, except for the golang part, that pretty much sounds right.
    I googled it. Sounded cool.

    1 Not allowed!

  11. #11
    Lifer Tekime's Avatar
    Join Date
    Mar 2016
    Location
    Falmouth, ME
    Age
    39
    Posts
    1,318

    Re: Web CSV Search Info

    Squarespace integration with Lightspeed API would be the shoe-in but probably costs $$$.

    The CSV-export alternative will depend wholly on whether you can automate CSV exports via triggers/cron/etc. Totally up to Lightspeed. Or he'll be saving CSV exports end of each day manually.

    He's paying good money for Lightspeed every month, worth a call to their team to inquire about existing solutions/apps.

    Vendors love to make stuff like this pointlessly difficult... funky alternatives might be:

    - Email notification triggers on order status changes to auto-notify customers (if available)
    - A work order status request form on Squarespace that just emails the owner so he can handle responses in bulk at the end of the day (crude, yes)
    - Use a browser automation tool to log in/export CSV periodically if not natively supported


    Quote Originally Posted by nhbubba View Post
    What you need is a microservice, hosted in the cloud, which periodically accesses the POS API, extracts the work order list, converts it to a view-model suitable for presenting to the user through a REST endpoint-providing filtering and authentication capacities of course-and rendered using a golang front-end.
    Well the POS API would already be the endpoint unless you're talking about another storage/authentication layer and API on top of that. Without a full-blown accounts system I'm not sure how much authentication will be done here since the only input to work with is a work order #. Although this would be proper, just thinking out loud.

    0 Not allowed!
    05GSXR75005SV65090DR350

  12. #12
    Senior Member capitalcrew's Avatar
    Join Date
    Feb 2017
    Location
    Albany NY
    Posts
    485

    Re: Web CSV Search Info

    Sub because I'm interested in what you come up with

    0 Not allowed!

  13. #13
    Backwoods lobster boy number9's Avatar
    Join Date
    Aug 2005
    Location
    Hanover, NH
    Posts
    3,639

    Re: Web CSV Search Info

    Quote Originally Posted by Tekime View Post
    Well the POS API would already be the endpoint unless you're talking about another storage/authentication layer and API on top of that. Without a full-blown accounts system I'm not sure how much authentication will be done here since the only input to work with is a work order #. Although this would be proper, just thinking out loud.
    It's obviously not really secure, but I would just do a simple login dialog that requires order number AND phone number or ZIP code.

    1 Not allowed!
    '08 Ducati HyperMotard 1100S, '14 Subaru XV Crosstrek

  14. #14
    Lifer
    Join Date
    Nov 2009
    Location
    Bristol County
    Age
    32
    Posts
    2,771

    Re: Web CSV Search Info

    I'm not seeing a lot of technical details on the Lightspeed site. But I'd be wondering if they offered an API and/or embeddable widget to put on the shop's website.

    0 Not allowed!
    02 Aprilia Futura
    86 GPz900R

  15. #15
    Lifer jasnmar's Avatar
    Join Date
    Mar 2012
    Location
    Chichester, NH
    Posts
    4,021

    Re: Web CSV Search Info

    If you actually need Auth either Okta or Auth0 offer "free tiers" which are more than capable of supporting the load for a use case like this.

    0 Not allowed!

  16. #16
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    Quote Originally Posted by number9 View Post
    It's obviously not really secure, but I would just do a simple login dialog that requires order number AND phone number or ZIP code.
    I just don't really think security is really necessary? It won't really say anything other than order status and date ready for pickup.

    0 Not allowed!
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  17. #17
    Lifer
    Join Date
    Nov 2009
    Location
    Bristol County
    Age
    32
    Posts
    2,771

    Re: Web CSV Search Info

    Quote Originally Posted by csmutty View Post
    I just don't really think security is really necessary? It won't really say anything other than order status and date ready for pickup.
    That's true as long as whatever is being 'exposed' through the website isn't also authorized for more. Like if someone built a widget that shows data, thinking it only displays data, without realizing that it also allows savvy users to run queries or additional commands.

    Would I be wrong in saying this a side project/learning adventure? Because I think some of the stuff being thrown around is way beyond the scope of what you probably had in mind. However, what they're mostly trying to address is that once you have the data you want in the excel spreadsheet, how does that data end up on the website? Are you thinking there'd be a link to bikect.com/dailyspreadsheet.xls? I don't see any reason for authorization on a downloaded spreadsheet assuming it has zero personal customer data.

    0 Not allowed!
    02 Aprilia Futura
    86 GPz900R

  18. #18
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    Quote Originally Posted by aldend123 View Post
    That's true as long as whatever is being 'exposed' through the website isn't also authorized for more. Like if someone built a widget that shows data, thinking it only displays data, without realizing that it also allows savvy users to run queries or additional commands.

    Would I be wrong in saying this a side project/learning adventure? Because I think some of the stuff being thrown around is way beyond the scope of what you probably had in mind. However, what they're mostly trying to address is that once you have the data you want in the excel spreadsheet, how does that data end up on the website? Are you thinking there'd be a link to bikect.com/dailyspreadsheet.xls? I don't see any reason for authorization on a downloaded spreadsheet assuming it has zero personal customer data.
    That could be done. I was thinking along the lines of a form that would then record the results of the form in a Google sheet. Said form would then search for the last row in the column on the page and match the work order number from the last row in the column to the sheet generated from the pos. This match would then be stored on a separate sheet in the workbook which would be displayed on the site after the form is completed.

    0 Not allowed!
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  19. #19
    Your Father csmutty's Avatar
    Join Date
    Sep 2008
    Location
    Pomfret Center, CT
    Age
    28
    Posts
    10,194

    Re: Web CSV Search Info

    This is what I was messing around with last night.

    Untitled spreadsheet - Google Sheets

    0 Not allowed!
    Last edited by csmutty; 04-10-19 at 06:35 PM.
    -Christian LRRS/CCS #316 ECK Racing GMD Computrack Boston | Pine Motorparts/PBE Specialists | Woodcraft | Street & Competition | OnTrack Media

    2011 Pit Bike Race CHAMPION!

  20. #20
    Lifer
    Join Date
    Nov 2009
    Location
    Bristol County
    Age
    32
    Posts
    2,771

    Re: Web CSV Search Info

    Quote Originally Posted by csmutty View Post
    That could be done. I was thinking along the lines of a form that would then record the results of the form in a Google sheet. Said form would then search for the last row in the column on the page and match the work order number from the last row in the column to the sheet generated from the pos. This match would then be stored on a separate sheet in the workbook which would be displayed on the site after the form is completed.
    I think you kinda just described the primary functionality of databases

    When you say form, are you talking about using form building features offered by Google Sheet and/or Excel? If so, I'm pretty sure you can get what you want out of it. But that's only half the equation, as you'd need a way to update/re-populate/re-create the spreadsheet with new data. Before digging too much in to the form aspect, I think getting the data from the POS system is going to be the more difficult portion. Find out what options they offer - I saw some mention of report generators, and I'd wonder if their admin pages allow the creation of mostly what you want and it could be scheduled.

    0 Not allowed!
    02 Aprilia Futura
    86 GPz900R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •