Help

Updating records with data extracted from pdfs

Topic Labels: Automations
260 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Mojjo
4 - Data Explorer
4 - Data Explorer

Hi, 
i want to read pdfs via the open api and fill the data into predefined tables.
Im not so experienced with JS, but with chatGPt I managed to receive already the correct data. 
Now I get an error that doesn't help me any further. I checked all fields have correct names. The pdf data get correctly extracted.  Now im running in circles, I think the scripts tires to update Column 0 ("Bestellnummer") but shouldn't, I only want to update the fields with "AB". 

If anyone can help here I would be very happy. 
peace 


Screenshot 2024-09-16 at 15.11.50.png
this is the code:

// Retrieve all input variables in a single input.config() call
let { pdfFile, lieferant, recordId } = input.config(); // Destructure to get multiple values
console.log("PDF File Structure at Start: ", JSON.stringify(pdfFile, null, 2));

let pdfUrl; // Declare it here to ensure it's accessible outside the block

// Check if a PDF exists in the input and is properly structured
if (Array.isArray(pdfFile)) {
console.log(`pdfFile length: ${pdfFile.length}`);
if (pdfFile.length > 0 && typeof pdfFile[0] === 'string') {
pdfUrl = pdfFile[0]; // Since pdfFile is an array of URLs, directly access the first URL
console.log("PDF URL: ", pdfUrl);

// Output PDF URL to verify the content
output.set("message", `Record for ${lieferant} (Record ID: ${recordId}) successfully updated with PDF URL: ${pdfUrl}`);
} else {
console.log("pdfFile is not an array of strings or has no valid URLs.");
output.set("message", "No valid PDF URL found.");
throw new Error("No valid PDF URL found, stopping script.");
}
} else {
console.log("pdfFile is not an array or is undefined.");
output.set("message", "No valid PDF found in the record.");
throw new Error("No valid PDF found, stopping script.");
}
let extracted_pdf_text = `
Best-Nr. Kunde: 2024-2933
Auftrags-Nr.: 1016366083
Material: CZ-NS5P
Bezeichnung: SOLAR KIT
Menge: 6 ST
Preis: 84.00
Betrag: 504.00
Liefertermin: 2024-10-01
`;
HERE KEYS ETC 
// Parse the response from OpenAI
let openaiResponse = await response.json();

// Log the full response to understand its structure
console.log("Full OpenAI Response:", JSON.stringify(openaiResponse, null, 2));

// Check if the response contains the expected structure
if (!openaiResponse.choices || !openaiResponse.choices[0] || !openaiResponse.choices[0].message || !openaiResponse.choices[0].message.content) {
console.error("Unexpected OpenAI response structure:", openaiResponse);
output.set("message", "Unexpected OpenAI response structure.");
throw new Error("Unexpected OpenAI response structure.");
}

let extractedData = openaiResponse.choices[0].message.content.trim();

// Convert the OpenAI response into JSON (assuming valid JSON)
let jsonData;
try {
jsonData = JSON.parse(extractedData);
} catch (e) {
console.error("Failed to parse OpenAI response as JSON:", extractedData);
output.set("message", "Failed to parse OpenAI response as JSON.");
throw new Error("Failed to parse OpenAI response as JSON.");
}

// Log the extracted data from OpenAI
console.log("Extracted Data from OpenAI:", jsonData);

// Check Airtable field names to map them correctly
let table = base.getTable('Bestellung aus Disposition');
let fieldNames = table.fields.map(field => field.name);
console.log("Available Field Names: ", fieldNames);

// Ensure we're accessing the first object in the array
let extractedObject = jsonData[0];

// Extract the numeric part of "Menge" but treat it as text since all fields are single-line text fields
let mengeValue = extractedObject["Menge"] ? extractedObject["Menge"].split(" ")[0] : null; // Just the number part as a string

// Log the data being sent to Airtable to verify we're sending the correct fields
console.log("Data being sent to Airtable:", {
"AB Best.-Nr.": extractedObject["Bestellnummer"],
"AB Menge": mengeValue,
"AB Preis": extractedObject["Preis"],
"AB Liefertermin": extractedObject["Liefertermin"],
"AB Angebotsnummer": extractedObject["Auftragsnummer"],
"AB Material": extractedObject["Material"],
"AB Bezeichnung": extractedObject["Bezeichnung"],
"AB Betrag": extractedObject["Betrag"]
});

try {
await table.updateRecordAsync(recordId, {
"AB Best.-Nr.": extractedObject["Bestellnummer"], // Send as string
"AB Menge": mengeValue, // Send as string
"AB Preis": extractedObject["Preis"], // Send as string
"AB Liefertermin": extractedObject["Liefertermin"], // Send as string
"AB Angebotsnummer": extractedObject["Auftragsnummer"], // Send as string
"AB Material": extractedObject["Material"], // Send as string
"AB Bezeichnung": extractedObject["Bezeichnung"], // Send as string
"AB Betrag": extractedObject["Betrag"] // Send as string
});

console.log("Record successfully updated.");
} catch (error) {
console.error("Error updating record:", error);
}

output.set("message", `Record for ${lieferant} (Record ID: ${recordId}) successfully updated with data from PDF file: ${pdfUrl}.`);
1 Reply 1
Alexey_Gusev
13 - Mars
13 - Mars

Hi,
chatGPT often overengineering things that are more simple. also, it mixes methods from scripting extension with methods of automation and API, so the result code usually works nowhere. But in your case It feels like code is mostly OK.
To update attachment field, you should pass url:  property (and optionally filename:)
 Actually, it should be array of urls   

 

 [ {url:'http://....'} , {url:'http://....'} , {url:'http://....'} ]

 


even if it contains just 1 file, it must be array

Alexey_Gusev_0-1726603655814.png


Please use 

Alexey_Gusev_1-1726603689377.png

 

for pasting code, because it's hard to read when posted  in usual text mode.