deleted-U072JS2NHRQ
2025-05-19 15:48:12

Here's the Address of Contaminated Water sheet with the custom ID's - it's the sample of the end product

IMPORT_DL_AFFF_-_Address_of_Contaminated_Water-Custom Report-2025-05-13.xlsx
Josh Josue (jjosue@shield-legal.com)
2025-05-19 15:54:05

awesome - thanks man!

deleted-U072JS2NHRQ
2025-05-19 16:04:41

anytime 🫡 lemme know if you need anything else! I'll try and get the other excel sheets ready like the one above^ but as examples of the output for each of the remaining 8-9 templates

👍 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-20 12:34:17

gmorning, that sheet worked nicely!

I'm ready for the other sheets with their custom ID's 🤓

deleted-U072JS2NHRQ
2025-05-20 12:34:40

🫡

deleted-U072JS2NHRQ
2025-05-20 12:34:47

deal, gimme like acouple minutes and I'll send em over

👍 Josh Josue
deleted-U072JS2NHRQ
2025-05-20 12:35:13

I haven't gotten started on the code to un-format the google sheet yet though, cuz I've had to do some other priority things, so just a heads up as well

Josh Josue (jjosue@shield-legal.com)
2025-05-20 12:35:32

oh that code update isnt needed til later

Josh Josue (jjosue@shield-legal.com)
2025-05-20 12:35:53

right now, I'm just getting a better picture on how we're gonna handle this diff checking stuff

Josh Josue (jjosue@shield-legal.com)
2025-05-20 12:37:07

awesome thanks!! 🙏

Josh Josue (jjosue@shield-legal.com)
2025-05-20 14:04:31

ok so i ran a test that took all those question id's and tried to look them up on the GCP columns

a question id c-24159 ("OtherType of Facility") was not found in the GCP columns. What could cause this to happen?

deleted-U072JS2NHRQ
2025-05-20 14:32:22

hey sorry, I was at lunch

deleted-U072JS2NHRQ
2025-05-20 14:33:02

i'll check right now to double check something

deleted-U072JS2NHRQ
2025-05-20 14:34:44

is that the only one for 'Other Type of Facility' that's not showing up? I think there's 15 of them

deleted-U072JS2NHRQ
2025-05-20 14:37:13

Honestly, it's probably that dustin needs to either pull the updates questions or something into the database. We updated the questions like 3 months ago, so he probably just hasn't updated the bigquery database since then regarding the questions

👍 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-20 15:31:02

no worries

Josh Josue (jjosue@shield-legal.com)
2025-05-20 15:31:14

it's the first one i ran into and failed the test

Josh Josue (jjosue@shield-legal.com)
2025-05-20 15:31:47

thanks for checking that!

ok i'll have a chat with them

deleted-U072JS2NHRQ
2025-05-20 15:33:13

lemme know if you run into any other ones, it might just specifically be the 'Other Type of Facility' questions as those were added more recently

👍 Josh Josue
deleted-U072JS2NHRQ
2025-05-20 15:33:25

like a month or two ago roughly

deleted-U072JS2NHRQ
2025-05-20 15:33:32

but yeah lemme know what they say!

Josh Josue (jjosue@shield-legal.com)
2025-05-20 16:07:21

Ok so turns out their script was implicitly filtering out the columns without any answers to them. After Dustin updated his script, it was revealed that there were supposed to be more questions on their output file

👍:skin_tone_3: deleted-U072JS2NHRQ
Josh Josue (jjosue@shield-legal.com)
2025-05-20 16:08:34

TL;DR they updated their script to include empty columns, and now all my unit tests pass (confirmed that all question id's from your templates are in their excel file)

deleted-U072JS2NHRQ
2025-05-20 16:13:00

awesome, thank you! at least it's all added now 😎

😎 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-20 16:14:33

so from here, I'm gonna make some mock data with the assumption that a column will contain the "1 of 13" in them

what are you planning to call this column?

deleted-U072JS2NHRQ
2025-05-20 16:15:03

anything we want to, I was just gonna probably name it 'Count' or something

deleted-U072JS2NHRQ
2025-05-20 16:15:16

something where I won't be like 'wtf is this' cuz i forget what it is honestly

😆 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-20 16:16:48

how bout "EntryCountPer_Lead" ?

deleted-U072JS2NHRQ
2025-05-20 16:17:37

works for me! 🫡

deleted-U072JS2NHRQ
2025-05-20 16:29:45

@Josh Josue just a heads up, brittany just told me we have to add questions to the AFFF case type, so we'll probably be doing that over the next couple days, it'll affect my reformatter and what you're doing too but i'll give you the custom ID whenever it's made 👍:skintone3:

👍 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-20 16:41:44

Ok thanks for the heads up!

Josh Josue (jjosue@shield-legal.com)
2025-05-23 12:24:54

hi Aiden, so i forgot that the question id's have the curly braces, there's no need for a delimiter

e.g. AFFF-contaminated water 2: Zip code of location where exposed to AFFF-contaminated water (residence, school, workplace, military base, etc):{{Custom23723}} ^this format would work just fine

deleted-U072JS2NHRQ
2025-05-23 12:25:41

gotcha, that works for me too, thank you! 🙂

🙏 Josh Josue
deleted-U072JS2NHRQ
2025-05-23 12:46:19

so wait, I was going to reconstruct the 10 different excel files, are you saying it'd be easier for you if it was all into 1 file?

deleted-U072JS2NHRQ
2025-05-23 12:46:25

sorry it just clicked into my brain rn

Josh Josue (jjosue@shield-legal.com)
2025-05-23 12:46:51

oh yea it would be easier as 1 file

deleted-U072JS2NHRQ
2025-05-23 12:48:44

okay deal 🫡

Josh Josue (jjosue@shield-legal.com)
2025-05-23 12:48:57

thanks man!

deleted-U072JS2NHRQ
2025-05-23 12:52:40

if it's 1 file, but multiple sheets would that make any difference in regards to the ease of doing this? or would it be the same as different files basically

🤔 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-23 12:54:31

hmm... um what do u mean by 1 file but multiple sheets?

Josh Josue (jjosue@shield-legal.com)
2025-05-23 12:54:47

like, the 1 file contains all the sheets you sent me right?

deleted-U072JS2NHRQ
2025-05-23 12:58:35

Yeah, like I sent them to you as different files (each with 1 sheet), but I want to see if I can send you them as 1 excel file, with 1 different sheets/tabs within that 1 file.

There’s like multiple ‘Direct Exposure 16+’ questions that’s like a ‘catch all’ if they go above 16 locations so I have to see how I can deal with those questions when formatting them back into the format that you need

Josh Josue (jjosue@shield-legal.com)
2025-05-23 13:00:37

ohh that tabs, i misunderstood. by 1 file i thought they would all be merged together

Josh Josue (jjosue@shield-legal.com)
2025-05-23 13:01:11

if it's the caase that it's 1 excel file with multiple tabs, i would prefer the multiple excel files scenario

deleted-U072JS2NHRQ
2025-05-23 13:01:52

Gotcha gotcha, I’ll see what I can do but I’ll keep you updated on it

🙏 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-23 15:33:24

so I talked with Nick, i dont think we'll need that column with "1 or 5" on it

deleted-U072JS2NHRQ
2025-05-23 15:47:35

awesome, one less thing for me to do lol

😆 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-05-29 15:00:00

Hey man, any update on the excel files with question id's appended to column names?

deleted-U072JS2NHRQ
2025-05-29 15:01:15

Oh shit, no I actually forgot about it cuz Brittany had me pivot to something else, but I finished it a bit ago, so I will work on these and have them to you by tomorrow-ish

Josh Josue (jjosue@shield-legal.com)
2025-05-29 15:09:41

gotcha - alright thanks!

deleted-U072JS2NHRQ
2025-05-29 15:10:12

Sorry dude, I’m a wreck lol I mean this place is a wreck, and I’m playing catch up with all the stuff to fix 😭

Josh Josue (jjosue@shield-legal.com)
2025-05-29 15:13:42

Lol naw i get it. But i appreciate ya for adding those question id’s! 🙏

deleted-U072JS2NHRQ
2025-05-30 12:07:44

These are updates with 'Question:{{Custom12345}}'

Josh Josue (jjosue@shield-legal.com)
2025-05-30 12:27:21

Thanks dude! I'll be able to run more tests with these

deleted-U072JS2NHRQ
2025-06-04 11:57:06

so I found out it's only because they're headers, if I give you the excel back in csv format it should have all the characters

Josh Josue (jjosue@shield-legal.com)
2025-06-04 12:01:29

Ohh awesome- yes plz I’d appreciate that

deleted-U072JS2NHRQ
2025-06-04 12:17:08

do you know specifically which headers were cut off? or at least which sheet/excel file it was in?

Josh Josue (jjosue@shield-legal.com)
2025-06-04 12:17:31

Oh yeah, one sec

Josh Josue (jjosue@shield-legal.com)
2025-06-04 12:21:02

• Address of Contaminated Water • Direct Exposure Product Information • Direct Exposure Product Information

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2025-06-04 12:26:03

thank you!

deleted-U072JS2NHRQ
2025-06-04 14:19:34

In order to update the questions, do you need the output questions to match exactly what the questions in LR are? like does it matter if they're changed a little?

Josh Josue (jjosue@shield-legal.com)
2025-06-04 14:19:53

the question wording doesnt matter

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2025-06-04 14:20:08

okay perfect, and it'll still have the custom id so we're golden

🙏 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-06-04 14:20:11

the thing that matters the most is that there's a question id per question

deleted-U072JS2NHRQ
2025-06-04 14:21:24

gotcha, yeah we got that down 👍:skintone3:

💯 Josh Josue
deleted-U072JS2NHRQ
2025-06-04 14:21:27

thank you!

Josh Josue (jjosue@shield-legal.com)
2025-06-04 15:28:36

when you get the chance, could i plz have the updated versions of those 3 files: • Address of Contaminated Water • Direct Exposure Product Information • Direct Exposure Product Information

deleted-U072JS2NHRQ
2025-06-04 15:29:38

I haven't finished them up, but I will probably get them to you some time tomorrow if that's okay? sorry!

Josh Josue (jjosue@shield-legal.com)
2025-06-04 15:30:07

oh that's totally fine - thanks man!

Josh Josue (jjosue@shield-legal.com)
2025-06-06 13:14:14

Hey man, got those updated files?

deleted-U072JS2NHRQ
2025-06-06 13:18:02

I do not, I'm sorry, I'm still working out some kinks with the code - do you just want an example of what the output will look like? cuz I can manually edit one of the older ones if you'd like and send it over to you

Josh Josue (jjosue@shield-legal.com)
2025-06-06 13:22:54

oh ok, then could i plz have the question id's for the last columns of those 3 files?

Josh Josue (jjosue@shield-legal.com)
2025-06-06 13:23:03

i'll manually type em in for testing purposes

deleted-U072JS2NHRQ
2025-06-06 13:24:26

ohhh, yeah yeah please hold

deleted-U072JS2NHRQ
2025-06-06 13:26:06

Address of Contaminated Water 16+ question - {{Custom23963}} Direct Exposure Address History 16+ question - {{Custom24193}} Direct Exposure Product Information 6+ question - {{Custom24242}}

are these the right ones? cuz you put Direct Exposure Product Information twice

🙏 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-06-06 13:31:41

oh sorry, but yea these are the ones i need, thanks!

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2025-06-09 16:04:25

Hey @Josh Josue can I give you like 1 or 2 and let me know if it'd work? don't like actually update the info - but would this layout work? these are how they'd be output through my code: These are just rough drafts, i have to make sure there are no weird things happening with the text and such, if there is we can't have it uploading the weird stuff to lawruler

Initial PFS Data_2025-06-09.xlsx
Medical Provider PFS_2025-06-09.xlsx
Military Service_2025-06-09.xlsx
Prior Residence_2025-06-09.xlsx
Direct Exposure Address History_2025-06-09.xlsx
Direct Exposure Product Information_2025-06-09.xlsx
Fire Service_2025-06-09.xlsx
Persons with Knowledge_2025-06-09.xlsx
Water Exposure Information_2025-06-09.xlsx
Address of Contaminated Water_2025-06-09.xlsx
Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:06:57

sure, lemme run tests on these

deleted-U072JS2NHRQ
2025-06-09 16:07:18

just make sure to don't actually use these to update anything in lawruler plz! lol

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:07:57

yep!

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:08:10

nothing on my code uploads to anywhere

deleted-U072JS2NHRQ
2025-06-09 16:10:41

oh, that's good then lol phew

😆 Josh Josue
deleted-U072JS2NHRQ
2025-06-09 16:10:46

so it would just change bigquery right?

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:11:41

yea, they told me that Dustin's web portal would make the updates

I'm just working on a thing that checks the differences between GCP entries and your guys' excel files

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:14:29

so i noticed that these excel files' first column is "Plaintiff Number"

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:14:40

it used to be "Lead ID"

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:14:50

is this going to be the new template column names?

deleted-U072JS2NHRQ
2025-06-09 16:15:31

Ahhh gotcha gotcha, that makes more sense now for some reason I thought you were directly updating lawruler, I misunderstood

deleted-U072JS2NHRQ
2025-06-09 16:16:24

And I can change it back to Lead ID, idk why it’s plaintiff number - I’m still working on a couple things but this is the general layout for how they’ll be output

👍 Josh Josue
deleted-U072JS2NHRQ
2025-06-09 16:17:03

The ones without {{Custom#####}} in the headers are blank columns/filler columns - they’re just needed for spacing so you can ignore the ones without custom id’s

👍 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:21:28

*Thread Reply:* awesome! yea bcuz I've only been checking diffs for columns with {{Custom####}} haha

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:21:50

but yeah, i think everything else is looking good

deleted-U072JS2NHRQ
2025-06-09 16:24:19

okay PERfect, that's awesome, I'm thinking of possibly automating the 'sending it to you' part and just sending it in an email to you, so if you're able to have it so that it checks your email for any unread email with 'xyz' subject - did you want me to do that?

deleted-U072JS2NHRQ
2025-06-09 16:25:31

i leave in like 6 minutes, but we can talk more about that tomorrow or something if you want - cuz the audits that they do are inconsistent time-wise so i'd have to manually execute it, but you might be able to set it up so that it checks your email for an unread email (with a subject that we determine at a later date) and executes when that email is received - but idk, there might be easier ways to do that

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:25:57

I dont think that automation is necessary

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:26:10

the actual usecase is a User uploading these excel files onto webportal

Josh Josue (jjosue@shield-legal.com)
2025-06-09 16:26:30

yea we can talk tomorrow - thanks dude!

deleted-U072JS2NHRQ
2025-06-09 16:27:49

ooh i didn't know that's how it'd work! yeah, we'll talk tomorrow, thank you! 🫡

👍 Josh Josue
🫡 Josh Josue
Josh Josue (jjosue@shield-legal.com)
2025-06-10 11:00:04

I've noticed some questions have {{Default###}} For example "Date of Death {{Default91}}" from Initial PFS Data_2025-06-09

deleted-U072JS2NHRQ
2025-06-10 11:01:00

Those are default fields - so instead of like c-12345 , it'd be like d-14 for the custom id

Josh Josue (jjosue@shield-legal.com)
2025-06-10 11:09:35

If that field isnt found on Big Query, does it mean that it was recently added?

deleted-U072JS2NHRQ
2025-06-10 11:19:05

No, it's just probably not being pulled by dustins code

deleted-U072JS2NHRQ
2025-06-10 11:19:11

into bigquery

Josh Josue (jjosue@shield-legal.com)
2025-06-10 11:20:32

Ohhh - ok I’ll look into this

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2025-06-10 11:29:12

thank you! lemme know what he says, cuz we haven't added any more default fields (to my knowledge) for like 6+ months

👍 Josh Josue
deleted-U072JS2NHRQ
2025-06-10 12:07:16

the field might also jsut be the number for the default ones

deleted-U072JS2NHRQ
2025-06-10 12:07:28

if it's default51, it might just be "51"

deleted-U072JS2NHRQ
2025-06-10 12:07:50

Not 100% sure but just in case wanted to tell you

Josh Josue (jjosue@shield-legal.com)
2025-06-10 12:07:55

Ok I’ll try that out! Thanks

Josh Josue (jjosue@shield-legal.com)
2025-06-11 11:00:15

Dustin gave me a table of the default fields. I was able to account for them now

deleted-U072JS2NHRQ
2025-06-11 11:01:01

okay perfect! thank you! I'm still working on some final tweaks, but it seems to be fine right now

👍 Josh Josue